Oracle DBMS_XPLAN
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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;

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;
Dependencies
DBA_ADVISOR_FINDINGS DBMS_SQLTCB_INTERNAL PRVT_SQLADV_INFRA
DBA_ADVISOR_SQLPLANS DBMS_SQLTUNE PRVT_SQLPA
DBA_ADVISOR_TASKS DBMS_SQLTUNE_INTERNAL SQLPROF_ATTR
DBMS_ADVISOR DBMS_SQLTUNE_UTIL0 SQLSET_ROW
DBMS_ASSERT DBMS_SWRF_REPORT_INTERNAL SQL_BINDS
DBMS_COMPARISON DBMS_SYSTEM SQL_PLAN_TABLE_TYPE
DBMS_LOB DBMS_UTILITY V$DATABASE
DBMS_OUTPUT DBMS_XPLAN_LIB WRI$_REPT_PLAN_DIFF
DBMS_REPORT DBMS_XPLAN_TYPE WRI$_REPT_XPLAN
DBMS_SMB_INTERNAL DBMS_XPLAN_TYPE_TABLE XMLSEQUENCE
DBMS_SPM_INTERNAL DUAL XMLSEQUENCETYPE
DBMS_SQL PLITBLM XMLTYPE
DBMS_SQLDIAG PRVT_REPORT_TAGS  
Documented Yes
First Available 9.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsxpln.sql
Subprograms
BUILD_PLAN_XML DISPLAY_PLAN FORMAT_TIME_S
DIFF_PLAN DISPLAY_SQLSET GET_CURSOR_ROWS
DIFF_PLAN_AWR DISPLAY_SQL_PATCH_PLAN GET_PLANDIFF_REPORT_XML
DIFF_PLAN_CURSOR DISPLAY_SQL_PLAN_BASELINE GET_PLAN_ROWS
DIFF_PLAN_OUTLINE DISPLAY_SQL_PROFILE_PLAN 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
DISPLAY_CURSOR FORMAT_SIZE2  
 
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;
 
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;
SQL> SELECT DISTINCT a.sql_id, a.plan_hash_value, b.plan_hash_value
   2 FROM dba_hist_sql_plan a, dba_hist_sql_plan b
   3 WHERE a.sql_id = b.sql_id
   4 AND a.plan_hash_value <> b.plan_hash_value
   5 AND a.timestamp > sysdate-1
   6 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

SQL> SELECT dbms_xplan.diff_plan_awr('12a2xbmwn5v6z', 3607810482, 518152518)
   2 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

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

SQL> SELECT message
  2  FROM dba_advisor_findings
  3  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 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		 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;

Format Constants
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
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',
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_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 (new 12.1)
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 (new 12.1)
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'));
 
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 (new 12.1)
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_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 (new 12.1)
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_SMB_PLAN (new 12.1)
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 BINARY_INTEGER)
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
AWR
Explain Plan
DBMS_HPROF
DBMS_PROFILER
DBMS_SPM
DBMS_SPM_INTERNAL
DBMS_SQLPA
DBMS_SQLTUNE
Packages

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-2014 Daniel A. Morgan All Rights Reserved