 |
|
Oracle execution plan tips
Oracle Tips by Burleson Consulting |
Understanding a SQL execution
plan is critical to SQL tuning as I note in my book
"Oracle
Tuning: The Definitive Reference".
The execution plan reveals the internal details
about the SQL execution including table join types (nested loops, hash join,
merge join), and sorting.
Creating an execution
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)
Statistics
----------------------------------------------------------
83 recursive calls
0 db block gets
21 consistent gets
3 physical reads
0 redo size
221 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
For even more detailed SQL execution statistics, see the TKPROF (SQL Trace)
utility.
Reading an execution
plan
For a full tutorial, get my
book "Oracle
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 loop
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
function-based indexes)
- Change the optimizer
parms
- Employ Materialized Views
- Improve CBO statistics
(especially histograms)
- Cache high-use small
tables & indexes
- Tune the library cache
(cursor_sharing, etc)
- Adjust segment structure
(large blocksizes for index range scans, small blocksizes for random OLTP)
Global tuning with
execution 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).
