Ever wondered how to display the execution plan of a SQL Statement quickly?
Are you dreading the question “What is the execution plan of this SQL?”?
Are you struggling to figure out what the execution plan is?

If you answered yes to any of these questions, then this post is definitely for you.

Do you know that most DBAs get stuck when trying to display the execution plan?
Do you know why they get stuck? What is the “bottleneck” in the troubleshooting?

I imagine you don’t know, so let me share a little secret with you.

The #1 obstacle to getting the execution plan of a SQL statement, is not being able to properly identify the parent cursor (SQL_ID) associated with the SQL statement.

Once you know the SQL_ID associated with the SQL Statement, displaying the execution plan is easy.

Some people might argue that you can run the EXPLAIN PLAN statement on the SQL, and display the execution plan, so ultimately you don’t need to know the SQL_ID.

And I would tell you, that the EXPLAIN PLAN will not return the actual execution plan. EXPLAIN PLAN returns a possible execution plan. Be careful with EXPLAIN PLAN plans!

If the SQL_ID is so important, then that is what I will focus on first today!

I will show you how to find the SQL_ID of a SQL statement that you need to find the execution plan for.
Once we know how to obtain the SQL_ID, getting the execution plan is a breeze.

1. Get The SQL_ID Of A SQL Statement
2. Display Execution Plan From Library Cache
3. Display Execution Plan From AWR
4. Display Execution Plan From Statspack
5. Display Execution Plan for EXPLAIN PLAN

1. Get The SQL_ID Of A SQL Statement

When someone asks you about the execution of a SQL statement, you need to know the SQL_ID (the parent cursor) and best if you know the child cursor as well, associated with that statement. To find the SQL_ID and the child cursor of a statement, there are really two scenarios I can think of:
-the SQL statement is currently running in the database
-the SQL statement is no longer running in the database. In this case the cursor might still be in the library cache. If not, you might find the information in the AWR.

Scenario #1

The SQL Statement that you need to look at, is currently executed by a session in the database. In this case, if you know the username of the user running the statement, it is pretty easy to identify the session and the SQL_ID, just query V$SESSION:

select sid, serial#, username, status, sql_id, sql_child_number 
from gv$session
where username='DIANA';

The DBA life would be so easy, if this would be the case all the time. But usually it is not this simple.

Why?

Because many times the username looks something like HRAPP, and it is an application user, and there are about 10-50 sessions in the database for that user. So go figure which one is running the SQL statement that you are investigating.

What do you do now?

I usually run the following sql, also named as sessions.sql, which will return all the ACTIVE and INACTIVE sessions in the database.

/*
sessions.sql
Example: @sessions.sql
Copyright @2016 dbaparadise.com
*/
set linesize 200
set pagesize 100
clear columns
col inst for 99999999
col sid for 9990
col serial# for 999990
col username for a12
col osuser for a16
col program for a10 trunc
col Locked for a6
col status for a1 trunc print
col "hh:mm:ss" for a8
col SQL_ID for a15
col seq# for 99990
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
select inst_id inst, sid , serial# , username
, ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1
, length(osuser)-14))) osuser
, substr(program,instr(program,'/',-1)+1
, decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program)
, instr(program,'.')-1),instr(program,'@')-1)) program
, decode(lockwait,NULL,' ','L') locked, status
, to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss"
, SQL_ID, seq# , event
, decode(state,'WAITING','WAITING '|| 
​​​​​​​  lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6)
,'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state
, substr(module,1,25) module, substr(action,1,20) action
from GV$SESSION
where type = 'USER'
and audsid != 0 -- to exclude internal processess
order by inst_id, status, last_call_et desc, sid
/

So when you run the above sessions.sql script, you might have a few candidates for the SQL_ID. What I like to do in this case is to capture the SQL_ID for all these candidates. Then display the text of the SQL Statements that are associated with each SQL_ID, by using the following script: sqltext.sql.

You might need to run this script a few times for different SQL_IDs, until you find the one you were looking for.

/*
sqltext.sql
Example @sqltext.sql 6rv5za4tfnjs8
Copyright @2016 dbaparadise.com
*/
set define '&'
set verify off
define sqlid=&1
col sql_text for a80 word_wrapped
col inst_id for 9
break on inst_id
set linesize 150

--query V$ views 
select inst_id, sql_text
from gv$sqltext
where sql_id = '&&sqlid'
order by inst_id,piece
/

--query historical DBA view, if cursor no longer in memory.

select to_char(substr(sql_text,1,4000)) sql_text from dba_hist_sqltext 
where sql_id='&sqlid';

2. Display Execution Plan From Library Cache

You have found the SQL_ID for your statement. This is great, you should celebrate, you completed the most difficult task.

If the cursor is still in the library cache, use dbms_xplan package to display the execution plan for the cursor. This is the easiest way.
Of course there are other ways to get the plan, by querying V$ views.

select plan_table_output from table(dbms_xplan.display_cursor('SQL_ID'));

select plan_table_output from table(dbms_xplan.display_cursor('6ahf7wtdbh8xz'));

The above statement will display all the child cursors associated with the SQL_ID.
There are many things that you can pass as parameters to the display_cursor function, which could be the topic of another article.

Just remember to use the display_cursor function with the SQL_ID, and you will display all the execution plans associated with the child cursors.

3. Display Execution Plan From AWR

Don’t panic if the cursor is no longer in the library cache.
Before I knew about this option, I used to ask the user to run the script or report again, so I could capture the SQL_ID from memory.

If you are using AWR, to get the execution plan is easy, just make sure you are licensed for it:

select plan_table_output from table(dbms_xplan.display_awr('7p4b26hkcfyx7'));

Again, there are multiple parameters that you could pass, for now just remember to pass the SQL_ID.

4. Display Execution Plan From Statspack

Does Statspack even exists? Yes it does! For those of us less fortunate, that don’t have AWR, Statspack is our friend!

Execution plans for statspack are stored in the stats$sql_plan table.

This table is only populated when level 6 or greater is used for statspack snapshots.
If you are using a level lower than 6, then the execution plan is not stored in the table.

A great method to display the execution plan from stats$sql_plan would be to load the content into the PLAN_TABLE and then use dbms_xplan.display to display it.

5. Display Execution Plan for EXPLAIN PLAN

And finally, if you just want to run the EXPLAIN PLAN on your SQL statement, you can do that too.
I can’t emphasize enough, that the results you are getting, might not be accurate. The actual execution plan might be different than the EXPLAIN PLAN version.

How does this work?

You run the EXPLAIN PLAN statement, then you use dbms_xplan.display to display the execution plan:

EXPLAIN PLAN FOR
SELECT * FROM emp e
where emp_id=100;

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY); 

With what you learned today, you should be ready to display any execution plan!

There are many other ways to display execution plans, by querying V$ views, or historical views, by using Enterprise Manager and many more. I shared with you today some of the methods that I use.

What is your favorite way to display execution plans? Hit reply and let me know, I read every email!