About
A physical plan (or execution plan) is an ordered tree of physical relational operator that represent the execution a query
- A logical plan is also a ordered tree of relational operator but without the implementation (ie how to perform the operation)
- A query plan is the best physical plan chosen by the optimizer to be executed.
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:
- the plan that the optimizer chose before starting with
- the final plan with:
- Explain plan from the cursor cache (cursor loaded in memory)
You can influence the execution plan (e.g. the query optimizer) by
- setting the optimizer goal (either throughput or response time)
- gathering representative statistics
- using hints to instruct the optimizer about how a statement should be executed.
- changing some initialization parameters
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.
Articles Related
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 :