Table of Contents

About

A physical plan (or execution plan) is an ordered tree of physical relational operator that represent the execution a query

It is also known as:

  • execution plan
  • execution tree

It's the output of the optimizer. that describes an optimum method of execution.

A physical plan shows how the operation will be executed/implemented whereas a logical plan do not.

The execution plan takes the form of a combination of steps that the Database uses to execute a SQL statement.

Each step returns a row set. Each step either retrieves rows of data physically from the database and/or prepares them in some way for the user issuing the statement.

In some execution plans the steps are iterative and in others sequential. A plan is iterative when for instance the SQL engine moves from index to table to client and then repeats the steps.

When executed by the SQL virtual machine, the plan produces the result set.

You can get the execution plan through various mechanisms.

You can get:

You can influence the execution plan (e.g. the query optimizer) by

The query plan might not be the same from one version of Oracle Database to the next. In recent versions, the optimizer might make different decisions, because better information is available.

Query Plan For The Same Query Response Time

Operations and Pseudo Code

For the below SQL, 

select 
  * 
from 
  order o, item i
where
  o.order = i.order

You have two type of join.

The nested join will go faster than a hash join for a few records because it don't need to create the data structure

Option 1 - Nested Loop Join

  • the nested loop-join has O(n2) time complexity.
  • square complexity
for each record i in Item
   for each record o in Order
       if o.order = i.order
           return matching pair

Option 2 - Data Structure Join

  • Constant time
  • The hash-join has O(n) time complexity
for each record i in Item         
   insert into a data structure (such as a hash table)

for each record o in Order:
   lookup corresponding records in hashtable
   return matching pair

Reviewing plans and tuning

It's important when reviewing and tuning queries to emulate what your application does. You cannot tune a query with literals and expect a query that contains bind variables to have the same performance characteristics.

Tuning this sql : 

select * from some_table where column = 55

is different of tuning this one : 

select * from some_table where column = :bind_variable

Performance Tool kit

In single-user mode :

In multi-user mode :

Others