Home  >  Oracle Database Administration (DBA)  >  Database Tuning  >  The "Explain Plan" Command

    Explain Plan Overview

     Every time a SQL statement is sent to the database engine, during the parse phase, the Oracle Optimizer analyze the statement and find the "best" execution plan. The same statement could be executed in many ways, having the same result, but using different the CPU, the memory and the disks. The manner in which the statement will be executed is in function of the Oracle Optimizer settings and after the parse phase this information will be put in the Library Cache. 

     Sometimes we want to know which execution plan is/ will be used by Oracle, but we don't need to run the statement (supposing we have an update on a table in production, using CBO, with different statistics in production from development database). In such a case Oracle provide us with the "explain plan" command. 

     Checking the existence of PLAN_TABLE

     If the plan table is not accessible this table must be created using $ORACLE_HOME/rdbms/admin/UTLXPLAN.SQL script. This table keep the information about the execution plan and is populated after each "explain plan" command. If in Oracle database 9i the PLAN_TABLE is/ was a table in 10g the PLAN_TABLE is a global temporary table (with the option "on commit preserve rows"). So, in 10g we don't need to delete or truncate the PLAN_TABLE to release the space used by this object.

 Populating the PLAN_TABLE 

 This table is populated by using the "explain plan" command. 

The syntax for "explain plan" command is:

        EXPLAIN PLAN FOR <SQL statement>;

 See the picture below:

Displaying the execution plan

One time the PLAN_TABLE is populated with the information about the SQL statement execution plan, this table could be accessed:

•  directly  using the SELECT statement:

SELECT substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",

              object_name "Object Accessed",

              COST

FROM plan_table

START WITH id = 0

CONNECT BY PRIOR id=parent_id;

More information about  this subject ( The "Explain Plan" Command ) you can get from  www.in-oracle.com