dbmsxplan oracle_[转]Oracle DBMS_XPLAN

Using DBMS_XPLAN

Overview

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.

Security Model

This package runs with the privileges of the calling user, not

the package owner (SYS). The table function

DISPLAY_CURSOR requires to have select privileges on

the following fixed views: V$SQL_PLAN,

V$SESSION and

V$SQL_PLAN_STATISTICS_ALL.

Using the DISPLAY_AWR Function requires the user to have

SELECT privileges on DBA_HIST_SQL_PLAN,

DBA_HIST_SQLTEXT, and V$DATABASE.

Using the DISPLAY_SQLSET Functionrequires the user to have the

SELECT privilege on ALL_SQLSET_STATEMENTS

and ALL_SQLSET_PLANS.

Using DISPLAY_SQL_PLAN_BASELINE Function the user requires the user

to have the SELECT privilege on

DBA_SQL_PLAN_BASELINES.

All these privileges are automatically granted as part of the

SELECT_CATALOG role.

Examples

Displaying a Plan Table Using DBMS_XPLAN.DISPLAY

Execute an explain plan command on a SELECT

statement:

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

This query produces the following output:

Plan hash value: 3693697075

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 57 | 6 (34)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 57 | 6 (34)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (34)| 00:00:01 |

| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (34)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("E"."DEPTNO"="D"."DEPTNO")

2 - filter("E"."ENAME"='benoit')

15 rows selected.

Displaying a Cursor Execution Plan Using

DBMS_XPLAN.DISPLAY_CURSOR

By default, the table function DISPLAY_CURSOR

formats the execution plan for the last SQL statement executed by

the session. For example:

SELECT ename FROM emp e, dept d

WHERE e.deptno = d.deptno

AND e.empno=7369;

ENAME

----------

SMITH

To display the execution plan of the last executed statement for

that session:

SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

This query produces the following output:

Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0

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

SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno

AND e.empno=7369

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | | | |

|* 1 | HASH JOIN | | 1 | 16 | 6 (34)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| EMP | 1 | 13 | 3 (34)| 00:00:01 |

| 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (34)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("E"."DEPTNO"="D"."DEPTNO")

2 - filter("E"."EMPNO"=7369)

21 rows selected.

You can also use the table function DISPLAY_CURSOR

to display the execution plan for any loaded cursor stored in the

cursor cache. In that case, you must supply a reference to the

child cursor to the table function. This includes the SQL ID of the

statement and optionally the child number.

Run a query with a distinctive comment:

SELECT ename, dname

FROM dept d join emp e USING (deptno);

Get sql_id and child_number for the

preceding statement:

SELECT sql_id, child_number

FROM v$sql

WHERE sql_text LIKE '%TOTO%';

SQL_ID CHILD_NUMBER

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

gwp663cqh5qbf 0

Display the execution plan for the cursor:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0

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

SELECT ename, dname

FROM dept d JOIN emp e USING (deptno);

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | | 7 (100)| |

| 1 | SORT GROUP BY | | 4 | 64 | 7 (43)| 00:00:01 |

|* 2 | HASH JOIN | | 14 | 224 | 6 (34)| 00:00:01 |

| 3 | TABLE ACCESS FULL| DEPT | 4 | 44 | 3 (34)| 00:00:01 |

| 4 | TABLE ACCESS FULL| EMP | 14 | 70 | 3 (34)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("E"."DEPTNO"="D"."DEPTNO")

Instead of issuing two queries, one to the get the sql_id and

child_number pair and one to display the plan, you can combine

these in a single query:

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

Displaying a Plan Table with Parallel

Information

By default, only relevant information is reported by the display

and display_cursor table functions. In Displaying a Plan Table Using DBMS_XPLAN.DISPLAY,

the query does not execute in parallel. Hence, information related

to the parallelization of the plan is not reported. As shown in the

following example, parallel information is reported only if the

query executes in parallel.

ALTER TABLE emp PARALLEL;

EXPLAIN PLAN for

SELECT * FROM emp e, dept d

WHERE e.deptno = d.deptno

AND e.ename ='hermann'

ORDER BY e.empno;

Display the plan using the DBMS_XPLAN.DISPLAY

table function

SET LINESIZE 130

SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Plan hash value: 3693697345

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |INOUT |PQ Distrib |

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

| 0 | SELECT STATEMENT | | 1 | 117 | 6 (50) | 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (ORDER) |:TQ10003 | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | P->S | QC (ORDER) |

| 3 | SORT ORDER BY | | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | PCWP | |

| 4 | PX RECEIVE | | 1 | 117 | 5 (40) | 00:00:01 | Q1,03 | PCWP | |

| 5 | PX SEND RANGE |:TQ10002 | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | P->P | RANGE |

|* 6 | HASH JOIN | | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | PCWP | |

| 7 | PX RECEIVE | | 1 | 87 | 2 (50) | 00:00:01 | Q1,02 | PCWP | |

| 8 | PX SEND HASH |:TQ10001 | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | P->P | HASH |

| 9 | PX BLOCK ITERATOR | | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWC | |

|* 10| TABLE ACCESS FULL | EMP | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWP | |

| 11 | BUFFER SORT | | | | | | Q1,02 | PCWC | |

| 12 | PX RECEIVE | | 4 | 120 | 3 (34) | 00:00:01 | Q1,02 | PCWP | |

| 13 | PX SEND HASH |:TQ10000 | 4 | 120 | 3 (34) | 00:00:01 | | S->P | HASH |

| 14 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (34) | 00:00:01 | | | |

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

Predicate Information (identified by operation id):

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

6 - access("E"."DEPTNO"="D"."DEPTNO")

10 - filter("E"."ENAME"='hermann')

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

When the query is parallel, information related to parallelism

is reported: table queue number (TQ column), table

queue type (INOUT) and table queue distribution method

(PQ Distrib).

By default, if several plans in the plan table match the

statement_id parameter passed to the display table

function (default value is NULL), only the plan

corresponding to the last EXPLAIN PLAN

command is displayed. Hence, there is no need to purge the plan

table after each EXPLAIN PLAN. However,

you should purge the plan table regularly to ensure good

performance in the execution of the DISPLAY table

function. If no plan table is created, Oracle will use a global

temporary table to store any plan information for individual users

and will preserve its content throughout the lifespan of a session.

Note that you cannot truncate the content of a global temporary

table.

For ease of use, you can define a view on top of the display

table function and then use that view to display the output of the

EXPLAIN PLAN command:

Using a View to Display Last Explain Plan

# define plan view

CREATE VIEW PLAN AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

# display the output of the last explain plan command

SELECT * FROM PLAN;