Oracle DBMS_XPLAN
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views. Finally, it displays plans from a SQL plan baseline.
AUTHID CURRENT_USER
Constants
Name Data Type Value
UNKNOWN_DIFF_CLASS NUMBER POWER(2,31)
Data Types CREATE OR REPLACE TYPE sys.dbms_xplan_type
AS OBJECT (plan_table_output VARCHAR2(300));

CREATE OR REPLACE TYPE sys.dbms_xplan_type_table
AS TABLE OF dbms_xplan_type;

CREATE TYPE sys.generic_plan_object AUTHID CURRENT_USER
AS OBJECT (plan_source NUMBER,
MEMBER FUNCTION get_plan_rows RETURN sql_plan_table_type) NOT FINAL
/

TYPE display_map_record IS RECORD (
oper_id    NUMBER,  -- operation id
display_id NUMBER,  -- display id
parent_id  NUMBER,  -- parent display id
part_id    NUMBER,  -- partitioning display id
depth      NUMBER,  -- display depth
skipped    NUMBER); -- whether to skip this in display

TYPE display_map_tab_type IS TABLE OF display_map_record;

TYPE plan_record IS RECORD(
id                NUMBER,        -- operation id
parent_id         NUMBER,        -- parent id
partition_id      NUMBER,        -- partition id
timestamp         DATE,          -- time of plan generation
optimizer         VARCHAR2(20),  -- optimizer mode
position          NUMBER,        -- position used to detect RBO
search_columns    NUMBER,        -- search columns
depth             NUMBER,        -- depth
operation         VARCHAR2(300), -- operation
options           VARCHAR2(255), -- options
object_name       VARCHAR2(64),  -- name of the object
object_owner      VARCHAR2(64),  -- owner of the object
object_type       VARCHAR2(30),  -- type of the object
object_instance   NUMBER,        -- object instance
cardinality       NUMBER,        -- optimizer cardinality
bytes             NUMBER,        -- optimizer bytes
temp_space        NUMBER,        -- temp space consumption
cost              NUMBER,        -- optimizer cost
io_cost           NUMBER,        -- optimizer cpu cost
cpu_cost          NUMBER,        -- optimizer cpu cost
time              NUMBER,        -- optimizer estimated time
pstart            VARCHAR2(255), -- pruning info (start)
pstop             VARCHAR2(255), -- pruning info (stop)
object_node       VARCHAR2(128), -- tq id for PQ
other_tag         VARCHAR2(255), -- other_tag for PQ
distrib           VARCHAR2(30),  -- PX distribution
projection        VARCHAR2(4000),  -- projection information
access_pred       VARCHAR2(4000),  -- access predicates
filter_pred       VARCHAR2(4000),  -- filter predicates
other             VARCHAR2(32767), -- other tag, slave SQL
qblock_name       VARCHAR2(64),  -- query block name
object_alias      VARCHAR2(65),  -- object alias
other_xml         CLOB,          -- other_xml
sql_profile       VARCHAR2(64),  -- sql_profile in v$sql
sql_plan_baseline VARCHAR2(30),  -- sql_plan_baseline in v$sql
starts            NUMBER,        -- number of starts
outrows           NUMBER,        -- number of rows output by node
crgets            NUMBER,        -- number of cr buffer gets
cugets            NUMBER,        -- number of cr buffer gets
reads             NUMBER,        -- number of physical reads
writes            NUMBER,        -- number of physical writes
etime             NUMBER,        -- elapsed time
mem_opt           NUMBER,        -- optimal memory requirement
mem_one           NUMBER,        -- one-pass memory requirement
last_mem_used     NUMBER,        -- last memory used
last_mem_usage    VARCHAR2(10),  -- last memory usage (e.g. OPTIMAL)
mem_opt_cnt       NUMBER,        -- count of optimal memory usage
mem_one_cnt       NUMBER,        -- count of one-pass memory usage
mem_multi_cnt     NUMBER,        -- count of multi-pass memory usage
max_tmp_used      NUMBER,        -- max temp used
last_tmp_used     NUMBER);       -- last temp used

TYPE plan_table IS TABLE OF plan_record;

TYPE num_tab_type IS TABLE OF NUMBER;
Dependencies
ADVISOR_OBJECT DBMS_SQLTUNE_UTIL0 PRVT_REPORT_TAGS
AWR_OBJECT DBMS_SQLTUNE_UTIL1 PRVT_SQLADV_INFRA
CURSOR_CACHE_OBJECT DBMS_SQLTUNE_UTIL2 PRVT_SQLPA
DBA_ADVISOR_FINDINGS DBMS_STANDARD SDO_RDF
DBA_ADVISOR_SQLPLANS DBMS_SWRF_INTERNAL SDO_RDF_INTERNAL
DBA_ADVISOR_TASKS DBMS_SWRF_REPORT_INTERNAL SPM_OBJECT
DBMS_ADVISOR DBMS_SYSTEM SQLPROF_ATTR
DBMS_ASSERT DBMS_SYS_ERROR SQLSET_OBJECT
DBMS_COMPARISON DBMS_WORKLOAD_REPOSITORY SQLSET_ROW
DBMS_LOB DBMS_XPLAN_INTERNAL SQL_BINDS
DBMS_OUTPUT DBMS_XPLAN_LIB SQL_PLAN_TABLE_TYPE
DBMS_REPORT DBMS_XPLAN_TYPE SQL_PROFILE_OBJECT
DBMS_SMB_INTERNAL DBMS_XPLAN_TYPE_TABLE V$QUERY_BLOCK_ORIGIN
DBMS_SPM_INTERNAL DUAL V_$SESSION
DBMS_SQL GENERIC_PLAN_OBJECT WRI$_REPT_PLAN_DIFF
DBMS_SQLDIAG PLAN_OBJECT_LIST WRI$_REPT_XPLAN
DBMS_SQLTCB_INTERNAL PLAN_TABLE XMLSEQUENCETYPE
DBMS_SQLTUNE PLAN_TABLE_OBJECT XMLTYPE
DBMS_SQLTUNE_INTERNAL PLITBLM XQSEQUENCE
Documented Yes
First Available 9.2
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsxpln.sql
Subprograms
BUILD_PLAN_XML DISPLAY_CURSOR FORMAT_SIZE2
COMPARE_CURSOR DISPLAY_PLAN FORMAT_TIME_S
COMPARE_EXPLAIN DISPLAY_SHARD_PLANS GET_CURSOR_ROWS
COMPARE_PLANS DISPLAY_SQLSET GET_FINAL_PLAN
DIFF_PLAN DISPLAY_SQL_PATCH_PLAN GET_PLANDIFF_REPORT_XML
DIFF_PLAN_AWR DISPLAY_SQL_PLAN_BASELINE GET_PLAN_ROWS
DIFF_PLAN_CURSOR DISPLAY_SQL_PROFILE_PLAN I_DISPLAY_CURSOR
DIFF_PLAN_OUTLINE DISPLAY_WORKLOAD_REPOSITORY I_DISPLAY_SMB_PLAN
DIFF_PLAN_SQL_BASELINE FORMAT_NUMBER PREPARE_PLAN_XML_QUERY
DISPLAY FORMAT_NUMBER2 PREPARE_RECORDS
DISPLAY_AWR FORMAT_SIZE VALIDATE_FORMAT
 
BUILD_PLAN_XML
Return the last plan, or a named plan, explained as XML dbms_xplan.build_plan_xml(
table_name   IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
plan_id      IN NUMBER   DEFAULT NULL,
format       IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL,
plan_tag     IN VARCHAR2 DEFAULT 'plan',
report_ref   IN VARCHAR2 DEFAULT NULL)
RETURN XMLTYPE;
EXPLAIN PLAN
SET STATEMENT_ID = 'abc' FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

set pagesize 45
set linesize 121
set long 1000000
col xplan format a100

SELECT dbms_xplan.build_plan_xml(statement_id => 'abc') AS XPLAN
FROM dual;
 
COMPARE_CURSOR (new 21c)
Compare two plans populated in the cursor cache dbms_xplan.compare_cursor(
sql_id1   IN VARCHAR2 DEFAULT NULL,
sql_id2   IN VARCHAR2 DEFAULT NULL,
childnum1 IN INTEGER  DEFAULT NULL,
childnum2 IN INTEGER  DEFAULT NULL,
type      IN VARCHAR2 := 'TEXT',
level     IN VARCHAR2 := 'TYPICAL',
section   IN VARCHAR2 := 'ALL')
RETURN CLOB;
TBD
 
COMPARE_EXPLAIN (new 21c)
Compare two plans generated by EXPLAIN PLAN

In the demo, at right, ALPHA and BETA are used to tag the two plans for analysis

The plans are forced to be different by the USE_MERGE hint		 dbms_xplan.compare_explain(
statement_id1 IN VARCHAR2 DEFAULT NULL,
statement_id2 IN VARCHAR2 DEFAULT NULL,
plan_id1      IN NUMBER   DEFAULT NULL,
plan_id2      IN NUMBER   DEFAULT NULL,
type          IN VARCHAR2 := 'TEXT',
level         IN VARCHAR2 := 'TYPICAL',
section       IN VARCHAR2 := 'ALL')
RETURN CLOB;
conn sys as sysdba

EXPLAIN PLAN FOR
SELECT /* ALPHA */ DISTINCT s.srvr_id
FROM c##uwclass.servers s, c##uwclass.serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT statement_id, plan_id, other FROM plan_table$ WHERE timestamp > SYSDATE-5/1440;

EXPLAIN PLAN FOR
SELECT /*+ USE_MERGE(s,i) BETA */ DISTINCT s.srvr_id
FROM c##uwclass.servers s, c##uwclass.serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT statement_id, plan_id, other FROM plan_table$ WHERE timestamp > SYSDATE-5/1440;

DECLARE
 c CLOB;
BEGIN
  c := dbms_xplan.compare_explain(plan_id1=>'9', plan_id2=>'10');
  dbms_output.put_line(c);
END;
/

COMPARE PLANS REPORT
----------------------------------------------------------------------------------------
Current user : SYS
Total number of plans : 2

Number of findings : 1
----------------------------------------------------------------------------------------

COMPARISON DETAILS
----------------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)

Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SYS
Plan Table Name : PLAN_TABLE
Statement ID :

Plan ID : 9
Plan Database Version : 21.0.0.0
Parsing Schema : "SYS"
SQL Text : No SQL Text

Plan
-----------------------------
Plan Hash Value : 3029349409

----------------------------------------------------------------------------------------
|  Id | Operation               | Name                     |Rows| Bytes|Cost| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                          |  11|   88 |  4 | 00:00:01 |
|   1 |  RESULT CACHE           |g2p4d5h9bvdyvdmz3w5p0gvs99|  11|   88 |  4 | 00:00:01 |
|   2 |   HASH UNIQUE           |                          |  11|   88 |  4 | 00:00:01 |
|   3 |    NESTED LOOPS SEMI    |                          | 999| 7992 |  3 | 00:00:01 |
|   4 |     INDEX FAST FULL SCAN| PK_SERV_INST             | 999| 3996 |  3 | 00:00:01 |
| * 5 |     INDEX UNIQUE SCAN   | PK_SERVERS               |   1|    4 |  0 | 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
* 5 - access("S"."SRVR_ID"="I"."SRVR_ID")
----------------------------------------------------------------------------------------

Plan Number : 2

Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SYS
Plan Table Name : PLAN_TABLE
Statement ID :
Plan ID : 10
Plan Database Version : 21.0.0.0
Parsing Schema : "SYS"
SQL Text : No SQL Text

Plan
-----------------------------
Plan Hash Value : 3091993341
-----------------------------------------------------------------------------------------
|  Id | Operation                | Name                     |Rows| Bytes|Cost| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                          |  11|   88 |  6 | 00:00:01 |
|   1 |  RESULT CACHE            |asc5ncasy3b3491b7u27avv3td|  11|   88 |  6 | 00:00:01 |
|   2 |   SORT UNIQUE NOSORT     |                          |  11|   88 |  6 | 00:00:01 |
|   3 |    MERGE JOIN SEMI       |                          |  11|   88 |  5 | 00:00:01 |
|   4 |     INDEX FULL SCAN      | PK_SERVERS               | 141|  564 |  1 | 00:00:01 |
| * 5 |     SORT UNIQUE          |                          | 999| 3996 |  4 | 00:00:01 |
|   6 |      INDEX FAST FULL SCAN| PK_SERV_INST             | 999| 3996 |  3 | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
* 5 - access("S"."SRVR_ID"="I"."SRVR_ID")
* 5 - filter("S"."SRVR_ID"="I"."SRVR_ID")

Hint Report
(identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1), E - Syntax Error (1))
-------------------------------------------------------------------------------

1 - SEL$1
E - BETA

4 - SEL$1 / "S"@"SEL$1"
U - USE_MERGE(s,i)

Comparison Results (1):
-----------------------------
1. Query block SEL$1: Join order is different at position 1
(reference plan: "I"@"SEL$1", current plan: "S"@"SEL$1").

----------------------------------------------------------------------------------------
PL/SQL procedure successfully completed.
 
COMPARE_PLANS
Compare each plan in a list with a reference plan dbms_xplan.compare_plans(
reference_plan    IN sys.generic_plan_object,
compare_plan_list IN plan_object_list,
type              IN VARCHAR2 := 'TEXT',
level             IN VARCHAR2 := 'TYPICAL',
section           IN VARCHAR2 := 'ALL')
RETURN CLOB;
TBD
 
DIFF_PLAN
Compares two SQL plans reference plan: implicitly defined target plan: a plan generated by the given outline. dbms_xplan.diff_plan(
sql_text  IN CLOB,
outline   IN CLOB,
user_name IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD
 
DIFF_PLAN_AWR
Compares two sql plans specified by the given plan hash ids

Looks like it isn't quite ready for prime time but Oracle will tell me it is an unsupported proc so we'll just have to keep an eye on it as patches are released.		 dbms_xplan.diff_plan_awr(
sql_id           IN VARCHAR2,
plan_hash_value1 IN NUMBER,
plan_hash_value2 IN NUMBER)
RETURN VARCHAR2;
SELECT DISTINCT a.sql_id, a.plan_hash_value, b.plan_hash_value
FROM dba_hist_sql_plan a, dba_hist_sql_plan b
WHERE a.sql_id = b.sql_id
AND a.plan_hash_value <> b.plan_hash_value
AND a.timestamp > sysdate-1
AND b.timestamp > sysdate-1;

SQL_ID        PLAN_HASH_VALUE PLAN_HASH_VALUE
------------- --------------- ---------------
12a2xbmwn5v6z      3607810482       518152518
2crngsbggkkzv      3967021048      1901716261
836b98xx998x3      1844019431      1681323734
836b98xx998x3      1681323734      1844019431
12a2xbmwn5v6z       518152518      3607810482
dp2c6pq28u5jr      3350759982      3059474834
dp2c6pq28u5jr      3059474834      3350759982
2crngsbggkkzv      1901716261      3967021048

set serveroutput on

SELECT dbms_xplan.diff_plan_awr('12a2xbmwn5v6z', 3607810482, 518152518)
FROM dual;
            *
ERROR at line 1:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "SYS.PRVT_ADVISOR", line 4137
ORA-13608: The specified name NULL is invalid.
ORA-06512: at "SYS.PRVT_ADVISOR", line 4181
ORA-06512: at "SYS.DBMS_ADVISOR", line 363
ORA-06512: at "SYS.DBMS_SQLTUNE", line 903
ORA-06512: at "SYS.DBMS_XPLAN", line 7942
ORA-06512: at "SYS.DBMS_XPLAN", line 8769
 
DIFF_PLAN_CURSOR
Compares two sql plans derived from the given cursor child # dbms_xplan.diff_plan_cursor(
sql_id            IN VARCHAR2,
cursor_child_num1 IN NUMBER,
cursor_child_num2 IN NUMBER)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba

SELECT vs.sql_id, COUNT(*)
FROM v$sql vs
GROUP BY vs.sql_id
HAVING COUNT(*) > 2;

SELECT vs.child_number
FROM v$sql vs
WHERE vs.sql_id = 'bhvyz9bgyrhb2';

set serveroutput on

DECLARE
  2   x VARCHAR2(13) := '1p5grz1gs7fjq';
  3   y NUMBER := 5;
  4   z NUMBER := 8;
  5   r VARCHAR2(4000);
nbsp; 6  BEGIN
  7    r := dbms_xplan.diff_plan_cursor(x, y, z);
  8    dbms_output.put_line(r);
  9  END;
/
URL:
http://stads59.us.oracle.com:8080/orarep/plandiff/all?task_id=71&format=html&method=qbreg
TASK_71

SELECT message
FROM dba_advisor_findings
WHERE task_name = 'TASK_71';

MESSAGE
-----------------------------------------------------------------------------------------
A potentially better execution plan was found for this statement.
Plan for strategy plan comparison with plan directive (hint) IGNORE_OPTIM_EMBEDDED_HINTS has cost 4 with plan hash value 813480514
Q001 (Lvl: 1) P1: Qbc:SEL$1 (Org:PARSER Arg:NA) [FINAL] -*- P2: Qbc:SEL$1 (Org:PARSER Arg:NA) [FINAL] P1: P2:
=009 CR Diff'ing: Object base construct RWS_0 is found in target
=008 CR Diff'ing: Object base construct SYS.OBJ$ is found in target
=007 CR Diff'ing: Feature Index range scan [base final reason1: execution plan, target final reason2: execution plan]
=006 CR Diff'ing: Feature SQL EXECUTION [base final reason1: execution plan, target final reason2: execution plan]
=005 CR Diff'ing: Object base construct SEL$1 is found in target
=004 CR Diff'ing: Feature First rows (optimizer mode) [base final reason1: execution plan, target final reason2: execution plan]
=003 CR Diff'ing: Feature Index range scan [base final reason1: execution plan, target final reason2: execution plan]
=002 CR Diff'ing: Feature SQL EXECUTION [base final reason1: execution plan, target final reason2: execution plan]
=001 CR Diff'ing: Object base construct STATEMENT is found in target
Plan for strategy plan comparison with plan directive (hint) IGNORE_OPTIM_EMBEDDED_HINTS has cost 4 with plan hash value 813480514
Plan for strategy compilation history has cost 4 with plan hash value 813480514
At least one important bind value was missing for this sql statement. The accuracy of the advisor's analysis may depend on all important bind values being supplied.
 
DIFF_PLAN_OUTLINE
Compares two sql plan outlines dbms_xplan.diff_plan_outline(
sql_text  IN CLOB,
outline1  IN CLOB,
outline2  IN CLOB,
user_name IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD
 
DIFF_PLAN_SQL_BASELINE
Compares two sql plan baselines dbms_xplan.diff_plan_sql_baseline(
baseline_plan_name1 IN VARCHAR2,
baseline_plan_name2 IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
DISPLAY
Display the more recently created plan dbms_xplan.display(
table_name   IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format       IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL)
RETURN dbms_xplan_type_table PIPELINED;

Format choices are:
BASIC ..... displays minimum information
TYPICAL ... displays most relevant information
SERIAL .... like TYPICAL but without parallel information
ALL ....... displays all information

Follow the link to dbms_stats.gather_system_statistics for information on CPU costing.
EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

set pagesize 45
set linesize 121

SELECT * FROM TABLE(dbms_xplan.display);
Display a specific plan by name EXPLAIN PLAN
SET STATEMENT_ID = 'abc' FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

set pagesize 45
set linesize 121

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','BASIC'));

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','TYPICAL'));

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));
Using A View To Display The DBMS_XPLAN Output CREATE OR REPLACE VIEW plan_view AS
SELECT * FROM TABLE(dbms_xplan.display);

SELECT * FROM plan_view;
Predicate Display EXPLAIN PLAN FOR
SELECT a.program_id, b.line_number
FROM airplanes a, airplanes b
WHERE a.program_id = b.program_id
AND a.line_number = b.line_number;

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT a.program_id, b.line_number
FROM airplanes a, airplanes b
WHERE a.program_id = b.program_id
AND a.line_number = b.line_number
AND a.program_id = '777';

SELECT * FROM TABLE(dbms_xplan.display);
 
DISPLAY_AWR
Format and display the contents of the execution plan of a stored SQL statement in the AWR

Deprecated as of 18.0 instead use DISPLAY_WORKLOAD_REPOSITORY		 dbms_xplan.display_awr(
sql_id          IN VARCHAR2,
plan_hash_value IN INTEGER DEFAULT NULL,
db_id           IN INTEGER DEFAULT NULL,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
conn sys@pdbdev as sysdba

GRANT SELECT ON dba_hist_sql_plan TO uwclass;
GRANT SELECT ON dba_hist_sqltext TO uwclass;
GRANT SELECT ON v_$database TO uwclass;

conn uwclass/uwclass@pdbdev

desc dba_hist_sql_plan

SELECT MAX(io_cost)
FROM dba_hist_sql_plan;

SELECT sql_id
FROM dba_hist_sql_plan
WHERE io_cost = 142775;

SELECT * FROM TABLE(dbms_xplan.display_awr('24033vh7b098h'));

or

SELECT /* XPLAN_CURSOR */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT tf.*
FROM dba_hist_sqltext ht,
TABLE(dbms_xplan.display_awr(ht.sql_id,NULL,NULL, 'ALL')) tf
WHERE ht.sql_text LIKE '%XPLAN_CURSOR%';
 
DISPLAY_CURSOR
Display from GV$SQL_PLAN (or GV$SQL_PLAN_STATISTICS_ALL)

Formats and display the contents of the execution plan of any loaded cursor

Overload 1		 dbms_xplan.display_cursor(
sql_id          IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN INTEGER  DEFAULT 0,
format          IN VARCHAR2 DEFAULT 'TYPICAL',
shard_id        IN NUMBER)
RETURN dbms_xplan_type_table PIPELINED;
Format Constants
ADAPTIVE
  • Displays the final plan, or the current plan if the execution has not completed. This section includes notes about runtime optimizations that affect the plan, such as switching from a Nested Loops join to a Hash join.
  • Plan lineage. This section shows the plans that were run previously due to automatic reoptimization. It also shows the default plan, if the plan changed due to dynamic plans.
  • Recommended plan. In reporting mode, the plan is chosen based on execution statistics displayed. Note that displaying the recommended plan for automatic reoptimization requires re-compiling the query with the optimizer adjustments collected in the child cursor. Displaying the recommended plan for a dynamic plan does not require this.
  • Dynamic plans. This summarizes the portions of the plan that differ from the default plan chosen by the optimizer.
ADVANCED Similar to ‘All’, but also include the Outline information (the set of hints that will reproduce the plan) and the peeked bind variables used to optimize the query
ALIAS If relevant, shows the "Query Block Name / Object Alias" section
ALL Shows the Query block/Object Alias section, Predicate information, and Column Projections following the plan
ALLSTATS A shortcut for 'IOSTATS MEMSTATS'
BYTES If relevant, shows the number of bytes estimated by the optimizer
COST If relevant, shows optimizer cost information
IOSTATS Assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL), this format will show IO statistics for ALL (or only for the LAST as shown below) executions of the cursor
LAST By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last execution
MEMSTATS Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators
NOTE If relevant, shows the note section of the explain plan
OUTLINE Shows only Outline and Predicate information after the basic plan
PARALLEL If relevant, shows PX information (distribution method and table queue information)
PARTITION If relevant, shows partition pruning information
PREDICATE If relevant, shows the predicate section
PROJECTION If relevant, shows the projection section
REMOTE If relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)
ROWS If relevant, shows the number of rows estimated by the optimizer
RUNSTATS_LAST Same as IOSTATS LAST: displays the runtime stat for the last execution of the cursor
RUNSTATS_TOT Same as IOSTATS: displays IO statistics for all executions of the specified cursor
TBD
Overload 2 dbms_xplan.display_cursor(
sql_id          IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN INTEGER  DEFAULT 0,
format          IN VARCHAR2 DEFAULT 'TYPICAL',
shard_ids       IN num_tab_type)
RETURN dbms_xplan_type_table PIPELINED;
TBD
Overload 3

The original function is now Overload 3		 dbms_xplan.display_cursor(
sql_id          IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN INTEGER  DEFAULT 0,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
conn sys@pdbdev as sysdba

GRANT SELECT ON v_$sql_plan TO uwclass;
GRANT SELECT ON gv_$sql TO uwclass;
GRANT SELECT ON v_$session TO uwclass;

conn uwclass/uwclass@pdbdev

SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*)
FROM plan_table;

-- most recent cursor
SELECT * FROM TABLE(dbms_xplan.display_cursor);

-- named statement
SELECT /* XPLAN_CURSOR */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT sql_id, child_number
FROM gv$sql
WHERE sql_text LIKE '%XPLAN_CURSOR%';

SELECT * FROM TABLE(dbms_xplan.display_cursor('cpm9ss48qd32f'));

SELECT * FROM TABLE(dbms_xplan.display_cursor('cpm9ss48qd32f', 0));

SELECT * FROM TABLE(dbms_xplan.display_cursor('cpm9ss48qd32f', 0, 'RUNSTATS_TOT'));

SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>'RUNSTATS_TOT'));

SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>'IOSTATS'));

SELECT /*+ GATHER_PLAN_STATISTICS */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>'MEMSTATS'));

spool c:\temp\allstats.txt
set linesize 141
set trim on
set trimspool on

SELECT /*+ gather_plan_statistics */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST'));

spool off

or

SELECT t.*
FROM gv$sql s,
TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number)) t
WHERE sql_text LIKE '%XPLAN_CURSOR%';
 
DISPLAY_PLAN
Return the last plan, or a named plan, explained as a CLOB dbms_xplan.display_plan(
table_name   IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format       IN VARCHAR2 DEFAULT 'TYPICAL', -- see the format constants, above, under DISPLAY_CURSOR
filter_preds IN VARCHAR2 DEFAULT NULL,
type         IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
EXPLAIN PLAN
SET STATEMENT_ID = 'abc' FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

set pagesize 45
set linesize 121
set long 1000000
col xplan format a100

SELECT dbms_xplan.display_plan(statement_id => 'abc') AS XPLAN
FROM dual;
 
DISPLAY_SHARD_PLANS
Undocumented internal function to display plans from shards. It is called from prepare_records before and from main loop dbms_xplan.display_shard_plans(
format_flags    IN NUMBER,
diag_repos_cur  IN sys_refcursor DEFFAULT NULL,
sql_id          IN VARCHAR2      DEFAULT NULL,
cursor_child_no IN INTEGER       DEFAULT NULL,
shard_ids       IN num_tab_type  DEFAULT NULL)
RETURN dbms_xplan_type_table PIPELINED;
TBD
 
DISPLAY_SQLSET
Format and display the contents of the execution plan of statements stored in a SQL tuning set dbms_xplan.display_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
plan_hash_value IN INTEGER  DEFAULT NULL,
format          IN VARCHAR2 DEFAULT 'TYPICAL',
sqlset_owner    IN VARCHAR2 DEFAULT NULL)
RETURN dbms_xplan_type_table PIPELINED;
conn sys@pdbdev as sysdba

GRANT SELECT ON all_sqlset_statements TO uwclass;
GRANT SELECT ON all_sqlset_plans TO uwclass;

conn uwclass/uwclass@pdbdev

-- create a SQL tuning set

set linesize 121

SELECT hs.snap_id, TO_CHAR(hs.end_interval_time,'DD MON YYYY HH24:MI') SNAP_DAT
FROM dba_hist_snapshot hs, dba_hist_database_instance di
WHERE di.dbid = hs.dbid
AND di.instance_number = hs.instance_number
AND di.startup_time = hs.startup_time
ORDER BY snap_id;

DECLARE
 l_cursor dbms_sqltune.sqlset_cursor;
 x        VARCHAR2(30);
BEGIN
  -- create a sqlset
  dbms_sqltune.create_sqlset('UW Set', 'Test');

  -- load the sqlset
  OPEN l_cursor FOR
  SELECT VALUE(p)
  FROM TABLE(dbms_sqltune.select_workload_repository(
  15782,15792,NULL,NULL,NULL,NULL,NULL,NULL,10)) p;

  dbms_sqltune.load_sqlset(sqlset_name => 'UW Set',
  populate_cursor => l_cursor);

  -- create a tuning task from the sqlset
  x := dbms_sqltune.create_tuning_task(sqlset_name=>'UW Set');

  -- run the tuning task
  dbms_sqltune.execute_tuning_task(x);
END;
/

SELECT sql_id, plan_hash_value
FROM TABLE(dbms_sqltune.select_sqlset ('UW Set'));

desc all_sqlset_statements

SELECT sqlset_name, sqlset_owner, sqlset_id, sql_id, plan_hash_value
FROM all_sqlset_statements;

desc all_sqlset_plans

SELECT ssp.sqlset_name, ssp.sqlset_owner, ssp.sqlset_id, ssp.sql_id, ssp.plan_hash_value
FROM all_sqlset_plans ssp;

SELECT sql_id, plan_hash_value
FROM TABLE(dbms_sqltune.select_sqlset ('UW Set'));

/* display the execution plan for the SQL statement associated with SQL ID '6hwjmjgrpsuaa' and PLAN HASH 2721822575 in the SQL Tuning Set called 'OLTP_optimization_0405" */
SELECT *
FROM TABLE(dbms_xplan.display_sqlset('UW Set','6hwjmjgrpsuaa', 2721822575));

-- to display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set
SELECT *
FROM TABLE(dbms_xplan.display_sqlset('UW Set', 'dwssdqx28tzf5'));

-- to display runtime statistics for the SQL statement included in the preceding statement
SELECT * FROM TABLE(dbms_xplan.display_sqlset(
'UW Set', 'dwssdqx28tzf5', NULL, 'ALLSTATS LAST'));
 
DISPLAY_SQL_PATCH_PLAN
SQL Patch Display dbms_xplan.display_sql_patch_plan(
name   IN VARCHAR2,
format IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
SELECT * FROM TABLE(dbms_xplan.display_sql_patch_plan('UW_PPLAN'));
 
DISPLAY_SQL_PLAN_BASELINE
Displays one or more execution plans for the specified sql_handle of a SQL statement

To load plans from a SQL Tuning or the Cursor Cache use the DBMS_SPM package		 dbms_xplan.display_sql_plan_baseline(
sql_handle IN VARCHAR2 DEFAULT NULL,
plan_name  IN VARCHAR2 DEFAULT NULL,
format     IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
conn sys@pdbdev as sysdba

set linesize 121
col name format a40
col value format a30

SELECT p.name, p.value
FROM gv$parameter p
WHERE p.name LIKE 'optimizer_capture%';

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE SCOPE=BOTH;

SELECT p.name, p.value
FROM gv$parameter p
WHERE p.name LIKE 'optimizer_capture%';

GRANT select ON dba_sql_plan_baselines TO uwclass;

conn uwclass/uwclass@pdbdev

SELECT /* TEST */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

desc dba_sql_plan_baselines

SELECT spb.sql_handle
FROM dba_sql_plan_baselines spb
WHERE spb.sql_text LIKE '%TEST%';

SELECT * FROM TABLE (dbms_xplan.display_sql_plan_baseline('SYS_SQL_71e1abffb11f9833'));

or

SELECT t.*
FROM (
  SELECT DISTINCT spb.sql_handle
  FROM dba_sql_plan_baselines spb
  WHERE spb.sql_text like '%HR2%') pb,
  TABLE(dbms_xplan.display_sql_plan_baseline(pb.sql_handle, NULL, 'basic')) t;
 
DISPLAY_SQL_PROFILE_PLAN
Display a SQL Profile dbms_xplan.display_sql_profile_plan(
name   IN VARCHAR2,
format IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
SELECT * FROM TABLE(dbms_xplan.display_sql_profile_plan('UW_PROFILE'));
 
DISPLAY_WORKLOAD_REPOSITORY
Display from AWR dbms_xplan.display_workload_repository(
sql_id          IN VARCHAR2,
plan_hash_value IN INTEGER  DFAULT NULL,
format          IN VARCHAR2 DEFAULT 'TYPICAL',
dbid            IN INTEGER  DEFAULT NULL,
con_dbid        IN INTEGER  DEFAULT NULL,
awr_location    IN VARCHAR2 DEFAULT 'AWR_ROOT')
RETURN dbms_xplan_type_table PIPELINED;
SELECT last_active_time, sql_id
FROM v$sql
WHERE last_active_time > SYSDATE-1/1440
ORDER BY 1;

LAST_ACTIVE_TIME SQL_ID
-------------------- -------------
26-JUN-2018 15:31:17 655vmvf20n9mw
26-JUN-2018 15:31:33 7am4w4pp3nwtm
26-JUN-2018 15:31:33 gjaap3w3qbf8c
26-JUN-2018 15:31:33 7am4w4pp3nwtm
26-JUN-2018 15:31:33 gjaap3w3qbf8c
26-JUN-2018 15:31:33 cgtc5gb7c4g07
26-JUN-2018 15:31:33 c9umxngkc3byq
26-JUN-2018 15:31:42 2t8b3gh7z1v0k
26-JUN-2018 15:31:42 dkubj0p62bd2w
26-JUN-2018 15:31:42 2t8b3gh7z1v0k
26-JUN-2018 15:31:42 3u5ma38bd03qq
26-JUN-2018 15:31:42 dkubj0p62bd2w
26-JUN-2018 15:31:42 bs1vs6u58nayu
26-JUN-2018 15:31:42 bs1vs6u58nayu
26-JUN-2018 15:31:42 3u5ma38bd03qq
26-JUN-2018 15:31:46 6jh4ua3hhf9us
26-JUN-2018 15:31:46 9babjv8yq8ru3
26-JUN-2018 15:31:51 8p447s6p0rv6b
26-JUN-2018 15:31:51 772s25v1y0x8k
26-JUN-2018 15:31:51 aykvshm7zsabd
26-JUN-2018 15:31:51 5yv7yvjgjxugg
26-JUN-2018 15:31:58 bsa0wjtftg3uw
26-JUN-2018 15:32:00 87gaftwrm2h68
26-JUN-2018 15:32:00 089d5jrtq3skb

24 rows selected.

SELECT * FROM TABLE(dbms_xplan.display_workload_repository('87gaftwrm2h68'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID 87gaftwrm2h68
--------------------
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname
from obj$ o where o.obj#=:1

Plan hash value: 1072382624
---------------------------------------------------------------------------------------
| Id | Operation                           | Name  | Rows| Bytes| Cost(%CPU)| Time    |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |       |     |      |    3 (100)|         |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$  |   1 |  111 |    3   (0)| 00:00:01|
|  2 |   INDEX RANGE SCAN                  | I_OBJ1|   1 |      |    2   (0)| 00:00:01|
---------------------------------------------------------------------------------------
 
FORMAT_NUMBER
Returns a number as a string dbms_xplan.format_number(num IN NUMBER) RETURN VARCHAR2;
SELECT dump(100.1), dbms_xplan.format_number(100.1), dump(dbms_xplan.format_number(100.1))
FROM dual;
 
FORMAT_NUMBER2
Returns a number as a string formatted with a leading space (CHR(32) dbms_xplan.format_number2(num IN NUMBER) RETURN VARCHAR2;
SELECT dump(100.1), dbms_xplan.format_number2(100.1), dump(dbms_xplan.format_number2(100.1))
FROM dual;
 
FORMAT_SIZE
Undocumented dbms_xplan.format_size(num IN NUMBER) RETURN VARCHAR2;
col fsize format a15
col fsizedump format a30

SELECT dump(100.1), dbms_xplan.format_size(100.1) FSIZE, dump(dbms_xplan.format_size(100.1)) FSIZEDUMP
FROM dual;
 
FORMAT_SIZE2
Undocumented dbms_xplan.format_size2(num IN NUMBER) RETURN VARCHAR2;
col fsize format a15
col fsizedump format a30

SELECT dump(100.1), dbms_xplan.format_size2(100.1) FSIZE, dump(dbms_xplan.format_size2(100.1)) FSIZEDUMP
FROM dual;
 
FORMAT_TIME_S
Formats a number representing time in seconds using the format HH:MM:SS dbms_xplan.format_time_s(num IN NUMBER) RETURN VARCHAR2;
col ftime format a15
col ftimedump format a40

SELECT dump(100.1), dbms_xplan.format_time_s(100.1) FTIME, dump(dbms_xplan.format_time_s(100.1)) FTIMEDUMP
FROM dual;
 
GET_CURSOR_ROWS
Intentionally not documented by Oracle dbms_xplan.get_cursor_rows(
sql_id          IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN INTEGER  DEFAULT 0,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN plan_table PIPELINED;
SELECT DISTINCT a.sql_id
FROM dba_hist_sql_plan a, dba_hist_sql_plan b
WHERE a.sql_id = b.sql_id
AND a.plan_hash_value <> b.plan_hash_value;

SELECT * FROM TABLE(dbms_xplan.get_cursor_rows('bwsxc8jzcm635'));
 
GET_FINAL_PLAN
Intentionally undocumented function ... I have a suspicion this is intended to return the final plan after Adaptive Query Planning. Testing will determine whether that is true. dbms_xplan.get_final_plan(
plan_rows IN plan_table,
format    IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN plan_table PIPELINED;
TBD
 
GET_PLANDIFF_REPORT_XML
Undocumented dbms_xplan.get_plandiff_report_xml(
report_ref IN VARCHAR2 := NULL,  -- report name
tid        IN NUMBER,            -- task id
method     IN VARCHAR2)          -- comparison method (for example 'outline')
RETURN XMLTYPE;
TBD
 
GET_PLAN_ROWS
Intentionally not documented by Oracle dbms_xplan.get_plan_rows(
table_name   IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
plan_id      IN VARCHAR2 DEFAULT NULL,
format       IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL,
mask_cost    IN NUMBER   DEFAULT 0)
RETURN plan_table PIPELINED;
TBD
 
I_DISPLAY_CURSOR
Undocumented internal function to display cursor interfaces dbms_xplan.i_display_cursor(
sql_id          IN VARCHAR2     DEFAULT NULL,
cursor_child_no IN INTEGER      DEFAULT 0,
format          IN VARCHAR2     DEFAULT 'TYPICAL',
shard_ids       IN num_tab_type DEFAULT NULL)
RETURN dbms_xplan_type_table PIPELINED;
SELECT * FROM TABLE(dbms_xplan.i_display_cursor('94qn6y14kw01g'));

PLAN_TABLE_OUTPUT
------------------
SQL_ID 94qn6y14kw01g, child number 0
-------------------------------------
SELECT NVL(TO_NUMBER(EXTRACT(XMLTYPE(:B2 ), :B1 )), 0) FROM DUAL

Plan hash value: 1388734953

---------------------------------------------------------------
| Id | Operation        | Name | Rows | Cost (%CPU)| Time     |
---------------------------------------------------------------
|  0 | SELECT STATEMENT |      |      |     2 (100)|          |
|  1 |  FAST DUAL       |      |    1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------
 
I_DISPLAY_SMB_PLAN
Undocumented dbms_xplan.i_display_smb_plan(
name     IN VARCHAR2,
format   IN VARCHAR2 DEFAULT 'TYPICAL',
obj_type IN NUMBER)
RETURN dbms_xplan_type_table PIPELINED;
SELECT * FROM TABLE(dbms_xplan.i_display_smb_plan('UW_SMB', 'ALL', 1));
 
PREPARE_PLAN_XML_QUERY
Helper function that builds the XML version of the text of a select query that is run before the display display function to retrieve and display the execution plan of a SQL dbms_xplan.prepare_plan_xml_query(plan_query IN VARCHAR2) RETURN VARCHAR2;
conn uwclass/uwclass@pdbdev

set linesize 1024
set serveroutput on

DECLARE
 sqlst VARCHAR2(1024) := 'SELECT srvr_id FROM servers
                          INTERSECT
                          SELECT srvr_id FROM serv_inst';
 retval VARCHAR2(4000);
BEGIN
  retval := dbms_xplan.prepare_plan_xml_query(sqlst);
  dbms_output.put_line(retval);
END;
/
 
PREPARE_RECORDS
Private procedure: used internally dbms_xplan.prepare_records(
plan_cur        IN sys_refcursor,
i_format_flags  IN NUMBER,
diag_repos_cur  IN sys_refcursor DEFAULT NULL,
sql_id          IN VARCHAR2      DEFAULT NULL,
cursor_child_no IN INTEGER       DEFAULT NULL,
shard_ids       IN num_tab_type  DEFAULT NULL)
RETURN dbms_xplan_type_table PIPELINED;
TBD
 
VALIDATE_FORMAT
Private function to validate the user format: used internally dbms_xplan.validate_format(
hasPlanStats IN  BOOLEAN,
format       IN  VARCHAR2,
format_flags OUT BINARY_INTEGER)
RETURN BOOLEAN;
DECLARE
 b  BOOLEAN;
 bi BINARY_INTEGER;
BEGIN
  IF dbms_xplan.validate_format(TRUE, 'ALL', bi) THEN
    dbms_output.put_line('T: ' || bi);
  ELSE
    dbms_output.put_line('F: ' || bi);
  END IF;
END;
/

Related Topics
Autotrace
Built-in Functions
Built-in Packages
Explain Plan
DBMS_HPROF
DBMS_PROFILER
DBMS_SPM
DBMS_SPM_INTERNAL
DBMS_SQLPA
DBMS_SQLTUNE
AWR
DBMS_XPLAN_INTERNAL
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx