Why Explain plan lies - Part I

 

When it comes to SQL Tuning, very often we need to look at the execution plan to determine where the majority of time being spent. But how we generate that execution plan can have a big impact on whether or not the plan we are looking at is really the plan being used.

 

The two common methods for generating the execution plans are

 

·         Explain plan command (or) set autotrace traceonly explain: this command displays the execution plan for SQL statement without actually executing the statement.

 

·         V$SQL_Plan: dynamic performance view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor and stored in the library cache.

 

The preferred method is always to go with V$SQL_PLAN, but under few circumstances the plan shown by the EXPLAIN PLAN command can be different from the plan that will actually be used when the query is executed.

 

So what can cause the plans to differ?

 

Bind Variables:

 

When a sql statement contains bind variables, the plan shown using explain plan is not aware of bind variables, while the plan show using V$SQL_Plan takes the bind variable values into account for plan generation process. To put it simple “Explain plans are blind to bind variables“

 

 

demo@ORA12C> create table t

  2  as

  3  select a.*,

  4     decode(rownum,1,1,99) x

  5  from all_objects a;

 

Table created.

 

demo@ORA12C> create index t_idx on t(x);

 

Index created.

 

demo@ORA12C> begin

  2     dbms_stats.gather_table_stats(

  3             ownname=> user,

  4             tabname=> 'T',

  5             options=>'GATHER AUTO',

  6             method_opt=> 'for columns X size 2 ');

  7  end;

  8  /

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select num_distinct,num_nulls,histogram

  2  from user_tab_col_statistics

  3  where table_name ='T'

  4  and column_name ='X';

 

NUM_DISTINCT  NUM_NULLS HISTOGRAM

------------ ---------- ---------------

           2          0 FREQUENCY

 

demo@ORA12C> set serveroutput off

demo@ORA12C> alter session set statistics_level=all;

 

Session altered.

 

demo@ORA12C> variable n number

demo@ORA12C> exec :n := 1;

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select max( object_id ) from t where x = :n;

 

MAX(OBJECT_ID)

--------------

            16

 

demo@ORA12C> select * from table( dbms_xplan.display_cursor(format=>'allstats last') );

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------

SQL_ID  30x88cwmw09qd, child number 1

-------------------------------------

select max( object_id ) from t where x = :n

 

Plan hash value: 1339972470

 

---------------------------------------------------------------------------------

| Id  | Operation                            | Name  | Starts | E-Rows | A-Rows |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |       |      1 |        |      1 |

|   1 |  SORT AGGREGATE                      |       |      1 |      1 |      1 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |     13 |      1 |

|*  3 |    INDEX RANGE SCAN                  | T_IDX |      1 |     13 |      1 |

---------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("X"=:N)

 

When we query the actual plan used at execution from V$SQL_Plan (via dbms_xplan.display_cursor method) we get an index access plan and the cardinality estimate is close to the actuals.

 

However if we use the Explain plan command for our statement, we get full Table scan – with the estimated cardinality as 36149 rows.

 

demo@ORA12C> explain plan for

  2  select max( object_id ) from t

  3  where x = :n;

 

Explained.

 

demo@ORA12C> select * from table( dbms_xplan.display );

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------

Plan hash value: 2966233522

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     8 |   387  (28)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |

|*  2 |   TABLE ACCESS FULL| T    | 36149 |   282K|   387  (28)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("X"=TO_NUMBER(:N))

 

14 rows selected.

 

The first indication that explain plan is not bind aware can be seen in the “Predicate information” under the plan. There you will see the addition of TO_NUMBER function to our bind variable: N, even though we have declared the variable as number datatype.

 

Since no bind variable peeking the optimizer can’t use the histogram on the column X, therefore the optimizer has to assume the uniform data distribution in the column X and calculates the estimated cardinality as NUM_ROWS / NDV, which is rounded to 36149 rows.

 

demo@ORA12C> select t.num_rows, c.num_distinct,

  2         t.num_rows/c.num_distinct as estimated_rows

  3  from user_tables t,

  4     user_tab_col_statistics c

  5  where t.table_name = c.table_name

  6  and t.table_name ='T'

  7  and c.column_name ='X' ;

 

  NUM_ROWS NUM_DISTINCT ESTIMATED_ROWS

---------- ------------ --------------

     72297            2        36148.5

 

demo@ORA12C>