Oracle SQL Execution Plan tips
Oracle Database Tips by Donald Burleson
Understanding a SQL execution
plan is critical to SQL tuning as I note in my book
Tuning: The Definitive Reference".
Also see how to determine the
sequence of the steps in an execution plan.
The execution plan reveals the internal details about the SQL execution
including table join types (nested loops, hash join, merge join), and
Creating an Execution
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 to see the execution plan:
SQL> set autotrace traceonly explain
SQL> select ename from emp where empno = 12;
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
For even more detailed SQL execution plan statistics, see the TKPROF (SQL Trace)
Reading an Oracle Execution
For a full tutorial, get my
Tuning: The Definitive Reference" where I've devoted over 100 pages to
reading execution plans.
My goals for SQL tuning with execution plans are simple, "Fetch the rows while
minimizing data block touches":
- Eliminate sub-optimal
large-table full-table scans: Ensure that the fastest access path to the
data is chosen. The execution plan "FULL" is the key.
- Use fastest table join
method: The optimizer must choose intelligently between nested loops
joins, hash joins and star transformation join methods and these are
displayed in the execution plan.
- Ensure optimal
table-joining order: SQL will run fastest when the first table joins
deliver the smallest result set.
In sum, I review execution
plans looking for tuning opportunities as follows:
- Add indexes (especially
- Change the optimizer
- Employ Materialized Views
- Improve CBO statistics
- Cache high-use small
tables & indexes
- Tune the library cache
- Adjust segment structure
(large blocksizes for index range scans, small
- blocksizes for random OLTP)
Global Tuning with
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).
More information on Oracle execution plans is available
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.