Hi,

I will explain How to Interpret an Execution Plan in this article.

Read or Interpret an Execution Plan in Oracle

You should read the following article before this, if you want to learn more details about Execution Plan.

What is the Execution Plan and How to View Execution Plan in Oracle | Oracle Database Performance Tuning Tutorial -4

Interpret an Execution Plan in Oracle

Execution plan steps are performed in a parent-child relationship between steps, they are not performed in the order in which they are numbered as follows.

Execution starts the leaf at the left on each level of the tree in the tree format.

There are some columns such as ID, PARENT_ID, and POSITION in the PLAN_TABLE and V$SQL_PLAN to retrieve the Tree. These columns correspond to the id, pid, and pos fields in the trace file.

You should start to read an execution plan from the Top ( with id=1 ) which is the root node in the tree structure, and then You should find the leaf or child that feed this root node  which have parent_id or pid with value 1. 

Tree structure is very important to read and interpret an execution, so you can draw plan as a tree with the following steps.

1. Get the ID with the lowest number and place it at the Top of Tree.
2. Look for rows which have a PID (parent) equal to this value.
3. Place these in the tree below the Parent according to their POS values from the lowest to the highest, ordered from left to right. 
4. After all the IDs for a parent have been found, move down to the next ID and repeat the process, finding new rows with the same PID.

You can interpret the Standard explain plan with the following steps.

1. Start at the Top of Tree.
2. Move down the row sources until you get to one which produces data, but does not consume any. This is the start row source.
3. Look at the siblings of this row source. These row sources are executed next.
4. After the children are executed, the parent is executed next.
5. Now that this parent and its children are completed, work back up the tree, and look at the siblings of the parent row source and its parents. Execute as before.
6. Move back up the plan until all row sources are exhausted.

You can interpret the Standard Tree Structure with the following steps.

1. Start at the top.
2. Move down the tree to the left until you reach the left node. This is executed first.
3. Look at the siblings of this row source. These row sources are executed next.
4. After the children are executed, the parent is executed next.
5.  Now that this parent and its children are completed, work back up the tree, and look at the siblings of the parent row source and its parents. Execute as before.
6. Move back up the tree until all row sources are exhausted.

For example; Let’s go to interpret an execution plan of any SQL and its explain plan, Tree structure as follows.

Execution order of this SQL should be 3 – 5 – 4 – 2 – 6 – 1

• 3: The plan starts with a full table scan of EMP (ID=3).
• 5: The rows are passed back to the controlling nested loops join step (ID=2), which uses them to execute the lookup of rows in the PK_DEPT index in ID=5.
• 4: The ROWIDs from the index are used to lookup the other information from the DEPT table in ID=4.
• 2: ID=2, the nested loops join step, is executed until completion.
• 6: After ID=2 has exhausted its row sources, a full table scan of SALGRADE in ID=6 (at the same level in the tree as ID=2, therefore, its sibling) is executed.
• 1: This is used to filter the rows from ID2 and ID6.

You can look at the Execution order of any SQL via Enterprise Manager or Enterprise Manager Cloud Control as follows.

Or you can see it as Graphical or Tree structure via Enterprise Manager or Enterprise Manager Cloud Control as follows.

You can review the Execution plan with the following steps or points.

• Firstly, Start reviewing the table that has most selective filter.
• Secondly, Check to see whether the access paths are optimal
• The join method is appropriate or not ?
• Views are used efficiently or not ?
• The indexes available on the tables or not ?
• Indexes are used correctly by execution plan or not ?
• Used execution plan is the optimal plan or not ?

If the execution plan is insufficient and poor performance for you, then try to use the following tools to find any new plan.

• SQL Tuning Advisor
• SQL Access Advisor
• SQL Performance Analyzer

Or look for the good plans in the history for that SQL statement, if you find it, you can set it or create a SQL Plan baseline for it.

I will continue to explain Performance tuning tutorial in the next articles.

Next post link about Performance Tuning Tutorial is as follows.

How to Use SQL Trace , Trcsess and TKPROF | Oracle Database Performance Tuning Tutorial -6

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

SQL Tuning Tips and Tricks Tutorial in Oracle -3