8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

DBMS_XPLAN : Display Execution Plans

The DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the "utlxpls.sql" script or custom queries of the plan table. Subsequent database versions have increased the functionality of the package.

Related articles.

Setup

If it is not already present create the SCOTT schema.

conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql

Create a PLAN_TABLE if it does not already exist.

conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;

DISPLAY Function

The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain a SQL statement.

CONN scott/tiger

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

Next we use the DBMS_XPLAN.DISPLAY function to display the execution plan.

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

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    58 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.

SQL>

The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:

From Oracle 10g Release 2 onwards the format of the output can be tailored by using the standard list of formats along with keywords that represent columns to including or excluding (prefixed with '-'). As a result, the format column can now be a space or comma delimited list. The list of available columns varies depending on the database version and function being called. Check the documentation for your version.

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));

Plan hash value: 3625962092

------------------------------------------------
| Id  | Operation                    | Name    |
------------------------------------------------
|   0 | SELECT STATEMENT             |         |
|   1 |  NESTED LOOPS                |         |
|   2 |   NESTED LOOPS               |         |
|   3 |    TABLE ACCESS FULL         | EMP     |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |
------------------------------------------------

12 rows selected.

SQL>

DISPLAY_CURSOR Function

In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views, so the user must have access to these. It accepts three optional parameters:

The following example show the advanced output from a query on the SCOTT schema.

CONN / AS SYSDBA
GRANT SELECT ON v_$session TO scott;
GRANT SELECT ON v_$sql TO scott;
GRANT SELECT ON v_$sql_plan TO scott;
GRANT SELECT ON v_$sql_plan_statistics_all TO scott;

CONN scott/tiger

SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  gu62pbk51ubc3, child number 0
-------------------------------------
SELECT * FROM   emp e, dept d WHERE  e.deptno = d.deptno AND    e.ename
 = 'SMITH'

Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |     4 (100)|          |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],
       "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
   2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D".ROWID[ROWID,10],
       "D"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
       "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
       "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
   4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
   5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]

67 rows selected.

SQL>

Other Functions

There are some other useful functions in the package, but I don't find myself using them very often, so they are summarized below. If you need more information, follow the links at the bottom of the article for the appropriate database version.

Reading Execution Plans

Execution plans can look very confusing, but reading them is reasonably simple provided you follow three simple rules:

  1. The first operation, or starting point, is the first leaf node, when reading from the top to the bottom. That is, the first element without an indented entry below it. You read from that point backwards.
  2. Join operations always require two sets. The order you read the sets is top down, so the first set is the driving set and the second is the probed set. In the case of a nested loop, the first set is the outer loop. In the case of a hash join, the first set is used to build the hash table.
  3. One join is performed at a time, so you only need to consider two sets and their join operation at any one time.

Looking at the following execution plan, the order of the operations is 4, 5, 3, 6, 2, 9, 10, 8, 7, 1, 0.

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |    10 |   570 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN                    |                   |    10 |   570 |     7  (15)| 00:00:01 |
|   2 |   NESTED LOOPS                |                   |       |       |            |          |
|   3 |    NESTED LOOPS               |                   |    10 |   380 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | DEPARTMENTS       |     1 |    16 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
|   7 |   VIEW                        | index$_join$_004  |   107 |  2033 |     3  (34)| 00:00:01 |
|*  8 |    HASH JOIN                  |                   |       |       |            |          |
|   9 |     INDEX FAST FULL SCAN      | EMP_NAME_IX       |   107 |  2033 |     1   (0)| 00:00:01 |
|  10 |     INDEX FAST FULL SCAN      | EMP_EMP_ID_PK     |   107 |  2033 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Using Adrian Billington's utility, described below, we can show the plan including the order of the operations in the "Ord" column.

---------------------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 |     |  11 | SELECT STATEMENT              |                   |    10 |   570 |     7  (15)| 00:00:01 |
|*  1 |   0 |  10 |  HASH JOIN                    |                   |    10 |   570 |     7  (15)| 00:00:01 |
|   2 |   1 |   5 |   NESTED LOOPS                |                   |       |       |            |          |
|   3 |   2 |   3 |    NESTED LOOPS               |                   |    10 |   380 |     4   (0)| 00:00:01 |
|*  4 |   3 |   1 |     TABLE ACCESS FULL         | DEPARTMENTS       |     1 |    16 |     3   (0)| 00:00:01 |
|*  5 |   3 |   2 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   6 |   2 |   4 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
|   7 |   1 |   9 |   VIEW                        | index$_join$_004  |   107 |  2033 |     3  (34)| 00:00:01 |
|*  8 |   7 |   8 |    HASH JOIN                  |                   |       |       |            |          |
|   9 |   8 |   6 |     INDEX FAST FULL SCAN      | EMP_NAME_IX       |   107 |  2033 |     1   (0)| 00:00:01 |
|  10 |   8 |   7 |     INDEX FAST FULL SCAN      | EMP_EMP_ID_PK     |   107 |  2033 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Extending DBMS_XPLAN

Adrian Billington has created an "XPlan Utility", available here, to extend the output of DBMS_XPLAN to include the execution order of the steps. The following output shows the difference between the default output and that produced by Adrian's XPlan Utility.

CONN scott/tiger

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

SET LINESIZE 130

-- Default Output
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |     1 |    58 |     3	 (0)| 00:00:53 |
|   1 |  NESTED LOOPS		     |	       |       |       |	    |	       |
|   2 |   NESTED LOOPS		     |	       |     1 |    58 |     3	 (0)| 00:00:53 |
|*  3 |    TABLE ACCESS FULL	     | EMP     |     1 |    38 |     2	 (0)| 00:00:35 |
|*  4 |    INDEX UNIQUE SCAN	     | PK_DEPT |     1 |       |     0	 (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1	 (0)| 00:00:18 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.

SQL>


-- XPlan Utility output
@xplan.display.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation			 | Name    | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 |     |	6 | SELECT STATEMENT		 |	   |	 1 |	58 |	 3   (0)| 00:00:53 |
|   1 |   0 |	5 |  NESTED LOOPS		 |	   |	   |	   |		|	   |
|   2 |   1 |	3 |   NESTED LOOPS		 |	   |	 1 |	58 |	 3   (0)| 00:00:53 |
|*  3 |   2 |	1 |    TABLE ACCESS FULL	 | EMP	   |	 1 |	38 |	 2   (0)| 00:00:35 |
|*  4 |   2 |	2 |    INDEX UNIQUE SCAN	 | PK_DEPT |	 1 |	   |	 0   (0)| 00:00:01 |
|   5 |   1 |	4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |	 1 |	20 |	 1   (0)| 00:00:18 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

About
------
  - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)


18 rows selected.

SQL>

In the extended output, the "Ord" column displays the execution order of the plan steps.

QB_NAME Hint

Sometimes the same table is included in a query multiple times, so it is hard to know which operation in the execution plan refers to which reference to the table. The QB_NAME solves this problem by allowing you to name, or alias, individual query blocks. The alias information is displayed when the FORMAT parameter of the DISPLAY% functions is set to "ALL", or the " +ALIAS" value is added to the FORMAT parameter in 10gR2 onwards.

The following query references the same table twice, so we cannot easily tell from the execution plan which reference is which.

SELECT (SELECT COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count,
       (SELECT COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count
FROM   dual;

SET LINESIZE 100
SET PAGESIZE 50
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  4 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |
|   5 |  FAST DUAL         |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Next, we add the QB_NAME hint to the subqueries, giving each a recognizable alias. When the correct format is selected, the output from the DISPLAY% functions now includes a table giving us the relevant alias for each operation.

SELECT (SELECT /*+ QB_NAME(salesman) */ COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count,
       (SELECT /*+ QB_NAME(manager) */  COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count
FROM   dual;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALL));

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  4 |   TABLE ACCESS FULL| EMP  |     3 |    24 |     3   (0)| 00:00:01 |
|   5 |  FAST DUAL         |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SALESMAN
   2 - SALESMAN / EMP@SALESMAN
   3 - MANAGER
   4 - MANAGER  / EMP@MANAGER
   5 - SEL$1    / DUAL@SEL$1

GATHER_PLAN_STATISTICS Hint

Using the GATHER_PLAN_STATISTICS hint makes the optimizer gather the actual cardinalities in addition to the expected cardinalities in the execution plan. This can then be reported by the DISPLAY_CURSOR function if the format is set to 'ALLSTATS'.

CONN scott/tiger

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS                |         |      1 |        |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS               |         |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |    TABLE ACCESS FULL         | EMP     |      1 |      1 |      1 |00:00:00.01 |       7 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

Now the "Rows" column has been replaced by the original cardinality estimate (E-Rows) and the actual cardinality (A-Rows).

For more information see:

Hope this helps. Regards Tim...

Back to the Top.