DBMS_XPLAN package

:The DBMS_XPLAN package supplies five table functions

DISPLAY - to format and display the contents of a plan table.

DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.

DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.

DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle

DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.

----------------- SAMPLES. -------------


Using a View to Display Last Explain Plan

EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename='benoit';

Display the plan using the DBMS_XPLAN.DISPLAY table function

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);



Display the execution plan for the cursor:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

Display the execution plan of all cursors matching the string 'TOTO':

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';

------------------------

DISPLAY Function

This table function displays the contents of the plan table.

Syntax

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

To display the result of the last EXPLAIN PLAN command stored in the plan table:

SELECT * FROM table (DBMS_XPLAN.DISPLAY);
To display from other than the default plan table, "my_plan_table":

SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));
To display the minimum plan information:

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));


------------------------------------------

DISPLAY_AWR Function

This table function displays the contents of an execution plan stored in the AWR.

Syntax

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));

-----------------------------------------------------------------------------

DISPLAY_CURSOR Function

This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).

Syntax

DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');


child_number
Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.



To display the execution plan of the last SQL statement executed by the current session:

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR);
To display the execution plan of all children associated with the SQL ID 'atfwcg8anrykp':

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));
with children 1

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp',1));

--------------------------------------------------

DISPLAY_SQL_PLAN_BASELINE Function

This table function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.

Syntax

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TYPICAL')
RETURN dbms_xplan_type_table;

plan_name
Plan name. It identifies a specific plan. Default NULL means all plans associated with identified SQL statement are explained and displayed.

Display all plans of a SQL statement identified by the SQL handle 'SYS_SQL_b1d49f6074ab95af' using TYPICAL format

SET LINESIZE 150
SET PAGESIZE 2000
SELECT t.*
FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
'SYS_SQL_b1d49f6074ab95af')) t;

-----------------------------------------------------

DISPLAY_SQLSET Function


DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
format IN VARCHAR2 := 'TYPICAL',
sqlset_owner IN VARCHAR2 := NULL)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

This table function displays the execution plan of a given statement stored in a SQL tuning set.

plan_hash_value
Optional parameter. Identifies a specific stored execution plan for a SQL statement. If suppressed, all stored execution plans are shown.

To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set:

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'OLTP_optimization_0405','gwp663cqh5qbf'));

To display the execution plan for the SQL statement associated with SQL ID 'gwp663cqh5qbf' and PLAN HASH 3693697075 in the SQL Tuning Set called 'OLTP_optimization_0405":

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'OLTP_optimization_0405','gwp663cqh5qbf', 3693697075));