Kerry Osborne's Oracle Blog

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.