Using EXPLAIN PLAN in different ways

Overview

Does this query look familiar?

SELECT

    LPAD(' ', 2*level) ||

    operation   || ' ' ||

    options     || ' ' ||

    object_name ||

    ' COST='    || cost   "Execution Plan"

FROM plan_table

CONNECT BY PRIOR id = parent_id

    START WITH id = 1;

Hopefully after reading this article, you may never have to use it again. Oracle9i introduced a new package, DBMS_XPLAN that can be used to not only simplify obtaining an explain plan, but to give you much more detailed information about the access path. This article provides a brief overview of the new DBMS_XPLAN PL/SQL package and its use for obtaining the access path of SQL.

Using DBMS_XPLAN

The DBMS_XPLAN PL/SQL package is used obtain the access path (explain plan) of the last explain plan run.Let's start by populating the PLAN_TABLE with the access path of a trivial query:

SQL> EXPLAIN PLAN FOR

  2  SELECT e.name, e.position, d.name

  3  FROM emp e JOIN dept d USING (dept_id);

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT     |             |    10 |   540 |     5 |

|*  1 |  HASH JOIN           |             |    10 |   540 |     5 |

|   2 |   TABLE ACCESS FULL  | EMP         |    10 |   380 |     2 |

|   3 |   TABLE ACCESS FULL  | DEPT        |    21 |   336 |     2 |

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

Predicate Information (identified by operation id):

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

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

Note: cpu costing is off

16 rows selected.

Let's now clear out all records from the PLAN_TABLE:

SQL> delete from plan_table;

4 rows deleted.

SQL> commit;

Commit complete.

Here is another example of how we can query access plans with parallel options:

SQL> alter table emp parallel;

Table altered.

SQL> alter table dept parallel;

Table altered.

SQL> EXPLAIN PLAN FOR

  2  SELECT e.name, e.position, d.name

  3  FROM emp e JOIN dept d USING (dept_id);

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation            |  Name   | Rows  | Bytes | Cost  |  TQ    |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT     |         |    10 |   540 |     2 |        |      |            |

|*  1 |  HASH JOIN           |         |    10 |   540 |     2 | Q1,01  | P->S | QC (RAND)  |

|   2 |   TABLE ACCESS FULL  | EMP     |    10 |   380 |     1 | Q1,01  | PCWP |            |

|   3 |   TABLE ACCESS FULL  | DEPT    |    21 |   336 |     1 | Q1,00  | P->P | BROADCAST  |

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

Predicate Information (identified by operation id):

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

Note: cpu costing is off

16 rows selected.

DBMS_XPLAN Parameters

SQL> desc dbms_xplan

FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE

 Argument Name                  Type                    In/Out Default?

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

 TABLE_NAME                     VARCHAR2                IN     DEFAULT

 STATEMENT_ID                   VARCHAR2                IN     DEFAULT

 FORMAT                         VARCHAR2                IN     DEFAULT

Now, let's take a look at the parameters to the DBMS_XPLAN.DISPLAY procedures:

DBMS_XPLAN.DISPLAY(

    table_name       IN    VARCHAR2   DEFAULT   'PLAN_TABLE',

    statement_id     IN    VARCHAR2   DEFAULT   NULL,

    format           IN    VARCHAR2   DEFAULT   'TYPICAL'

);

·         table_name - Used to specify the table name where the plan is stored. By default, the table name is PLAN_TABLE which is also the default used by the EXPLAIN PLAN statement.

·         statement_id - Used to specify the statement_id of the plan to be displayed. This parameter is NULL by default which is also the default used by the EXPLAIN PLAN statement.

·         format - Used to control the level of detail for the displayed plan. The default value for this parameter is TYPICAL. Here are the other values this parameter can accept:

o    BASIC : Displays only minimal amount of information. Information like the operation ID, object name(s), and operation options are displayed.

o    TYPICAL : This is the default value. This option displays the most relevant information in the plan. This would include parition pruning, parallelism, and query predicates.

o    ALL : Displays the maximum level of detail. This will include information that would be displayed at the TYPICAL level and then adds the SQL statements generated for parallel execution servers (only if parallel is being used).

o    SERIAL : Similar to TYPICAL except that no parallel information is displayed, even if the plan executes with parallel options.

---

The utlxpls.sql File

Within the $ORACLE_HOME/rdbms/admin directory, Oracle includes two files:

·         utlxpls.sql - UTiLity eXPLain Serial plans. This query will not display information related to Parallel Query.

·         utlxplp.sql - UTiLity eXPLain Parallel plans. This query will display Parallel Query information if the plan happens to run parallel.

I like to reference these two files whenever I need a refresher on how to query DBMS_XPLAN. The script can be simply run from within SQL*Plus as follows:

SQL> explain plan for select /*+ index_ss(test1 test1_i) */ a, b from test1 where b = 101;

Explained.

SQL> start ?\rdbms\admin\utlxpls.sql

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT     |             |     1 |     5 |    11 |

|*  1 |  INDEX SKIP SCAN     | TEST1_I     |     1 |     5 |    11 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

   1 - access("TEST1"."B"=101)

       filter("TEST1"."B"=101)

Note: cpu costing is off

15 rows selected.

SQL> start ?\rdbms\admin\utlxplp.sql

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT     |             |     1 |     5 |    11 |

|*  1 |  INDEX SKIP SCAN     | TEST1_I     |     1 |     5 |    11 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

   1 - access("TEST1"."B"=101)

       filter("TEST1"."B"=101)

Note: cpu costing is off

15 rows selected.