How To Produce An Explain Plan

BACKGROUND & OVERVIEW

In order to identify whether a query is performing efficiently or not it is useful to produce an explain plan. This document describes the steps required to create such a plan.

CREATE THE PLAN TABLE

If a plan table does not already exist, use the following steps to create one:

  1. Log on the database as SYS.
  2. Create the PLAN_TABLE by running $ORACLE_HOME/rdbms/admin/utlxplan.sql
  3. Provide access to the PLAN_TABLE for the required users:
    • CREATE OR REPLACE PUBLIC SYNONYM plan_table FOR sys.plan_table;
    • GRANT SELECT, INSERT, UPDATE, DELETE ON plan_table TO <user name>;

ADD DATA TO THE PLAN TABLE

Generate the plan table data by using the following steps:

  1. Log on to the database as <user name>.
  2. Remove old data from the PLAN_TABLE:
    • DELETE FROM plan_table;
    • COMMIT;
  3. Generate new plan table data:
    • EXPLAIN PLAN FOR <enter sql to be explained here>;

QUERY THE PLAN TABLE DATA

Use the following script to produce an explain plan report:

SELECT
   LPAD( ' ' , 2*LEVEL )||operation||' '||options||' '||object_name
   EXPLAIN_PLAN
FROM
   plan_table
CONNECT BY
   PRIOR id = parent_id
START WITH
   id = 1
/
© copyright 2001-2014 ABCdba.com | all rights reserved