How to Create an Execution Plan with DBMS_XPlan

View All Scripts Login to 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
Area 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 |       3 |      2 |
|   1 |  SORT AGGREGATE            |      |      1 |      1 |      1 |00:00:00.01 |       3 |      2 |
|   2 |   TABLE ACCESS STORAGE FULL| T    |      1 |   1000 |   1000 |00:00:00.01 |       3 |      2 |
------------------------------------------------------------------------------------------------------
 

14 rows selected.

Statement 5
```
select min ( x ) from t
```
```
MIN(X)
1
```

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 \| 3 \| 2 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 3 \| 2 \|
\| 2 \| TABLE ACCESS STORAGE FULL\| T \| 1 \| 1000 \| 1000 \|00:00:00.01 \| 3 \| 2 \|
------------------------------------------------------------------------------------------------------

MIN(X)
1

Statement 6

ONLY in Live SQL, you can also get the plan for the last executed statement by passing :LIVESQL_LAST_SQL_ID for the first argument to dbms_xplan.display_cursor

Get Last Execution with :LIVESQL_LAST_SQL_ID

select *  
from   dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID, null, 'ALLSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  6fg4jt8xtq0t1, child number 0
-------------------------------------
select min ( x ) 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 |       3 |
|   1 |  SORT AGGREGATE            |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS STORAGE FULL| T    |      1 |   1000 |   1000 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 6fg4jt8xtq0t1, child number 0
-------------------------------------
select min ( x ) 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 \| 3 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 3 \|
\| 2 \| TABLE ACCESS STORAGE FULL\| T \| 1 \| 1000 \| 1000 \|00:00:00.01 \| 3 \|
---------------------------------------------------------------------------------------------

Additional Information

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