Oracle9i New Feature Series: Explain Plan Output Using DBMS Package

Remember the SQL you use to get the explain plan from the PLAN_TABLE and you perform and EXPLAIN on a statement:

  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

Well, you may never use the above SQL, Oracle9i gives you a package to look at the explain plans – DBMS_XPLAN.

Here is an example:

SQL> EXPLAIN PLAN FOR

  2  select object_type, object_name

  3  from test

  4* where object_name = 'DWA_JOB_LOG'

SQL> /

 

Explained.

 

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

 

PLAN_TABLE_OUTPUT

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

 

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

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

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

|   0 | SELECT STATEMENT            |             |     2 |    62 |    46 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     2 |    62 |    46 |

|*  2 |   INDEX SKIP SCAN           | TEST_I1     |     2 |       |    45 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("TEST"."OBJECT_NAME"='DWA_JOB_LOG')

       filter("TEST"."OBJECT_NAME"='DWA_JOB_LOG')

 

Note: cpu costing is off

 

16 rows selected.

 

SQL>

SQL> alter table test parallel;

 

Table altered.

 

SQL> delete from plan_table;

 

3 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> EXPLAIN PLAN FOR

  2  select object_type, object_name

  3  from test

  4  where object_name = 'DWA_JOB_LOG'

  5  /

 

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     |             |     2 |    62 |     5 |        |      |            |

|*  1 |  TABLE ACCESS FULL   | TEST        |     2 |    62 |     5 | 80,00  | P->S | QC (RAND)  |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("TEST"."OBJECT_NAME"='DWA_JOB_LOG')

 

Note: cpu costing is off

 

14 rows selected.

 

SQL>

 

The following are the parameters of DBMS_XPLAN.DISPLAY procedure:

 

DBMS_XPLAN.DISPLAY(

   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',

   statement_id  IN  VARCHAR2  DEFAULT  NULL, 

   format        IN  VARCHAR2  DEFAULT  'TYPICAL');