Oracle Database - Explain Plan from Cursor Cache (DBMS_XPLAN.DISPLAY_CURSOR)

Card Puncher Data Processing

About

This DBMS_XPLAN.DISPLAY_CURSOR function display the execution plan of an already executed statement where the resulting cursor is in the cursor cache.

Privileges

-- Always Needed
grant select on V_$SESSION to hr;
grant select on V_$SQL to hr;
--
grant select on V_$SQL_PLAN to hr;
-- To see the All stats option of the DBMS_XPLAN.DISPLAY_CURSOR function
grant select on V_$SQL_PLAN_STATISTICS_ALL to hr;

where:

Steps

SQL

SELECT /* gerardnico */ e.first_name, e.last_name, e.salary, d.department_name
FROM   hr.employees e, hr.departments d
WHERE  d.department_name IN ('Marketing', 'Sales')
AND    e.department_id = d.department_id;

DBMS_XPLAN.DISPLAY_CURSOR

SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));
SQL_ID  fcxjbjvm5d2tk, child number 0
-------------------------------------
SELECT /* gerardnico */ e.first_name, e.last_name, e.salary, 
d.department_name FROM   hr.employees e, hr.departments d WHERE  
d.department_name IN ('Marketing', 'Sales') AND    e.department_id = 
d.department_id
 
Plan hash value: 1021246405
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |     4 (100)|          |
|   1 |  NESTED LOOPS                |                   |       |       |            |          |
|   2 |   NESTED LOOPS               |                   |    19 |   722 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     2 |    32 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   3 - SEL$1 / D@SEL$1
   4 - SEL$1 / E@SEL$1
   5 - SEL$1 / E@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      INDEX(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "E"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales'))
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30], 
       "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25], "E"."SALARY"[NUMBER,22]
   2 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30], 
       "E".ROWID[ROWID,10]
   3 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
   4 - "E".ROWID[ROWID,10]
   5 - "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25], 
       "E"."SALARY"[NUMBER,22]
 
Note
-----
   - this is an adaptive plan

Support

User has no SELECT privilege on VSQL_PLAN

All the privileges must be granted. Even if the user has the privileg to select VSQL_PLAN, you may get this errors.

cannot fetch plan for SQL_ID

You may get this kind of answer in SQL Plus:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

This can be caused by the serveroutput sql plus system variable because it will add always an extra statement. Just turn it off.

set serveroutput off;





Discover More
Card Puncher Data Processing
Oracle Database - (Actual|Final) Execution Plan

The actual or final plan is the execution that was executed in order to retrieve the result of a SQL. optimizerquery planrow source generator V view contains the actual plan for a query that...
Card Puncher Data Processing
Oracle Database - Adaptive Plan

Adaptive plan is an adaptive functionality. The Adaptive plan functionality enables the optimizer to: generate multiple predetermined subplans defer the subplan decision until execution time based...
Query Plan For The Same Query Response Time
Sql Engine - (Physical|Execution) Plan

A physical plan (or execution plan) is an ordered tree of physical relational operator that represent the execution a query A logical plan is also a ordered tree of relational operator but without the...



Share this page:
Follow us:
Task Runner