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.
Articles Related
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;