How to find and execution plan and SQL ID for a SQL statement?


This time I will show you how to get the explain plan and SQL_ID from a query statement. If we have the SQL statement we just need to run it and then execute the following query:

select plan_table_output from table(dbms_xplan.display_cursor(null,null,’all’));

The display_cursor function return the execution plan that is allocated on the SQL area. It has 3 parameters,

  1. sql_id: if you use null, it will be take the last cursor executed in the session.
  2. Child_id: this parameter depends on sql_id, so if you didn’t set it before, you could not set this one either.
  3. Format: basically it shows the report, it controls how much information will be displayed about the plan. In the example we will use ALL option because in that case I want to show you all the details about the plan, but there are other options to show, like basic that shows the minimum information about the plan.

If you need a detail of the parameter you can find it here: DISPLAY_CURSOR Function

So let’s get start with the example:

I am going to use the schema scott, so please ensure that you have unlocked the user.


Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options</pre>
SQL> select * from scott.dept;

---------- -------------- -------------
30 SALES          CHICAGO

SQL> set line 300
set pagesize 5000
set timing onSQL> SQL>
SQL> <b>select plan_table_output from table(dbms_xplan.display_cursor(null,null,'all'));</b>

<b>SQL_ID  f6hhpzwv5jrna</b>, child number 0
select * from scott.dept

<b>Plan hash value: 3383998547</b>

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
Query Block Name / Object Alias (identified by operation id):

1 - SEL$1 / DEPT@SEL$1

Column Projection Information (identified by operation id):


24 rows selected.

Elapsed: 00:00:01.08



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s