04 Explain Plan

eExecution Plan

• An execution plan is a hierarchical listings of steps.
• Each step is one of a few basic data access operations known to the database server.
• The most complex statement can be broken down into a series of basic operations
• To execute any  SQL statement oracle has to derive an ‘Execution Plan’
• It is nothing but a tree which contains the order of steps and a relationship between them.

What should you look for in the execution plan?

You should be able to look at the execution plan and assess if the optimizer has made any mistakes in its estimations or calculations, leading to a sub-optimal plan.

• Cardinality : Estimate the number of rows coming out of each operations.
• Access Method:  The way in which data is being accessed , via either a table scan or index access (full table scan, index unique scan, range scan etc)
• Join Method: The method (e.g. hash, sort-merge join , nested loops, sort join, etc) used to join tables with each other.
• Join Type: The type of join (inner, outer, semi, anti…)
• Join Order : The order in which the tables are joined to each other.
• Partition Pruning: Are only the necessary partitions being accessed to answer the query?
• Parallel Execution : In case of parallel execution, is each operation in the plan being conducted in parallel? Is the right data redistribution method being used

What is Cost?

• Oracle optimizer is a cost based optimizer (see column cost in explain plan). This means depending on the cost Oracle will decide the best execution plan.
• The Optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query.
• The optimizer selects the execution plan with the lowest cost where cost represents the estimated resource usage  for that plan.

This means oracle will generate 3-4 execution plans and a cost and it selects the lowest cost execution plan. Also note that the total cost of an execution plan is indicated in row (step) 0.

Rules of an Execution Plan Tree

The basic rules of an Execution Plan Tree are

• An execution plan will contain a root, which has no parents (Ex Step 0)
• A parent will have one or more children, and its ID will be less than the Child(s) ID
• A child can have only one parent, It is indented to the right; in case of many child’s, it will have the same indentation. (Step 2 & 3 has the same indentation in the example above)

Traversing Through the Tree

1. Follow the left most branch to the last child. This is the first operation executed.
2. Now pick the next child and navigate to the deepest operation
3. If no child exist, move up to its parent
4. Go to step 1 until you reach the root node.

How to read an Oracle SQL Execution Plan?

Execution plans are read inside out, starting with the most indented operation.

• The first statement is the one with that has the most indentation
• if 2 statements appear at the same level of indentation the top statement in executed first

An Example of how to read an execution plan

Keeping the following points in mind lets proceed

1. An execution plan will contain a root, which has no parents
2. A parent can have one or more children, and its ID will be less than the child(s) ID
3. A child can have only one parent, it is indented to the right; in case of many child’s, it will have the same indentation
4. The first statement is the one that has the most indentation
5. If 2 statements appear at the same level of indentation, the top statement is executed first.

Example #1

select s.sales_date, s.order_ID, s.product_ID, p.product_name,

p.standard_cost, .color, p.list_price, p.product_size

from sales_history s , products p

where  s.product_ID=p.Product_ID

Using the 5 rules mentioned above , try to enumerate and understand the figure above. Note that operation 2 is on the left side. This is because  when Oracle is executing this tree, it always traverses to the left and then it goes to the right side. So if you look at the entire tree it performs operation 2 because it is at the leftmost side of the tree followed by operation 3 and the hash join.

Example #2

select s.sales_date, s.order_ID, s.product_ID, p.product_name, p.color,

p.list_price, p.product_size

from sales_history s , products p

where  s.product_ID=p.Product_ID

and s.order_ID>10

let me briefly explain the tree generation.  OP 2 is the first one after OP 1, OP 3 then is to the right of OP 2. Since OP 4 is indented after OP 3 , it becomes the child of OP 3. So OP 4 gets executed first, followed by OP 3 and OP 2. OP 1 is a hash join of OP 2 & OP 3.

Example #3

select s.sales_date, s.order_ID, s.product_ID, p.product_name, p.color,

p.list_price

from sales_history s , products p, customer c

where  s.product_ID=p.Product_ID

and s.customer_id=c.customer_ID

If we use the 5 rules, you should be able to generate the tree. Why don’t you give it a try?

If you are wondering what a buffer sort is check this out.

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help  elevate my companies Search engine rankings)