Oracle Explain Plan : How to find & Read the plan

We have already posted on Autotrace in Oracle, tkprof, sql_trace. Here we will be looking for the Oracle Explain Plan utility and how we can effectively use it to tune the Oracle sql statement

Table of Contents

What is Explain Plan in oracle

Oracle database internally creates a query execution plan in order to fetch the desired data from the physical tables. The query execution plan is nothing but a set of methods on how the database will access the data from the tables. This query execution plan is crucial as different execution plans will need different cost and time for the query execution
It shows the execution path of a SQL statement

it indicates what tables it will read first and in what order
it indicates what indexes it will use, or if new indexes are needed
It Helps verify that Oracle will retrieve the data the way you expect it to

How to find the Oracle Explain Plan?

Must have full access to a PLAN_TABLE or create PLAN_TABLE with utlxplan.sql delivered by Oracle

Insert the following command at the top of your SQL statement

SQL> explain plan for <sql statement>;
Explained
SQL> select * from table(dbms_xplan.display);

Example

SQL> explain plan for select * from dual;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT

| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 2 | 2 |
| 1 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 |
Note: cpu costing is off

With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan

explain plan
set statement_id = '<identifier>'
into plan_table
for  <sql statement>
;

Example
explain plan
set statement_id = 'x1'
into plan_table
for select user_name, user_id from emp
;

Once this is executed, we can view the explain plan with the help of the below queries. This does not execute the query, it just creates the explain plan

col operation format a13 trunc
col options format a15 trunc
col object_name format a23 trunc
col id format 9999
col parent_id format 9999
col position format 9999
col operations format a25
select lpad(' ',2*level) || operation operations,options,object_name
from plan_table
where statement_id = 'x1'
connect by prior id = parent_id and statement_id = 'x1'
start with id = 1 and statement_id = 'x1'
order by id;

Or
SQL> set linesize 132  
SQL> SELECT * FROM TABLE(dbms_xplan.display);
or
SQL> set linesize 132  
SQL> SELECT * FROM TABLE(dbms_xplan.display(statement_id=>'x1'));

If you want the output in HTML format, you can use the below query

set pages 0 head off
set linesize 132
set long 1000000
col xplan format a100
spool id1.html
SELECT dbms_xplan.display_plan(statement_id => 'id1',type=>'HTML') AS XPLAN
FROM dual;
spool off;

if you want the additional information also, you can execute the below statement

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'))

How to Read the explain Plan

Read innermost out, top/down
Join operations always require two sets. The order you read the sets is top down, so the first set is the driving set and the second is the probed set. In the case of a nested loop, the first set is the outer loop. In the case of a hash join, the first set is used to build the hash table.
One join is performed at a time, so you only need to consider two sets and their join operation at any one time.

Let’s take an example

Here the complete order will be 3,2,4,2,1,5,1,0
(a) It will first go to step 3, do a full table scan and pass the rows to step 2 which will build the hash table
(b) It will go to 4 and select the rows and give the rows to step 2. Here Hash join will be done.
(c) Now the rows selected will be given to step 1 which will build the hash table
(d) It will go to step 5, select the rows and pass it to step 1. Here Hash join will be done.
(e) Since no more children, it will output the rows in step 0

What to look for ?

-Look for TABLE ACCESS… (FULL) …
-Costly if the table is big
-Costly if performed many times
-Look for INDEX… (RANGE SCAN)…
-Expected for non-unique indexes
-Suspicious for unique indexes

Joins Info from Tuning Perspective

Nested Loops

Good for on-line screens and reports
Read all rows from table 1

Then access table 2 once for each row returned from table 1
Fastest if rows returned from table 1 are small , access to table2 is inexpensive.
This means either a UNIQUE lookup or a SMALL Range Scan.

Merge Join

Better than nested loops for joining a large number of rows

Hash Join

Common in Parallel Queries
Better than Merge Join
Smallest table is passed over and a hashing algorithm is applied to each row to create a hash table and a bitmap.

Second table is passed over and a hashing algorithm applied and check for match (ie. joins)
The bitmap is used as a quick lookup and is especially useful when hash table is too large to fit in memory.

Sort Merge Join

Rows are produced from table 1 and are then sorted
Rows are produced from table 2 and sorted by the same sort key as table 1
Table 1 and 2 are NOT accessed concurrently

Sorted rows from both sides are then merged together(joined)

how to get execution plan in oracle using sql_id in Cursor

If the statement is in Cursor( Shared pool), we can find the explain plan taken by the Oracle to execute the query using the below query. Here you need to provide the sql_id of the sql statement

col operation for a20
col object_name for a20
col options for a20
col optimizer for a12
col child_number a3
SELECT child_number, id , lpad (' ', depth) || operation operation , options , object_name , optimizer , cost
FROM V$SQL_PLAN
WHERE sql_id = '&sql_id'
ORDER BY child_number, id;

OR
select * from table(dbms_xplan.display_cursor('&1', NULL, 'ALL'));

OR
select * from table( DBMS_XPLAN.display_cursor('&1', NULL,'ADVANCED ROWS ALLSTATS'));
The above statement will provide lot of additional information

how to get execution plan in oracle using sql_id in AWR/how to get old execution plan in oracle

If a sql statement has been executed in past and it is in the Oracle Automatic Workload Repository, then we can find the explain plan using the below command

select * from table( DBMS_XPLAN.DISPLAY_AWR('&1', NULL,NULL,'ADVANCED ROWS ALLSTATS'));

This will list all the available plan in AWR and this will include the old execution plan also

Actual Sql execution plan

The plan you get through by using explain plan command is not an actual execution plan, it is the estimated execution plan. Oracle may choose a different plan at the time of execution depending on the table statistics and bind variable. So sometimes it will be better to execute the query and then find the explain plan for the query in V$SQL_PLAN

How to find the change in explain plan in oracle for a sql id

Many times because of stats issues, addition in the index, and other parameters, The sql start performing badly. One of the reasons could be a change in the execution plan of the sql. The below sql will show the history of all the execution/explain plans taken by the particular sql. This is very useful when your developer complains about performance on the sql query which was running nice yesterday

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
SELECT ss.snap_id,
ss.instance_number node,
begin_interval_time,
sql_id,
plan_hash_value,
NVL(executions_delta,0) execs,
(elapsed_time_delta/DECODE(NVL(executions_delta,0),0,1,executions_delta))/1000000
avg_etime,
(buffer_gets_delta /DECODE(NVL(buffer_gets_delta,0),0,1,executions_delta))
avg_lio
FROM DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT SS
WHERE sql_id = NVL('&sql_id','fhf133jjk234')
AND ss.snap_id = S.snap_id
AND ss.instance_number = S.instance_number
AND executions_delta > 0
ORDER BY 1,2,3
/