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 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options</pre>
SQL> select * from scott.dept;

DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

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>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<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):
-----------------------------------------------------------

1 - "DEPT"."DEPTNO"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14],
"DEPT"."LOC"[VARCHAR2,13]

24 rows selected.

Elapsed: 00:00:01.08
<pre>

 

 

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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