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.
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