Morgan's Library Oracle DBMS_XPLAN Explain Plan Examples Sample Code

Oracle DBMS_XPLAN
Version 11.2.0.2

General Information

Source

{ORACLE_HOME}/rdbms/admin/dbmsxpln.sql

First Availability

9.2

Constants

Name	Data Type	Value
UNKNOWN_DIFF_CLASS	NUMBER	POWER(2,31)

Dependencies

SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_XPLAN'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_XPLAN';

Security Model

Execute is granted to PUBLIC

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 25
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 (11.2.0.1)

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 (11.2.0.1)

Compares two sql plans specified by the given plan hash ids

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
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;

SQL_ID
-------------
3g7sxtj9d6zd3
9577p5ph7vm2c
1wdhux6669jk1
3wrrjm9qtr2my
424h0nf7bhqzd
bck4jg4996s2x
g8bbyzvyugjav
52jvfpxsyuvzf
7yjzq240hcd2c
62b22htb4fkmg
8y9zk3zqvncn8
1b28hzmjun5t0
fcb5u7n0r523m
0fr8zhn4ymu3v
ga9j9xk5cy9s0
cvn54b7yz0s8u
gjm43un5cy843
g5m0bnvyy37b1
12a2xbmwn5v6z
4njc9kmjb3qxs
1gu8t96d0bdmu
gx4mv66pvj3xz
43w0r9122v7jm
46zqay3hb3n7d
c6awqs517jpj0
czvfg1255s5zg
afcswub17n34t
dp2c6pq28u5jr
7ng34ruy5awxq
fz0ur7zcbqhzu
53saa2zkr6wc3
bsa0wjtftg3uw
397tpc55tsp6u
39m4sx9k63ba2
848dyu9288c3h
ab3swhv5g138y
1xpzt42f827zx
d2f0cu41f7guc
f3g84j69n0tjh

SELECT DISTINCT plan_hash_value
FROM v$sql_plan
WHERE sql_id = 'f3g84j69n0tjh';

PLAN_HASH_VALUE
---------------
2335623859
914792125

set serveroutput on

SQL> SELECT dbms_xplan.diff_plan_awr('f3g84j69n0tjh', 2335623859, 914792125)
2 FROM dual;
SELECT dbms_xplan.diff_plan_awr('f3g84j69n0tjh', 2335623859, 914792125)
*
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 4862
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 7625
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 40
ORA-06512: at "SYS.DBMS_XPLAN", line 6798
ORA-06512: at "SYS.DBMS_XPLAN", line 6984

DIFF_PLAN_CURSOR (11.2.0.1)

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 / as sysdba

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

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

set serveroutput on

DECLARE
x VARCHAR2(13) := 'bhvyz9bgyrhb2';
y NUMBER := 1;
z NUMBER := 2;
r VARCHAR2(4000);
BEGIN
r := dbms_xplan.diff_plan_cursor(x, y, z);
dbms_output.put_line(r);
END;
/

DIFF_PLAN_OUTLINE (11.2.0.1)

Compares two sql plans generated by the given 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 (11.2.0.1)

Compares two given sql plans (specified via plan_names)

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 25
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 25
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 / 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

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 / 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

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 25
set linesize 121
set long 1000000
col xplan format a100

SELECT dbms_xplan.display_plan(statement_id => 'abc') AS XPLAN
FROM dual;

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 / as sysdba

set linesize 121
col name format a40
col value format a30

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

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE SCOPE=BOTH;

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

GRANT select ON dba_sql_plan_baselines TO uwclass;

conn uwclass/uwclass

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 sql_handle
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%TEST%';

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

or

SELECT t.*
FROM (
SELECT DISTINCT sql_handle
FROM dba_sql_plan_baselines
WHERE sql_text like '%HR2%') pb,
TABLE(dbms_xplan.display_sql_plan_baseline(pb.sql_handle, NULL, 'basic')) t;

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 / as sysdba

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

conn uwclass/uwclass

-- create a SQL tuning set

set linesize 121

SELECT s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.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 Set');

-- 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 sqlset_name, sqlset_owner, sqlset_id, sql_id, plan_hash_value
FROM all_sqlset_plans;

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'));

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;

SELECT dump(100.1), dbms_xplan.format_size(100.1), dump(dbms_xplan.format_size(100.1))
FROM dual;

FORMAT_SIZE2

Undocumented

dbms_xplan.format_size2(num IN NUMBER) RETURN VARCHAR2;

SELECT dump(100.1), dbms_xplan.format_size2(100.1), dump(dbms_xplan.format_size2(100.1))
FROM dual;

FORMAT_TIME_S

Undocumented

dbms_xplan.format_time_s(num IN NUMBER) RETURN VARCHAR2;

SELECT dump(100.1), dbms_xplan.format_time_s(100.1), dump(dbms_xplan.format_time_s(100.1))
FROM dual;

GET_PLANDIFF_REPORT_XML (11.2.0.1)

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

PREPARE_PLAN_XML_QUERY

This is a 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;

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;

TBD

Related Topics
AWR
Explain Plan
DBMS_HPROF
DBMS_PROFILER
DBMS_SPM
DBMS_SQLPA
DBMS_SQLTUNE

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated: