| 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 |
GV$DATABASE |
| DBMS_SQL |
DBMS_XPLAN_TYPE |
PLAN_VIEW |
| DBMS_SQLTUNE_INTERNAL |
DBMS_XPLAN_TYPE_TABLE |
PLITBLM |
| DBMS_STATS_LIB |
EXTRACT |
XMLSEQUENCE |
| DBMS_SWRF_REPORT_INTERNAL |
EXTRACTVALUE |
XMLTYPE |
|
| |
| BUILD_PLAN_XML
(new 11g) |
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 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; |
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', 0));
SELECT * FROM TABLE(dbms_xplan.display_cursor('dpcugg8dz3y5k'));
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
11g) |
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 11g) |
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 11g) |
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 11g) |
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
11g) |
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
11g) |
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 11g) |
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_RECORDS |
| Private procedure: used internally |
dbms_xplan.repare_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 |