Oracle Database Performance Tuning Tutorial -5 How to Interpret an Execution Plan

Hi,

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

 

 

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

 

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.

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.