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 "Oracle 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 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 to see the execution plan:

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)

For even more detailed SQL execution plan statistics, see the TKPROF (SQL Trace) utility.

Reading an Oracle 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 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 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).

More information on Oracle execution plans is available HERE.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.