Understanding Explain Plan

Disraeli was a lot of things, but Oracle Programmer was not amongst them. To be fair, perhaps he wasn't talking about Explain Plan?

SQL is a goal-oriented language. Unlike procedural languages, we tell the database what we want rather than how to get it. Oracle's Cost Based Optimizer comes up with an execution plan that is hopefully the most efficient way to resolve the query, but for many reasons it will often choose a sub-optimal plan.

Explain Plan is the tool we use to view the execution plan of a SQL. It can be invoked with a button-click in a variety of GUI tools such as Oracle Enterprise Manager (OEM), TOAD, SQL Navigator, and Oracle SQL Developer to name a few. As a result, awareness of Explain Plan has never been higher; but it is still problematic for many developers because it often shows the wrong plan, and even When it does show the right plan we do not interpret the results correctly.

Getting the Real Plan

The prevalence of GUI development tools is the greatest enemy of Explain Plan. Whilst they are all equipped with simple single-click Explain Plan tools, they frequently show the wrong plan; ie. not the plan that is used by the live production code. The Cost Based Optimizer is sensitive to a number of session-level database parameters, any of which may be overridden either by the production code or the GUI tool.

GUI tools are likely to show a different plan unless these settings are identical in both environments. Furthermore, Dynamic Sampling in Oracle 10g means that even with the same parameter settings, a SQL will not necessarily give the same plan in any two parses!

Look at the following plans; all generated for the same SQL using different settings of the initialisation parameter OPTIMIZER_GOAL:

EXPLAIN PLAN FOR
SELECT  *
FROM    ef_actl_expns
WHERE   lbcr_sk IN (
        SELECT  lbcr_sk
        FROM    ed_lbr_cst_role
        WHERE   lbr_actv_typ_cd = 'A'
)
/

ALTER SESSION SET OPTIMIZER_MODE = 'FIRST_ROWS'

--------------------------------------------------------------------------
| Id  | Operation                         | Name                         |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                              |
|   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| EF_ACTL_EXPNS                |
|   2 |   NESTED LOOPS                    |                              |
|   3 |    VIEW                           | index$_join$_002             |
|   4 |     HASH JOIN                     |                              |
|   5 |      INDEX FAST FULL SCAN         | ED_LBCR_DSGRP_LBRACTTYPCD_UK |
|   6 |      INDEX FAST FULL SCAN         | ED_LBCR_PK                   |
|   7 |    PARTITION LIST ALL             |                              |
|   8 |     BITMAP CONVERSION TO ROWIDS   |                              |
|   9 |      BITMAP INDEX SINGLE VALUE    | EF_AEXP_LBCR_FK              |
--------------------------------------------------------------------------


ALTER SESSION SET OPTIMIZER_MODE = 'FIRST_ROWS_1'

--------------------------------------------------------
| Id  | Operation                    | Name            |
--------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |
|   1 |  NESTED LOOPS                |                 |
|   2 |   PARTITION LIST ALL         |                 |
|   3 |    TABLE ACCESS FULL         | EF_ACTL_EXPNS   |
|   4 |   TABLE ACCESS BY INDEX ROWID| ED_LBR_CST_ROLE |
|   5 |    INDEX UNIQUE SCAN         | ED_LBCR_PK      |
--------------------------------------------------------


ALTER SESSION SET OPTIMIZER_MODE = 'FIRST_ROWS_1000'

-------------------------------------------------------------
| Id  | Operation                         | Name            |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |
|   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| EF_ACTL_EXPNS   |
|   2 |   NESTED LOOPS                    |                 |
|   3 |    TABLE ACCESS FULL              | ED_LBR_CST_ROLE |
|   4 |    PARTITION LIST ALL             |                 |
|   5 |     BITMAP CONVERSION TO ROWIDS   |                 |
|   6 |      BITMAP INDEX SINGLE VALUE    | EF_AEXP_LBCR_FK |
-------------------------------------------------------------


ALTER SESSION SET OPTIMIZER_MODE = 'CHOOSE'

-----------------------------------------------
| Id  | Operation           | Name            |
-----------------------------------------------
|   0 | SELECT STATEMENT    |                 |
|   1 |  HASH JOIN          |                 |
|   2 |   TABLE ACCESS FULL | ED_LBR_CST_ROLE |
|   3 |   PARTITION LIST ALL|                 |
|   4 |    TABLE ACCESS FULL| EF_ACTL_EXPNS   |
-----------------------------------------------

We can mitigate this problem by ensuring that the tuning parameters of our Explain Plan session are the same as the database default (some GUIs update them on startup), but there is no simple way to know whether any of those parameters have been modified by the application code. Fortunately, both Oracle 9i and 10g write the plans of all executed SQLs to the dynamic tuning view V$SQL_PLAN. All that needs to be done is to get the user to run the sub-optimal query in Production and then capture that plan as the SQL is executed.

OEM has such a feature in the Instance/Sessions window (not in SQL Scratchpad!), where you choose a session and then view the SQL and its plan. For those who find OEM unacceptably slow, or where it is not available on the desktop, the following scripts can be used.

sid.sql -- List currently running session IDs

SET LINES 80 LONG 65536

CLEAR column

COLUMN username FORMAT A10 WRAP
COLUMN prog_event FORMAT A35 WRAP
COLUMN run_time FORMAT A10 JUSTIFY RIGHT
COLUMN sid FORMAT A4 NEW_VALUE sid
COLUMN status FORMAT A10

ACCEPT search_string PROMPT "Search for: "

SELECT  to_char(s.sid) AS sid
,       s.username || chr(10) || s.osuser AS username
,       s.status || chr(10) || 'PID:' || p.spid AS status
,       lpad(
                to_char(
                        trunc(24*(sysdate-s.logon_time))
                ) ||
                to_char(
                        trunc(sysdate) + (sysdate-s.logon_time)
                ,       ':MI:SS'
                )
        , 10, ' ') AS run_time
,       s.program ||  chr(10) || s.event AS prog_event
FROM    v$session s
JOIN    v$process p ON (p.addr = s.paddr)
WHERE   s.username <> 'DBSNMP'
AND     audsid != sys_context('USERENV','SESSIONID')
AND     upper(
                s.osuser || '|' ||
                s.program || '|' ||
                s.event || '|' ||
                s.sid || '|' ||
                s.username || '|' ||
                p.spid
        ) LIKE upper('%&search_string.%')
ORDER BY
        sid
/

Only the SELECT statement above is necessary if running from a GUI tool. The other commands and the chr(10) concatenation simply format the output for SQL*Plus.

plans.sql -- List the EXPLAIN PLAN for a currently running session

SELECT  p.plan_table_output
FROM    v$session s
,       table(dbms_xplan.display_cursor(s.sql_id, s.sql_child_number)) p
where   s.sid = &1
/

Run sid.sql to find the Session ID of a currently running process. It will list the SID, along with the OS User ID, Database User ID, Server Process ID, client program (eg. SQL*Plus), and event information (what the session is currently doing). The script prompts for a search string to filter the list. Once you have identified the session, run plans.sql with the identified SID to list the Explain Plan of the last SQL parsed by that session.

SQL> @sid
Search for:

SID  USERNAME   STATUS       RUN_TIME PROG_EVENT
---- ---------- ---------- ---------- -----------------------------------
138  CPROD      INACTIVE      3:06:26 java.exe
     c983127    PID:18756             SQL*Net message from client

150  CPROD      INACTIVE      3:06:13 java.exe
     c983127    PID:18770             SQL*Net message from client

153  CPROD      INACTIVE      0:20:12 sqlplus@lxapp0046v (TNS V1-V3)
     c985675    PID:25335             SQL*Net message from client


SQL> @plans 153

SQL_ID  06j2nyu76pv9v, child number 0
-------------------------------------
select min(actt_sk) from ed_actv_typ where actt_cd like '6%'

Plan hash value: 4235929892

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE              |                 |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| ED_ACTV_TYP     |    88 |   968 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ED_ACTT_ACTT_NK |    88 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ACTT_CD" LIKE '6%')
       filter("ACTT_CD" LIKE '6%')


21 rows selected.

The dbms_xplan.display_cursor table function used in plans.sql is a feature of Oracle 10g. A less elegant equivalent for 9i can be downloaded from http://people.aapt.net.au/roxsco/tuning/plans9.sql

The Predicate Information section was introduced to Explain Plan in v9i, but is not displayed in GUI's that were built on an 8i (or earlier) database. This information is invaluable - it removes much of the guesswork from interpreting the plan. If your Explain Plan does not display it, use dbms_xplan.display instead.

Interpreting Explain Plan

Explain Plan should be interpreted as a tree using the indentation to identify parent/child relationships. Detecting parent-child relationships is fairly simple; check the Oracle Perormance Tuning manual for further explanation. What's not so obvious is what those relationships mean?

The sections below refer to the following sample Explain Plan.

Sample Explain Plan

-----------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Pstart| Pstop |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                    |  1348 |       |       |
|*  1 |  FILTER                            |                    |       |       |       |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| EF_ACTL_REV        |    95 |       |       |
|   3 |    NESTED LOOPS                    |                    |  1348 |       |       |
|*  4 |     HASH JOIN                      |                    |    14 |       |       |
|   5 |      MAT_VIEW ACCESS BY INDEX ROWID| PD_PROJ            |     1 |       |       |
|*  6 |       INDEX RANGE SCAN             | PD_PROJ_PROJNUM_IX |     1 |       |       |
|   7 |      INDEX FAST FULL SCAN          | ED_PRJMAP_PK       |   237K|       |       |
|   8 |     PARTITION LIST ITERATOR        |                    | 37867 |   KEY |   KEY |
|*  9 |      INDEX RANGE SCAN              | EF_AREV_PRJMAP_IX  | 37867 |   KEY |   KEY |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y))
   2 - filter("AREV"."EFF_CMTH_SK">=TO_NUMBER(:X) AND "AREV"."EFF_CMTH_SK"<=TO_NUMBER(:Y))
   4 - access("PRJMAP"."PROJ_SK"="B"."PROJ_SK")
   6 - access("B"."PROJ_NUM"=:B)
   9 - access("AREV"."PRJMAP_SK"="PRJMAP"."PRJMAP_SK")

Plan steps with no children

A step in the plan with no dependents is a leaf of the tree (steps 6,7, and 9 in the sample above). A leaf step will be either a Table Access or an Index Scan; the Rows (or Cardinality) column tells us how many rows the scan should return. Simple? Well, not quite; there is a vital piece of information missing: How many times will the step be executed? An Index Range Scan that returns 500 rows is hardly cause for alarm; but if it is going to be exectued 2 million times then we have a problem.

Looking at a step in isolation (and this applies to branch steps as well as leaf steps), you cannot tell how many times it will be executed; you need to look at its ancestors in the tree.

Watch for:

• INDEX RANGE SCAN. This is probably the most insidious performance hole in Oracle. A Range Scan can return any number of rows; 1, 100, 100 million - the Rows column in Explain Plan often gets it wrong.
• TABLE ACCESS FULL. Full table scans (with high row counts) when you are performing low-volume transactional SQL. Full table scans are OK for high-volume batch processes and reports.

Plan steps with 1 child

Plan steps with one child fall into three main classes:

• Passive Operations

  Operations such as VIEW and PX SEND simply pass data through unaltered to a parent step. They may be ignored.

• Iterative Operations

  INLIST ITERATOR, PARTITION INLIST, PARTITION ALL, PARTITION ITERATOR, and PX ITERATOR all execute the child step many times.

  Even though we cannot tell from the plan how many times the child steps will be executed, the Rows column displays the expected number of rows for all iterations, not the average per iteration. For example, step 8 above expects to return 36867 rows in total, not per partition. Note that this is in contrast to plan steps with 2 children (see below).

• Active Operations

  All other operations with a single child are active; they receive the row set from the child, do something to it, then pass it on to the parent.

Note: the terms Passive, Iterative, and Active are just a learning-aid; they are not used by Oracle. If you use them, don't expect anyone to understand what you are talking about.

Watch for:

• SORT operations with high row counts. If a result set is small enough then Oracle will perform a very efficient in-memory sort. Beyond a certain size (depending on the setup of your database and session) the sort will need to page to disk; this can double the sort time or much worse. This means that execution times for small volumes will not scale proportionally to larger volumes.
• FILTER is an unusual step in its single-child form. Look at the Filter condition in the Predicate Information section of the plan. If the condition references any table columns from subordinate steps, then the filter is applied after the child step, filtering non-matching rows as they are returned. If the condition references only bind variables and constants, then it is evaluated before the child step; if the expression evaluates False, the the child step is not executed at all. Step 1 in the plan above is a good example.
• PARTITION ALL and any operation containing the word ITERATOR are iterative; they execute the child step many times. Note that the Rows column shows the total number of rows expected for all iterations; not per iteration.
• A VIEW operation is often encountered when selecting from a database view, an inline view, or simply when joining a large number of tables. It is a popular misconception that a VIEW operation will cause the result set to be materialised in TEMP space before proceeding with parent steps. This is not true; the VIEW operation appears to have no effect on the plan at all.

Plan steps with 2 children

There are two ways to interpret steps with two children:

1. Active: Do A, then do B.
2. Iterative: For each A, do B.

The difference is one of the most critical aspects of performance tuning. NESTED LOOPS, FILTER, and MERGE JOIN CARTESIAN are the only iterative operations; all others are active. Unlike the single-child iterative operations described above, the Rows measure is the expected number of rows for a single iteration of step 2. In the example above, Step 8 (37,867 rows) will be performed once for each row returned by Step 4 (14 rows). So instead of 37,867 rows, it is really 530,138 rows!

Watch for:

• NESTED LOOPS and FILTER operations with a large number of rows in the first child step, especially when the second child step returns more than one row or has subordinate steps; the cost of repeating the second child step so many times can be prohibitive. Exception: if the second child step is a unique index scan without a TABLE ACCESS, it can be very efficient in a NESTED LOOPS or FILTER operation.
• MERGE JOIN CARTESIAN has a bad reputation from the days of the Rule Based Optimizer because it usually signalled a programming error, and was the cause of a performance problem. Under the Cost Based Optimizer, MERGE JOIN CARTESIAN is often used to join two unrelated tables where one table will return just a single row (or no rows). A cartesian join is only a problem if both row sources in the join have a large number of rows.
• HASH JOIN is especially efficient when one of the sources is small (say, <10000 rows). The smaller table should always be the first child step in the Explain Plan. If both tables are large, or if the large table is in the first child step, then Oracle will run out of Temp space to do the join and will start paging to disk.

Summary

Explain Plan is not a magic bullet. It can be useful, but not if you are looking at the wrong plan, and not if you are looking for the wrong things. As a very general rule of rule of thumb, low-volume SQL (eg. Screen interfaces) should use Index Scans and Nested Loops joins; high-volume SQL (batch jobs) should use Full Scans and Hash Joins.

Remember that the stats shown in Explain Plan - even those taken from live SQLs in V$SQL_PLAN are estimates, not actual row counts. If a plan looks OK based on the row counts, trace the session and view the actual row counts in TK*Prof - see the Performance Tuning Manual for details.