In Oracle : Oracle Execution Plan : Explain plan

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle Execution Plan : Explain plan

 

 Oracle Execution Plan : Explain plan

 

 

Here are the main topics of this article:

     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

 

Oracle execution plan

 

     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:

 

Oracle execution plan

 

 

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;

  • using the utlxpls.sql (utlxpls.sql is a script that Oracle ships):

Oracle execution plan

 

  • using  table(dbms_xplan.display) table

Oracle execution plan

 

 

    To change the execution plan indexes must be added, hints could be inserted in the SQL statement or we have to take more statistics (for CBO).

 

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Oracle Execution Plan : Explain plan

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.