DBA Tips Archive for Oracle

  


New Explain Plan Output in Oracle9i

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. Using DBMS_XPLAN
  3. DBMS_XPLAN Parameters
  4. The utlxpls.sql File



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



The utlxpls.sql File

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

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.


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 20-Jan-2006 14:40:58 EST
Page Count: 19880