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
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;
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> 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
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.
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';
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 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%';
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;
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;
-- 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'));
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;
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;
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 |
---------------------------------------------------------------------------------------------
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'));
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;
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;
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 |
---------------------------------------------------------------
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;
/
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;
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;
/