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

> Database > Oracle Database

1 - 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.

Advertising

3 - 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:

4 - Steps

4.1 - 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;

4.2 - 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 / [email protected]$1
   4 - SEL$1 / [email protected]$1
   5 - SEL$1 / [email protected]$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

5 - Support

5.1 - User has no SELECT privilege on V$SQL_PLAN

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

5.2 - 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;
Advertising
db/oracle/display_cursor.txt · Last modified: 2017/09/13 16:16 by gerardnico