Explain Plan In Oracle

One of the most usefull tool which require for both DBAs and Developers to use it effectively to avoid the performance problem of Custom Code specailly in ERP database as well as any database. We should make best use of it.

 An execution plan is a list of steps that Oracle will follow in order to execute a SQL statement. Each step is one of a finite number of basic operations known to the database server. Even the most complex SQL statement can be broken down into a series of basic operations.
EXPLAIN PLAN is a statement that allows you to have Oracle generate the execution plan for any SQL statement without actually executing it. You will be able to examine the execution plan by querying the plan table.

 The Plan Table

1.A plan table holds execution plans generated by the EXPLAIN PLAN statement.
2.The typical name for a plan table is plan_table, but you may use any name you wish.
3.Create the plan table by running utlxplan.sql, located in $ORACLE_HOME/rdbms/admin.

Input of the Plan Table  

 statement_id: Unique identifier for each execution plan
 timestamp:  When the execution plan was generated
 operation:  The operation performed in one step of the execution plan, such as “table access”
 options:  Additional information about the operation,such as “by index ROWID”
 object_name:  Name of table, index, view, etc. accessed
 optimizer_id :           Optimizer goal used when creating execution plan Step number in execution plan
 parent_id:  Step number of parent step

 Explain Plan Pre-Req 

  INSERT privilege on a plan table
  All necessary privileges to execute the statement being explained
  SELECT privileges on underlying tables of views, if the statement being explained involves views

Explan Plan Syntax

EXPLAIN PLAN
[SET STATEMENT_ID = <string in single quotes>] [INTO <plan table name>] FOR
<SQL statement>;

Querying an Execuction Plan from the Plan Table

Use a CONNECT BY clause to trace the hierarchy
Use LPAD function to indent rows, making the hierarchy easier to follow
Put statement_id in WHERE clause to retrieve only one execution plan at a time
Sample script on next slide shows the most important information
You can also try utlxpls.sql or utlxplp.sql in $ORACLE_HOME/rdbms/admin

Sample Query to Display Execution

SET VERIFY OFF
ACCEPT stmt_id CHAR PROMPT “Enter statement_id: ”
COL id          FORMAT 999
COL parent_id   FORMAT 999 HEADING “PARENT”
COL operation   FORMAT a35 TRUNCATE
COL object_name FORMAT a30
SELECT     id, parent_id, LPAD (‘ ‘, LEVEL – 1) ||
           operation || ‘ ‘ || options operation,
           object_name
FROM       plan_table
WHERE      statement_id = ‘&stmt_id’
START WITH id = 0
AND        statement_id = ‘&stmt_id’
CONNECT BY PRIOR id = parent_id
AND        statement_id = ‘&stmt_id’;
Sample Execution Plan

SQL> EXPLAIN PLAN SET statement_id = ‘demo’ FOR
  2  SELECT a.customer_name, a.customer_number, b.invoice_number,
  3         b.invoice_type, b.invoice_date, b.total_amount,
  4         c.line_number, c.part_number, c.quantity, c.unit_cost
  5  FROM   customers a, invoices b, invoice_items c
  6  WHERE  c.invoice_id = :b1
  7  AND    c.line_number = :b2
  8  AND    b.invoice_id = c.invoice_id
  9  AND    a.customer_id = b.customer_id;
Explained.
SQL> @explain.sql
Enter statement_id: demo
  ID PARENT OPERATION                           OBJECT_NAME
—- —— ———————————– —————–
   0        SELECT STATEMENT
   1      0  NESTED LOOPS
   2      1   NESTED LOOPS
   3      2    TABLE ACCESS BY INDEX ROWID      INVOICE_ITEMS
   4      3     INDEX UNIQUE SCAN               INVOICE_ITEMS_PK
   5      2    TABLE ACCESS BY INDEX ROWID      INVOICES
   6      5     INDEX UNIQUE SCAN               INVOICES_PK
   7      1   TABLE ACCESS BY INDEX ROWID       CUSTOMERS
   8      7    INDEX UNIQUE SCAN                CUSTOMERS_PK


In my next post i will try to cover other ways to view execution plan with some examples. 

Share This Post with Your Friends over Social Media!

About the Author Rajat

I am Rajat Dey and I have more than decade of Oracle experience from all major regions throughout the globe. I am well versed in most facets of Oracle, supporting database, fusion middleware and the eBusiness suite across many operating system platforms.Currently I am located in Sydney  Oracle Financials 11i and Release 12,12.2  Oracle RDBMS 7x,8i , 9i,10g and 11g  Amazon cloud .  Oracle RAC 10g and 11g  Oracle Applications Server  Oracle Web logic Server  OBIEE  Hyperion  SSO  Oracle Portal  PL/SQL  UNIX  SQL  MS-SQL 2008.  OEM 12c .  Data Guard .  Essbase .  Database backup and recovery.  Performance Tunning.  TimeSten

Leave a Comment: