Usage Notes

By default, only relevant information is reported by the display table function. In "Displaying a Plan Table Using DBMS_XPLAN.DISPLAY: Example", 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.

Displaying a Plan Table with Parallel Information: Example

Rem
Rem Execute an explain plan command for a parallel query
Rem
ALTER TABLE emp PARALLEL;
EXPLAIN PLAN for
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename    ='benoit'
   ORDER BY e.empno;

Rem
Rem Display the plan using the dbms_xplan.display() table function
Rem
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

The above EXPLAIN PLAN produces output as follows:

--------------------------------------------------------------------------------
| Id  | Operation        |Name | Rows | Bytes | Cost | TQ   |IN-OUT|PQ Distrib |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |     |    1 |    50 |    3 |67,60 |      |           |
|   1 | SORT ORDER BY    |     |    1 |    50 |    3 |67,61 | P->S | QC(ORDER) |
|   2 | MERGE JOIN       |     |    1 |    50 |    3 |67,62 | P->P | RANGE     |
|   3 | SORT JOIN        |     |    4 |    72 |    3 |67,63 | PCWP |           |
|   4 | TABLE ACCESS FULL|DEPT |    4 |    72 |    2 |67,64 | S->P | BROADCAST |
|*  5 | SORT JOIN        |     |    1 |    32 |    2 |67,65 | PCWP |           |
|*  6 | TABLE ACCESS FULL|EMP  |    1 |    32 |    2 |67,66 | PCWP |           |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id)
   5 - access("E1"."DEPTNO"="D1"."DEPTNO")
       filter("E1"."DEPTNO"="D1"."DEPTNO")
   6 - filter("E1"."ENAME"'benoit')

When the query is parallel, information related to parallelism is reported: table queue number (TQ column), table queue type (IN-OUT) 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 (for example, by using the TRUNCATE TABLE command) to ensure good performance in the execution of the DISPLAY table function.

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, as shown below:

Using a View to Display Output: Example

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