PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

SOracle DBMS_XPLAN
Version 11.1
 
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
DBMS_ASSERT DBMS_XPLAN_LIB PLAN_VIEW
DBMS_SQL DBMS_XPLAN_TYPE PLITBLM
DBMS_SQLTUNE_INTERNAL DBMS_XPLAN_TYPE_TABLE V$DATABASE
DBMS_STATS_LIB EXTRACT XMLSEQUENCE
DBMS_SWRF_REPORT_INTERNAL EXTRACTVALUE XMLTYPE
 
BUILD_PLAN_XML (new 11.1.0.6)

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

Display the last plan explained		 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
ALIAS If relevant, shows the "Query Block Name / Object Alias" section
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
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 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: emp\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 (new 11.1.0.6)

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 (new 11.1.0.6)

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 (new 11.1.0.6)

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 (new 11.1.0.6)

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 (new 11.1.0.6)

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 (new 11.1.0.6)

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 (new 11.1.0.6)

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;
 
PREPARE_PLAN_XML_QUERY (new 11.1.0.7)

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) -- query to fetch plan table
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_SPM
DBMS_SQLTUNE
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----