 |
|
Inside Oracle execution plans
Oracle Tips by Burleson Consulting |
For complete details on tuning SQL
with execution plans (explain plan), see
my book "Oracle
Tuning: The Definitive Reference". Also see my notes on tuning
historical SQL from STATSPACK and AWR.
The "explain plan" utility is provided by Oracle to allow you to see the
internal machinations of how your SQL is executed.
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 = 22;
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.
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.
See my other notes on reading SQL execution
plan here:
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
