Are we getting different plan by using bind variable for an SQL_ID?

I wonder why are we getting different plan for an SQL_ID while using bind variable. I have verified first plan is going for a full table scan and second plan is going for an index read instead of Full table scan.

Did Optimizer played a bad game again? No... Please do not blame optimizer every time J. There is a chance of skewed distribution of values in predicate column .

If Index histogram of the predicate column has an accurate information about the skewed distribution, we will get different plan sometime with respect to the predicate which we given.

How it will work ?

Adaptive Cursor is playing a major role for this scenario.

This feature will decide , if plan has to be  recalculated when the bind variable values changes. Who is helping to  make a good plan with respect to the bind variable value?

of course, it is Index Histogram , we should have accurate index histogram details otherwise they cannot make a decision for recalculation(Wrong Estimation of Cardinality) and again it will end up with bad performance

by using old cached execution plan.

Let me give a quick and simple test.

We have a table called JOB_MASTER and which is having two column named JOB_ID, JOB_CODE

SQL> DESC JOB_MASTER;

Name                                      Null?    Type

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

JOB_ID                                             NUMBER(10)

JOB_CODE                                           VARCHAR2(10)

And there is also a non-unique index in the JOB_CODE column.

Please see the query passing from application side against this table.

SQL_ID: cyt7c564y3nf6

SQL_TEXT :

select max(JOB_ID) from JOB_MASTER where JOB_CODE=:B1;

I checked AWR history of this query and found we are getting different plan for this particular SQL for most of the time.

PLAN_HASH_VALUE SQL_ID        CHILD_NUMBER

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

     4293876030 cyt7c564y3nf6            0

     1405390124 cyt7c564y3nf6            1

              

Plan with Table Scan :

Execution Plan

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

Plan hash value: 1405390124

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

-

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

|

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

-

|   0 | SELECT STATEMENT   |            |     1 |     7 |  3923   (3)| 00:00:48

|

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

|

|*  2 |   TABLE ACCESS FULL| JOB_MASTER |  4196K|    28M|  3923   (3)| 00:00:48     =====> Full Table Scan

|

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

3 - access("JOB_CODE"='DBA') 

              

                

Second Plan with Index Read:

Execution Plan

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

Plan hash value: 4293876030

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

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

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

|   0 | SELECT STATEMENT             |            |     1 |     7 |    16   (0)| 00:00:01 |

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

|   2 |   TABLE ACCESS BY INDEX ROWID| JOB_MASTER |  2311 | 16177 |    16   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | TEST_INDX  |  2383 |       |     7   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - access("JOB_CODE"='DEVELOPER')

     

Why do we get this different plan, is this column having Skewed distribution of values?

Let me have a look,

SQL> select count(*),JOB_CODE from JOB_MASTER group by JOB_CODE;

  COUNT(*) JOB_CODE

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

   8388608 DBA

      4608 DEVELOPER

                

                 

                 

Then we found its highly skewed values in the column JOB_CODE and we can see very less percentage of rows having "DEVELOPER" compared to "DBA" value in this column

There is a catch, It’s a thumb rule oracle following , Oracle will decide to go with full table scan when the bind variable (:B1) is "DBA" and will decide to go with index read

when bind variable (:B1) is "DEVELOPER"

It is really costly when oracle use full table scan while access predicate is "DEVELOPER" and on the other hand , when the access predicate is "DBA", index read is useless.

If the plan need to be recalculated by this way, cursor denoted as Bind Sensitive.

SQL> select plan_hash_value,sql_id,child_number,IS_BIND_SENSITIVE from gv$sql where sql_id='cyt7c564y3nf6';

PLAN_HASH_VALUE SQL_ID        CHILD_NUMBER I

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

     4293876030 cyt7c564y3nf6            0 Y

My request is , please do not scare, if we get multiple plan while using bind variable until we get a performance degradation over there. Please make sure to test SQL Profile couple of time to verify the performance improvement before getting to default category(Live).

Thanks,

Jyothish B