Site icon IT Tutorial

How to Read or Interpret an Execution Plan in Oracle | Oracle Database Performance Tuning Tutorial -5

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

 

 

 

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.

 

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

 

 

 

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

Exit mobile version