How to read Oracle explain plan? | Oracle Explain plan examples

In my previous article i have given the details about different oracle indexes and how to use those oracle indexes in detail.In this article i would like to give you example and explanation about Oracle explain plan in detail. The Oracle explain plan is most important concept if you want to start work with SQL performance tuning. What is explain plan? – Explain plan is nothing the the execution plan for the query set by the oracle optimizer by considering multiple factors.

What we will see in this article?

1. What is explain plan ?
2. What are different things to check in explain plan?
3. Example of some explain plans with explanation and steps.

What is Oracle Explain plan?

The Oracle explain plan is nothing but the execution plan in readable format with using which user can check ,analyse and tune the SQL queries. The explain plans are first entry point if you want to tune the query. These are the execution plan or execution path for SQL query execution set by oracle optimizer.

Things to know before we start reading Oracle Explain Plan :

There are set of things you require to know before reading explain plan. I will explain those in detail in separate article.

1.Join Methods in Oracle Engine

2.Access Methods of Oracle E.g. Full table scan

3.Data operations in the query E.g. Filter,Sort,aggregation

1. Join Methods in Oracle Engine :

There are always separate methods to join tables internally in oracle engine. There are three types of join methods we are used internally in oracle sql performance tuning.

1. Nested Loop join : Some oracle SQL statements useds the Nested loop join. For the small tables we require to use the nested loop join.
2. Merge Join : This is another type of join oracle engine is using. These kind of join used when we are using any greater than or less than operators or you can say it as logical operators.
3. Hash Join : This is the fastest join in oracle where the oracle will internally create a key value pair joining two tables and with checking the values of that temporary table it will execute.

Access Methods used by Oracle Optimizer :

I will not go deep in to the access methods and will only name the access methods to remember :

1. Full table scan
2. Range scan
3. Index Unique scan
4. Full index scan
5. Hash access
6. Cluster access
7. Bitmap scan
8. Fast Full index scan
9. Index joins
10. Cluster index

The examples of explain plan :

The next question in mind that how to read the oracle explain plan in detail and what is method to display and analyse it. To generate explain plan there are multiple ways

Oracle SQL developer : You can use the direct F9 button to display the explain plan in detail.

With using multiple queries :

You can use different queries to execute the explain plan in oracle sql.

Query 1 :

Explain plan for ….Query

Query 2 :

select * from table(dbms_xplan.disaplay);

Example :

I am using the example for Employees table in HR schema to explain that in detail .

The Columns in Explain plan :

The explain plan in nothing but the set of steps to query execution. The first column is id column which is the statement no. Then the operation is nothing but any operation performed on the query like select,insert or update.

The rows column gives you number of rows for the specified operation and bytes is number of bytes used for the specified operation. The time is nothing but the time for execution for the specified SQL operation.

Explain plan in table format :

You can also check the explain plan in table format using plan_table. You require to check the specified plan id checking the timestamp column.

select * from plan_table where plan_id=31;

Different explain plan with different operations :

I would like to give more examples to show the different explain plans and operations. To make it more complex i am joining two tables and will show you the explain plan.

Explain plan for
Select * from Hr.employees a, hr.departments b
where a.employee_id = b.manager_id;

If you can execute and check the explain plan there are so many operations. In above query you can check that there is nested join between department and employees table and the employee table is unique index scan. You can check the different operations as well.

This is all about the reading explain plan in oracle with multiple examples. I hope this article is useful to you. If you like this article or if you have any issues with the same kindly comment in comments section.