Different flavors of Explain Plan – Part I

There are several ways to get the execution plan of a sql statement now. With the introduction of the package DBMS_XPLAN, we can do much more than just running a query on PLAN_TABLE.

DBMS_XPLAN can retrieve execution plans

• for the SQLs that were executed recently and are in the cursor cache.
• for the SQLs that were aged out of the SGA but can be found in the AWR repository.

1. In Oracle9i, we have the low fat latte, where we run EXPLAIN PLAN on a sql and get the formatted output :

– start of script –

EXPLAIN PLAN for select * from dual ; — replace this sql with your sql

set lines 130
set head off
spool foo.txt
alter session set cursor_sharing=EXACT;
select plan_table_output from table(dbms_xplan.display(‘PLAN_TABLE’,null,’ALL’));
spool off

– end of script –

dbms_xplan.display simply reads from PLAN_TABLE and formats the output as follows:

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |
--------------------------------------------------------------------

2. Next the double shot latte, say, if the SQL has been executed, and you know the hash value of the SQL, you can pull the plan from v$sql_plan using the script below (this is useful in Oracle9i, I sourced this script from Metalink) :

——————————-
—- requires SELECT on V$SQL,V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL
—-
set pagesize 600
set tab off
set linesize 140
set echo off
set long 4000
col TQID format A4
col “SLAVE SQL” format A95 WORD_WRAP
col address format A12
col sql_hash format A15
col exec format 9999
col sql_text format A75 WORD_WRAP
repfooter off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
alter session set “_complex_view_merging”=false;
select hash_value||decode(child_number, 0, ”, ‘/’||child_number) sql_hash,
sql_text
from v$sql
where child_number= 0 and hash_value= &hashvalue;

select ‘| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |’ as “Plan Table” from dual
union all /* QWEKLOIPYRTJHH7 */
select ‘————————————————————————————————————————’
from dual
union all
select rpad(‘| ‘||substr(lpad(‘ ‘,1*(depth))||operation|| decode(options, null,”,’ ‘||options), 1, 33), 34, ‘ ‘)||’|'||
rpad(substr(object_name||’ ‘,1, 19), 20, ‘ ‘)||’|'||
lpad(decode(starts,null,’ ‘,
decode(sign(starts-1000), -1, starts||’ ‘,
decode(sign(starts-1000000), -1, round(starts/1000)||’K',
decode(sign(starts-1000000000), -1, round(starts/1000000)||’M',
round(starts/1000000000)||’G')))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(cardinality,null,’ ‘,
decode(sign(cardinality-1000), -1, cardinality||’ ‘,
decode(sign(cardinality-1000000), -1, round(cardinality/1000)||’K',
decode(sign(cardinality-1000000000), -1, round(cardinality/1000000)||’M',
round(cardinality/1000000000)||’G')))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(outrows,null,’ ‘,
decode(sign(outrows-1000), -1, outrows||’ ‘,
decode(sign(outrows-1000000), -1, round(outrows/1000)||’K',
decode(sign(outrows-1000000000), -1, round(outrows/1000000)||’M',
round(outrows/1000000000)||’G')))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(crgets,null,’ ‘,
decode(sign(crgets-10000000), -1, crgets||’ ‘,
decode(sign(crgets-1000000000), -1, round(crgets/1000000)||’M',
round(crgets/1000000000)||’G'))), 9, ‘ ‘) || ‘|’ ||
lpad(decode(reads,null,’ ‘,
decode(sign(reads-10000000), -1, reads||’ ‘,
decode(sign(reads-1000000000), -1, round(reads/1000000)||’M',
round(reads/1000000000)||’G'))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(writes,null,’ ‘,
decode(sign(writes-10000000), -1, writes||’ ‘,
decode(sign(writes-1000000000), -1, round(writes/1000000)||’M',
round(writes/1000000000)||’G'))), 8, ‘ ‘) || ‘|’ ||
lpad(decode(etime,null,’ ‘,
decode(sign(etime-10000000), -1, etime||’ ‘,
decode(sign(etime-1000000000), -1, round(etime/1000000)||’M',
round(etime/1000000000)||’G'))), 8, ‘ ‘) || ‘|’ as “Explain plan”
from
(select /*+ no_merge */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
from v$sql_plan_statistics_all pa,
V$sql_plan p
where p.hash_value = &hashvalue
and p.CHILD_NUMBER= 0
and p.hash_value = pa.hash_value(+)
and pa.child_number(+) = 0 )
union all
select ‘————————————————————————————————————————’ from dual;
REM
REM Print slave sql
REM
select /* QWEKLOIPYRTJHH7 */
decode(object_node,null,”, substr(object_node,length(object_node)-3,1) || ‘,’ ||
substr(object_node,length(object_node)-1,2)) TQID,
other “SLAVE SQL”
from v$sql_plan vp
where other is not NULL
and hash_value = &hash_value
and CHILD_NUMBER= 0;
——————————-

3. In Oracle 10G we have the gingerbread latte, peppermint mocha, eggnog latte….

Enter the sophisticated DBMS_XPLAN (dbms_xplan is there in 9i, but it is under-documented and just has one sub program DISPLAY), that now has additional procedures namely DISPLAY_CURSOR, DISPLAY_AWR, DISPLAY_SQLSET .. these procedures can peek into the SGA to retrieve the execution plan of a SQL that was recently executed or even dig into AWR to get the execution plan of a SQL that was executed a couple of days ago which may not be in the SGA, but is captured in AWR.

To get the plan of the last executed SQL issue the following:

select 1 from dual; — the sql in interest
set feed off
set linesize 150
set pagesize 2000
select * from table(dbms_xplan.display_cursor(null,null, ‘ALL’));

Below is the output:

Plan hash value: 1388734953
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

If you want to display the execution plan of any SQL in the cursor cache, then get the sql_id and the child_number

SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE ‘%<search_string>%’;

Use the sql_id and child_number as parameters to the following SQL:

set linesize 150
set pagesize 2000
select * from TABLE(dbms_xplan.display_cursor(‘&SQL_ID’, &CHILD));

4. The next flavor is to get execution plan from AWR. To do this we use the dbms_xplan.display_awr procedure. Here, I try to pull out two different execution plans for the same sql.

For example, I created table T1 and dumped the rows of dba_objects in it with about 1M rows and executed the following sql

select /* test */ object_id from t1 where object_name = ‘T1′;

twice, once with an index T1X on T1.OBJECT_NAME and then by dropping the index.

First, I need the sql_id for the test sql.

SELECT sql_id, child_number, substr(sql_text, 1, 30) sqltxt
from v$sql WHERE sql_text LIKE ‘%test%’ ;

SQL_ID        CHILD_NUMBER SQLTXT
------------- ------------ ------------------------------
5py68v0qb20g7            0 select /* test */ object_id fr

Next, I supply the sql_id to dbms_xplan.display_awr procedure as follows.

set linesize 150
set pagesize 2000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(‘5py68v0qb20g7′));

Here, we see two different execution plans for the same sql_id, once when the index was present and then when the index was dropped.

SQL_ID 5py68v0qb20g7
--------------------
select /* test */ object_id from t1 where object_name = 'T1'      

Plan hash value: 2358165537      

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |   301 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |   443 | 13290 |   301   (0)| 00:00:04 |
|   2 |   INDEX RANGE SCAN          | T1X  |   443 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------         

SQL_ID 5py68v0qb20g7
--------------------         

select /* test */ object_id from t1 where object_name = 'T1'         

Plan hash value: 3617692013         

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 55891 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |   443 | 13290 | 55891   (1)| 00:11:11 |
--------------------------------------------------------------------------

continued in … Part II