Explain Plan Lies

The Oracle Explain Plan command is widely used to evaluate the plan that the Oracle optimizer will choose for a given SQL statement. Unfortunately, it doesn’t always tell the truth. This is due to the fact that the Explain Plan statement does not go through the same code path that the optimizer uses when determining a plan for execution. One of the simplest examples of this behavior is the case where bind variables are used in a statement. Explain plan ignores them while the optimizer uses them to determine the plan. Here’s an example.

SQL> @test_bind
SQL> var how_many number
SQL> accept col1 -
>        prompt 'Enter value for col1: '
SQL>
SQL> DECLARE
2  x number;
3  BEGIN
4
5  :how_many := 0;
6  x := &col1;
7
8  select avg(pk_col) into :how_many
9  from kso.skew
10  where col1 = x;
11
12  dbms_output.put_line(trunc(:how_many)||' records.');
13
14  END;
15  /
16487500 records.
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> undef col1
SQL> undef how_many
SQL>
SQL> @find_sql
Enter value for sql_text: select avg(pk_col) from kso.skew where col1 =%
Enter value for sql_id:
 
SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ -----------------------------------------
7rf1jw8jsw0v7      0  568322376          1         48.32      173,734 SELECT AVG(PK_COL) FROM KSO.SKEW WHERE COL1 = :B1
 
SQL> -- Only 1 plan for this statement
SQL>
SQL>@dplan
Enter value for sql_id: 7rf1jw8jsw0v7
Enter value for child_no: 0
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7rf1jw8jsw0v7, child number 0
-------------------------------------
SELECT AVG(PK_COL) FROM KSO.SKEW WHERE COL1 = :B1
 
Plan hash value: 568322376
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       | 31720 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| SKEW |  3150K|    33M| 31720  (37)| 00:00:43 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("COL1"=:B1)
 
19 rows selected.
 
SQL> -- It did a full table scan expecting 3M rows
SQL>-- Now lets see what Explain Plan thinks it should have done
SQL>
SQL> explain plan for select avg(pk_col) from kso.skew where col1 = :b1;
 
Explained.
 
SQL> @xplan
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    11 |    53   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    53 |   583 |    53   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    54 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
1 - SEL$1
2 - SEL$1 / SKEW@SEL$1
3 - SEL$1 / SKEW@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
3 - access("COL1"=TO_NUMBER(:B1))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
1 - (#keys=0) AVG("PK_COL")[22]
2 - "PK_COL"[NUMBER,22]
3 - "SKEW".ROWID[ROWID,10]
 
29 rows selected.
 
SQL> -- It thinks it will use an index (and that it will get only 54 rows - even though the histogram knows better
SQL>
SQL> select count(*) from kso.skew where col1=1;
 
COUNT(*)
----------
3199971

10g has a very nice utility (DBMS_XPLAN) to display plans from various locations (AWR, V$SQL_PLAN and Plan Tables). It has been expanded in 11g to work with Plan Baselines as well. So stop using Explain Plan and start looking at the real plan that the optimizer comes up with.

  • Just execute the statement (it’s Ok to hit control-C if it’s a long running statement, it will still be in the shared pool)
  • Find the SQL ID for the statement (I use find_sql.sql for this step)
  • Then use dbms_xplan.display_cursor to see the plan (I use dplan.sql for this this step).

There is are a couple of great posts by Rob Van Wijk and Jonathan Lewis on DBMS_XPLAN.DISPLAY_CURSOR on their blogs. Also the Oracle Optimizer Development Group Blog has a good post on the subject which includes 11g options.

7 Comments

  1. [...] заметку Керри Осборна с характерным названием Explain Plan Lies), что EXPLAIN PLAN – это инструмент только для [...]

  2. Autotrace Lies Too! (because it uses Explain Plan) See this post for details.

  3. [...] » Explain Plan Lies – Kerry Osborne’s Oracle Blog kerryosborne.oracle-guy.com/2008/10/explain-plan-lies – view page – cached Oracle’s explain plan utility doesn’t always give the correct answers for several reasons. This post shows an example and has a couple of scripts tha Filter tweets [...]

  4. Balakrishna says:

    Hi Kerry,

    One of your scirpt is giving me error and sql text is little huge , do you have any other version of the below ?
    SQL> @find_sql_awr.sql
    Enter value for sql_text: ae.event_id
    Enter value for sql_id:

    where dbms_lob.substr(sql_text,3999,1) like nvl(‘ae.event_id’,dbms_lob.substr(sql_text,3999,1))
    *
    ERROR at line 4:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 1

    Regards
    Bala

  5. Somu says:

    This should be due to bind peeking. You might be correct in stating that the code path is different as for explain plan the values of the bind variables are not peeked.

  6. TOAD Ambulance Button (and other rants)…

    I’ve recently spent quite a while working in a performance team that supported Production performance incidents and worked with developers on application performance before the code hit Production. Whenever a developer asked me to look at th…

  7. [...] check out a related article by Kerry Osborne Related PostsSecret Hacking Session: Find Out How Oracle SQL Plans Are…What the heck is the [...]

Leave a Reply