Oracle SQL Tuning: Execution Plan

EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

• An ordering of the tables referenced by the statement
• An access method for each table mentioned in the statement
• A join method for tables affected by join operations in the statement
• Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

• Optimization, such as the cost and cardinality of each operation
• Partitioning, such as the set of accessed partitions
• Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

Running EXPLAIN PLAN

To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example:

EXPLAIN PLAN FOR SELECT last_name FROM employees;

This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE.

You can specify the INTO clause to specify a different table.

EXPLAIN PLAN 
   INTO my_plan_table 
   FOR 
   SELECT last_name FROM employees;

You can specify a statement Id when using the INTO clause.

EXPLAIN PLAN
   SET STATEMENT_ID = 'st1'
   INTO my_plan_table 
   FOR 
   SELECT last_name FROM employees;

Displaying PLAN_TABLE Output

DBMS_XPLAN.DISPLAY procedure accepts options for displaying the plan table output. You can specify:

• A plan table name if you are using a table different than PLAN_TABLE
• A statement Id if you have set a statement Id with the EXPLAIN PLAN
• A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   938 | 37520 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEE|   938 | 37520 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------

Customizing PLAN_TABLE Output

If you have specified a statement identifier, then you can write your own script to query the PLAN_TABLE. For example:

• Start with ID = 0 and given STATEMENT_ID.

• Use the CONNECT BY clause to walk the tree from parent to child, the join keys being STATEMENT_ID = PRIOR STATEMENT_ID and PARENT_ID = PRIOR ID.

• Use the pseudo-column LEVEL (associated with CONNECT BY) to indent the children.

SELECT cardinality "Rows",
   lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
  FROM PLAN_TABLE
CONNECT BY prior id = parent_id
        AND prior statement_id = statement_id
  START WITH id = 0
        AND statement_id = 'st1'
  ORDER BY id;

   Rows Plan
------- ----------------------------------------
        SELECT STATEMENT
         TABLE ACCESS FULL EMPLOYEES

The NULL in the Rows column indicates that the optimizer does not have any statistics on the table. Analyzing the table shows the following:

   Rows Plan
------- ----------------------------------------
  16957 SELECT STATEMENT
  16957  TABLE ACCESS FULL EMPLOYEES

You can also select the COST. This is useful for comparing execution plans or for understanding why the optimizer chooses one execution plan over another.