 |
|
Using explain plan
Oracle Tips by Burleson Consulting |
The "explain plan" utility is
provided by Oracle to allow you to see the internal machinations of how your SQL
is executed. Using the "explain plan" to solve SQL tuning problems is
central in my book
"Oracle
Tuning: The Definitive Reference".
The explain plan shows table access methods (FULL,
INDEX), table join types (nested loops, hash join, merge join), and sorting.
Creating a SQL
explain plan
For any specific SQL query you can see SQL
execution plan. In SQL*Plus you can issue the "set autotrace" command
and then issue your SQL statement:
SQL> set autotrace traceonly explain
SQL> select ename from emp where empno = 12;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
To see the output of an explain
plan, you must first create a “plan table.” Oracle provides a script in
$ORACLE_HOME/rdbms/admin called utlxplan.sql. Execute utlxplan.sql and create a
public synonym for the plan_table:
sqlplus > @utlxplan
Table created.
sqlplus > create public synonym plan_table for sys.plan_table;
Synonym created.
Most relational databases use an explain utility that takes the SQL statement as
input, runs the SQL optimizer, and outputs the access path information into a
plan_table, which can then be interrogated to see the access methods.
EXPLAIN PLAN SET STATEMENT_ID = 'test1'
FOR
SET STATEMENT_ID = 'RUN1'
INTO plan_table
FOR
SELECT
For even more detailed SQL execution statistics, see the
TKPROF
(SQL Trace) utility.
Reading an explain
plan
For a full tutorial, get my
book "Oracle
Tuning: The Definitive Reference" where I've devoted over 100 pages to
reading execution plans.
The most common way of determining the execution plan for a SQL statement is to
use Oracle's explain plan utility.
Global tuning with
explain plans
My
plan9i and plan10g scripts are invaluable for holistic SQL tuning because
they examine v$sql_plan and dba_hist_sql_plan and plot summary
execution plans, identifying possible unnecessary large-table full-table scans
(that may result from a missing index), and many, many other techniques.
