 |
|
Oracle execution plan tips
Oracle Tips by Burleson Consulting |
An Oracle execution plan
(explain plan) is a way to see the internal row fetching mechanisms that are
used to retrieve the rows from an SQL statement. As author of the
officially authorized Oracle Press book "Oracle High
Performance SQL Tuning", I've been using execution plans for over a
decade, and I can say that tuning SQL can be very difficult without
understanding SQL execution plans.
My latest book
book "Oracle
Tuning: The Definitive Reference", is a great way to understand SQL tuning
with SQL execution plans, and I highly recommend it for SQL tuning with
execution plans.
Displaying an
execution plan
For any SQL statement you can quickly see the
execution plan in SQL*Plus with the "set autotrace" command, followed by
your 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)
Oracle execution plan methods
When you display an Oracle execution plan you
will see many tables access methods, including TABLE ACCESS FULL, INDEX RANGE
SCAN, INDEX UNIQUE SCAN, and many, many others including LOOP execution plans
such as NESTED LOOP, MERGE and HASH JOIN. It's important to understand
these execution plan steps so that you understand how your tables are being
accessed by Oracle.
System-wide execution plans
To see an aggregation of all SQL access methods
in your database with counts,
see
plan9i.sql, a great way to locate missing indexes and see how your tables
are being accessed by the SQL, and to locate indexing opportunities:
Full table scans and counts
Note that "K" indicates in the table is in the KEEP pool.
OWNER NAME NUM_ROWS C K BLOCKS NBR_FTS
-------------- ------------------------ --------- - - -------- --------
SYS DUAL N 2 97,237
SYSTEM SQLPLUS_PRODUCT_PROFILE N K 2 16,178
DONALD PAGE 3,450,209 N 932,120 9,999
DONALD RWU_PAGE 434 N 8 7,355
DONALD PAGE_IMAGE 18,067 N 1,104 5,368
DONALD SUBSCRIPTION 476 N K 192 2,087
DONALD PRINT_PAGE_RANGE 10 N K 32 874
For time-series execution plan observation, see
the script
plan10g.sql. For even more detailed SQL execution statistics, see the
TKPROF (SQL Trace) utility.
For more execution plan tricks, see my
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
