Never complain and never explain.
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 thewrong 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
andPX SEND
simply pass data through unaltered to a parent step. They may be ignored. - Iterative Operations
INLIST ITERATOR
,PARTITION INLIST
,PARTITION ALL
,PARTITION ITERATOR
, andPX 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 evaluatedbefore 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 wordITERATOR
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 aVIEW
operation will cause the result set to be materialised in TEMP space before proceeding with parent steps. This is not true; theVIEW
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:
- Active: Do A, then do B.
- 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
andFILTER
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 aTABLE ACCESS
, it can be very efficient in aNESTED LOOPS
orFILTER
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.
- rleishman's blog
- Login to post comments
Inaccessible link for Oracle 9i
Hi,
Thanks for providing an illustrative article on explain plan.
The link provided for Oracle 9i is not correct.
http://people.aapt.net.au/roxsco/tuning/plans9.sql
Please update the link so that it can be used for the system still running Oracle 9i.
Thanks,
Priya.
Here's the script
Sorry about that. The tuning guide of which the script was supposed to be a part is now at http://www.orafaq.com/tuningguide but it looks like I never got around to adding plans9.sql
So here it is. I'll work on getting the link updated and file posted.
Cheers,
Ross.