How to Create an Execution Plan with DBMS_XPlan

View All Scripts Login and Run Script

Script Name How to Create an Execution Plan with DBMS_XPlan
Description An example of how to get the execution plan for a query using dbms_xplan. For further methods, read this blog post: https://blogs.oracle.com/sql/how-to-create-an-execution-plan
Category SQL General
Contributor Chris Saxon (Oracle)
Created Wednesday July 19, 2017

Statement 1
This enables Oracle Database to capture rows processed, time taken, etc. for each step of the plan. Alternatively you could use the /*+ gather_plan_statistics */ hint in each query you wish to diagnose.
```
alter session set statistics_level = all
```
Statement processed.

Statement 2

create table t as 
  select rownum x from dual connect by level <= 1000

Table created.

Statement 3
The "MY QUERY" comment makes it easier for you to find your statement in v$sql. You use this view to get the SQL_ID of your query, which you use to get the plan. If you're tracking many statements, place a different comment in each ;)
```
select /* MY QUERY */count(*) from t
```
COUNT(*)
1000

COUNT(*)
1000

Statement 4

This searches for your SQL in v$sql by looking for the comment in the SQL_text. It then passes the SQL_ID to DBMS_XPlan to get the execution plan for the query. HT to Lukas Eder for coming up with this: https://blog.jooq.org/2012/09/19/how-to-get-oracle-execution-plans-with-starts-e-rows-a-rows-and-a-time-columns/

select p.*  
from v$sql s, table (  
  dbms_xplan.display_cursor (  
    s.sql_id, s.child_number, 'ALLSTATS LAST'  
  )  
) p  
where s.sql_text like '%MY QUERY%'  
and   s.sql_text not like '%not this%'

PLAN_TABLE_OUTPUT
SQL_ID 8k3njz4u929m4, child number 0
-------------------------------------
select /* MY QUERY /count() from t

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \|
----------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 10 \| 2 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 10 \| 2 \|
\| 2 \| TABLE ACCESS FULL\| T \| 1 \| 1000 \| 1000 \|00:00:00.01 \| 10 \| 2 \|
----------------------------------------------------------------------------------------------

SQL_ID 8avbr47zf2fcm, child number 0
-------------------------------------
select /+ MY QUERY /count(*) from t

Plan hash value: 2966233522

-------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
-------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 10 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 10 \|
\| 2 \| TABLE ACCESS FULL\| T \| 1 \| 1000 \| 1000 \|00:00:00.01 \| 10 \|
-------------------------------------------------------------------------------------

28 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials