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,
- sql_id: if you use null, it will be take the last cursor executed in the session.
- Child_id: this parameter depends on sql_id, so if you didn’t set it before, you could not set this one either.
- 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>
An Oracle DBA Journey 