fOracle DBMS_XPLAN
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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;

TYPE num_tab_type IS TABLE OF NUMBER;
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_XPLAN_LIB V$DATABASE
DBMS_OUTPUT DBMS_XPLAN_TYPE WRI$_REPT_PLAN_DIFF
DBMS_REPORT DBMS_XPLAN_TYPE_TABLE WRI$_REPT_XPLAN
DBMS_SMB_INTERNAL DUAL XMLSEQUENCE
DBMS_SPM_INTERNAL PLITBLM XMLSEQUENCETYPE
DBMS_SQL PRVT_REPORT_TAGS XMLTYPE
DBMS_SQLDIAG    
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_SQLSET FORMAT_TIME_S
DIFF_PLAN DISPLAY_SQL_PATCH_PLAN GET_CURSOR_ROWS
DIFF_PLAN_AWR DISPLAY_SQL_PLAN_BASELINE GET_PLANDIFF_REPORT_XML
DIFF_PLAN_CURSOR DISPLAY_SQL_PROFILE_PLAN GET_PLAN_ROWS
DIFF_PLAN_OUTLINE DISPLAY_WORKLOAD_REPOSITORY I_DISPLAY_CURSOR
DIFF_PLAN_SQL_BASELINE FORMAT_NUMBER I_DISPLAY_SMB_PLAN
DISPLAY FORMAT_NUMBER2 PREPARE_PLAN_XML_QUERY
DISPLAY_AWR FORMAT_SIZE PREPARE_RECORDS
DISPLAY_CURSOR FORMAT_SIZE2 VALIDATE_FORMAT
DISPLAY_PLAN    
 
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);
nbsp; 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

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 (2 new 18.1 overloads and new parameters)
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_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 (new 18.1)
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;
 1 SELECT last_active_time, sql_id
 2 FROM v$sql
 3 WHERE last_active_time > SYSDATE-1/1440
 4* 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.

SQL> 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_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 (new 18.1)
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;
SQL> 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 (4 new parameters 18.1)
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
AWR
Built-in Functions
Built-in Packages
Explain Plan
DBMS_HPROF
DBMS_PROFILER
DBMS_SPM
DBMS_SPM_INTERNAL
DBMS_SQLPA
DBMS_SQLTUNE
What's New In 12cR2
What's New In 18cR3

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