Same SQL_ID with Different Execution Plans

Few days back i had a discussion with one of my team members regarding same sql_id having different execution plans.So, it was time to login and test myself :)

Oracle Database Version –> 11.2.0.1.0

17:01:58 ANAND@MATRIX> show parameter cursor_sharing

NAME                                 TYPE        VALUE                                                                                                                    
------------------------------------ ----------- ------------------------------                                                                                           
cursor_sharing                       string      EXACT                                                                                                                    
17:01:59 ANAND@MATRIX> show parameter optimizer

NAME                                 TYPE        VALUE                                                                                                                    
------------------------------------ ----------- ------------------------------                                                                                           
optimizer_capture_sql_plan_baselines boolean     FALSE                                                                                                                    
optimizer_dynamic_sampling           integer     2                                                                                                                        
optimizer_features_enable            string      11.2.0.1                                                                                                                 
optimizer_index_caching              integer     0                                                                                                                        
optimizer_index_cost_adj             integer     100                                                                                                                      
optimizer_mode                       string      ALL_ROWS                                                                                                                 
optimizer_secure_view_merging        boolean     TRUE                                                                                                                     
optimizer_use_invisible_indexes      boolean     FALSE                                                                                                                    
optimizer_use_pending_statistics     boolean     FALSE                                                                                                                    
optimizer_use_sql_plan_baselines     boolean     TRUE                        
17:02:00 ANAND@MATRIX> CREATE TABLE T (
17:02:01   2    C1 NUMBER,
17:02:01   3    C2 NUMBER,
17:02:01   4    C3 VARCHAR2(100));

Table created.

Elapsed: 00:00:01.03
17:02:03 ANAND@MATRIX> INSERT INTO
17:02:04   2    T
17:02:04   3  SELECT
17:02:05   4    *
17:02:07   5  FROM
17:02:08   6    (SELECT
17:02:09   7      ROWNUM C1,
17:02:10   8      DECODE(MOD(ROWNUM,100),99,99,1) C2,
17:02:12   9      RPAD('A',100,'A') C3
17:02:13  10    FROM
17:02:14  11      DUAL
17:02:14  12    CONNECT BY
17:02:15  13      LEVEL  commit;

Commit complete.

Elapsed: 00:00:00.00
17:02:18 ANAND@MATRIX> select c2,count(1) from t group by c2;

        C2   COUNT(1)                                                                                                                                                     
---------- ----------                                                                                                                                                     
         1       9900                                                                                                                                                     
        99        100                                                                                                                                                     

Elapsed: 00:00:00.01
17:02:19 ANAND@MATRIX> CREATE INDEX INDX_T_C2 ON T(C2);

Index created.

Elapsed: 00:00:00.04
17:02:20 ANAND@MATRIX> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.57
17:02:22 ANAND@MATRIX> select column_name,endpoint_number,endpoint_value from user_tab_histograms where table_name='T' and column_name ='C2';

COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE                                                                                                                       
-------------------- --------------- --------------                                                                                                                       
C2                                 0              1                                                                                                                       
C2                                 1             99                                                                                                                       

Elapsed: 00:00:00.15
17:02:24 ANAND@MATRIX> select column_name, density, histogram from user_tab_col_statistics where table_name='T' and column_name='C2';

COLUMN_NAME             DENSITY HISTOGRAM                                                                                                                                 
-------------------- ---------- ---------------                                                                                                                           
C2                           .5 NONE                                                                                                                                      

The table T is having 3 columns of which C2 will be used in the where clause of the sql.Currently, no histograms is generated for the columns.The column C2 is having 2 distinct values 1 and 99, which has been shown above.

17:02:24 ANAND@MATRIX> VARIABLE N1 NUMBER
17:02:26 ANAND@MATRIX> EXEC :N1:=1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
17:02:26 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
17:02:27   2    C1,
17:02:28   3    C2
17:02:29   4  FROM
17:02:29   5    T
17:02:30   6  WHERE
17:02:31   7    C2 = :N1;

        C1         C2                                                                                                                                                     
---------- ----------                                                                                                                                                     
       194          1                                                                                                                                                     
       195          1               
       .................
       .................
9900 rows selected.

Elapsed: 00:00:05.34
17:02:37 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1601196873                                                                                                                                               
                                                                                                                                                                          
------------------------------------------------------------------------------------                                                                                      
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                      
------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT  |      |      1 |        |   9900 |00:00:00.07 |     853 |                                                                                      
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |   9900 |00:00:00.07 |     853 |                                                                                      
------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   1 - filter("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

19 rows selected.
17:02:59 ANAND@MATRIX> VARIABLE N1 NUMBER
17:03:08 ANAND@MATRIX> EXEC :N1:=2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
17:03:08 ANAND@MATRIX> 
17:03:08 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
17:03:08   2    C1,
17:03:08   3    C2
17:03:08   4  FROM
17:03:08   5    T
17:03:08   6  WHERE
17:03:08   7    C2 = :N1;

no rows selected

Elapsed: 00:00:00.01
17:03:09 ANAND@MATRIX> 
17:03:09 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1601196873                                                                                                                                               
                                                                                                                                                                          
------------------------------------------------------------------------------------                                                                                      
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                      
------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT  |      |      1 |        |      0 |00:00:00.01 |     203 |                                                                                      
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |      0 |00:00:00.01 |     203 |                                                                                      
------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   1 - filter("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

19 rows selected.

Elapsed: 00:00:00.12
17:03:16 ANAND@MATRIX> VARIABLE N1 NUMBER
17:03:22 ANAND@MATRIX> EXEC :N1:=99

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
17:03:22 ANAND@MATRIX> 
17:03:22 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
17:03:23   2    C1,
17:03:23   3    C2
17:03:23   4  FROM
17:03:23   5    T
17:03:23   6  WHERE
17:03:23   7    C2 = :N1;

        C1         C2                                                                                                                                                     
---------- ----------                                                                                                                                                     
      6499         99                                                                                                                                                     
      6599         99                                                                                                                                                     
      ...................
100 rows selected.

Elapsed: 00:00:00.18
17:03:23 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                         --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 1                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1601196873                                                                                                                                               
                                                                                                                                                                          
------------------------------------------------------------------------------------                                                                                      
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                      
------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT  |      |      1 |        |    100 |00:00:00.01 |     210 |                                                                                      
|*  1 |  TABLE ACCESS FULL| T    |      1 |   5000 |    100 |00:00:00.01 |     210 |                                                                                      
------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   1 - filter("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

19 rows selected.
17:03:54 ANAND@MATRIX> select
17:04:46   2   sql_id
17:04:46   3   ,plan_hash_value
17:04:46   4   , child_number
17:04:46   5   , executions
17:04:46   6   , parse_calls
17:04:46   7   , buffer_gets
17:04:46   8   , is_bind_sensitive
17:04:46   9   , is_bind_aware
17:04:46  10   from
17:04:46  11   v$sql
17:04:46  12   where
17:04:46  13   sql_id = '3wy8vdgf80ysw';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I                                                                                         
------------- --------------- ------------ ---------- ----------- ----------- - -                                                                                         
3wy8vdgf80ysw      1601196873            0          2           3        1056 Y N                                                                                         
3wy8vdgf80ysw      1601196873            1          1           0         210 Y Y                                                                                         

From Oracle Doc
================

IS_BIND_SENSITIVE VARCHAR2(1) Indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivities and where a change in a bind variable value may cause the optimizer to generate a different plan.

IS_BIND_AWARE VARCHAR2(1) Indicates whether the cursor is bind aware (Y) or not (N). A query is considered bind-aware if it has been marked to use extended cursor sharing. The query would already have been marked as bind-sensitive.

What are the changes with Histogram on the column C2, which is used in the where clause on the sql.

17:05:58 ANAND@MATRIX> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
17:06:13 ANAND@MATRIX> 
17:06:14 ANAND@MATRIX> 
17:06:14 ANAND@MATRIX> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.15
17:06:24 ANAND@MATRIX> 
17:06:24 ANAND@MATRIX> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.75
17:06:26 ANAND@MATRIX> 
17:06:26 ANAND@MATRIX> 
17:06:26 ANAND@MATRIX> VARIABLE N1 NUMBER
17:07:01 ANAND@MATRIX> EXEC :N1:=1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
17:07:01 ANAND@MATRIX> 
17:07:01 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
17:07:01   2    C1,
17:07:01   3    C2
17:07:01   4  FROM
17:07:01   5    T
17:07:01   6  WHERE
17:07:01   7    C2 = :N1;

        C1         C2                                                                                                                                                     
---------- ----------                                                                                                                                                     
       194          1                                                                                                                                                     
       195          1                                                                                                                                                     
      ...............
9900 rows selected.

Elapsed: 00:00:05.78
17:07:06 ANAND@MATRIX> 
17:07:06 ANAND@MATRIX> 
17:07:06 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1601196873                                                                                                                                               
                                                                                                                                                                          
---------------------------------------------------------------------------------------------                                                                             
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |                                                                             
---------------------------------------------------------------------------------------------                                                                             
|   0 | SELECT STATEMENT  |      |      1 |        |   9900 |00:00:01.29 |     853 |    200 |                                                                             
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |   9900 |00:00:01.29 |     853 |    200 |                                                                             
---------------------------------------------------------------------------------------------                                                                             
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   1 - filter("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

19 rows selected.

Elapsed: 00:00:01.82

In the above plan, as the histogram details in present on the column C2, the optimizer correctly knows that there are 9900 rows for value 1 and the same is reflected in E-rows.

17:07:09 ANAND@MATRIX> VARIABLE N1 NUMBER
17:07:22 ANAND@MATRIX> EXEC :N1:=2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
17:07:22 ANAND@MATRIX> 
17:07:22 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
17:07:22   2    C1,
17:07:22   3    C2
17:07:22   4  FROM
17:07:22   5    T
17:07:22   6  WHERE
17:07:22   7    C2 = :N1;

no rows selected

Elapsed: 00:00:00.01
17:07:22 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1601196873                                                                                                                                               
                                                                                                                                                                          
------------------------------------------------------------------------------------                                                                                      
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                      
------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT  |      |      1 |        |      0 |00:00:00.01 |     203 |                                                                                      
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |      0 |00:00:00.01 |     203 |                                                                                      
------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   1 - filter("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

19 rows selected.
17:07:22 ANAND@MATRIX> VARIABLE N1 NUMBER
17:07:34 ANAND@MATRIX> EXEC :N1:=99

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
17:07:34 ANAND@MATRIX> 
17:07:34 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
17:07:34   2    C1,
17:07:34   3    C2
17:07:34   4  FROM
17:07:34   5    T
17:07:34   6  WHERE
17:07:34   7    C2 = :N1;

        C1         C2                                                                                                                                                     
---------- ----------                                                                                                                                                     
      6499         99                                                                                                                                                     
       .................                                                                                                          

100 rows selected.

Elapsed: 00:00:00.20
17:07:34 ANAND@MATRIX> 
17:07:34 ANAND@MATRIX> 
17:07:34 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 1                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1487524476                                                                                                                                               
                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------                                                              
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |                                                              
------------------------------------------------------------------------------------------------------------                                                              
|   0 | SELECT STATEMENT            |           |      1 |        |    100 |00:00:00.01 |      19 |      9 |                                                              
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |      1 |    100 |    100 |00:00:00.01 |      19 |      9 |                                                              
|*  2 |   INDEX RANGE SCAN          | INDX_T_C2 |      1 |    100 |    100 |00:00:00.02 |       9 |      9 |                                                              
------------------------------------------------------------------------------------------------------------                                                              
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   2 - access("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

20 rows selected.
17:07:34 ANAND@MATRIX> select
17:07:44   2   sql_id
17:07:44   3   , child_number
17:07:44   4   , executions
17:07:44   5   , parse_calls
17:07:44   6   , buffer_gets
17:07:44   7   , is_bind_sensitive
17:07:44   8   , is_bind_aware
17:07:44   9   from
17:07:44  10   v$sql
17:07:44  11   where
17:07:44  12   sql_id = '3wy8vdgf80ysw';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- --------------- ------------ ---------- ----------- ----------- - -
3wy8vdgf80ysw      1601196873            0          2           3        1007 Y N
3wy8vdgf80ysw      1487524476            1          1           0          19 Y Y

Elapsed: 00:00:00.03
17:07:49 ANAND@MATRIX> 

So, we can have different execution plans and different plan hash value for the same sql_ids.So, if the execution plan changes the plan hash value will also change.From Oracle 11g we have Adaptive Cursor Sharing (ACS) which allows the server to compare the effectiveness of execution plans between executions with different bind variable values and if notices suboptimal plans,allows certain bind variable values, to use different execution plans for the same statement hence creating a new child cursor.

A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for. This cursor was marked bind sensitive because the histogram on the C2 column was used to compute the selectivity of the predicate “where C2 = :N1″. Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans.

Lets play by setting OPTIMIZER_FEATURES_ENABLE=’10.2.0.4′ and see what happens.Here, the the column C2 is already having frequency based histogram.

20:43:54 ANAND@MATRIX> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.4';

Session altered.

20:45:52 ANAND@MATRIX> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
20:46:04 ANAND@MATRIX> 
20:46:04 ANAND@MATRIX> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.25
20:46:04 ANAND@MATRIX> 
20:46:04 ANAND@MATRIX> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.78
20:46:06 ANAND@MATRIX> 
20:46:06 ANAND@MATRIX> 
20:46:06 ANAND@MATRIX> VARIABLE N1 NUMBER
20:46:18 ANAND@MATRIX> EXEC :N1:=1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
20:46:18 ANAND@MATRIX> 
20:46:18 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
20:46:18   2    C1,
20:46:18   3    C2
20:46:18   4  FROM
20:46:18   5    T
20:46:18   6  WHERE
20:46:18   7    C2 = :N1;

        C1         C2                                                                                                                                                     
---------- ----------                                                                                                                                                     
         1          1                                    
         ..............
      7691          1                                                                                                                                                     
      7692          1                                                                                                                                                     

9900 rows selected.

Elapsed: 00:00:05.42
20:46:24 ANAND@MATRIX> 
20:46:24 ANAND@MATRIX> 
20:46:24 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1601196873                                                                                                                                               
                                                                                                                                                                          
---------------------------------------------------------------------------------------------                                                                             
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |                                                                             
---------------------------------------------------------------------------------------------                                                                             
|   0 | SELECT STATEMENT  |      |      1 |        |   9900 |00:00:02.82 |     852 |    200 |                                                                             
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |   9900 |00:00:02.82 |     852 |    200 |                                                                             
---------------------------------------------------------------------------------------------                                                                             
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   1 - filter("C2"=:N1)                                                                                               
20:46:26 ANAND@MATRIX> VARIABLE N1 NUMBER
20:46:26 ANAND@MATRIX> EXEC :N1:=2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
20:46:26 ANAND@MATRIX> 
20:46:26 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
20:46:26   2    C1,
20:46:26   3    C2
20:46:26   4  FROM
20:46:26   5    T
20:46:26   6  WHERE
20:46:26   7    C2 = :N1;

no rows selected

Elapsed: 00:00:00.01
20:46:26 ANAND@MATRIX> 
20:46:26 ANAND@MATRIX> 
20:46:26 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1601196873                                                                                                                                               
                                                                                                                                                                          
------------------------------------------------------------------------------------                                                                                      
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                      
------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT  |      |      1 |        |      0 |00:00:00.01 |     203 |                                                                                      
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |      0 |00:00:00.01 |     203 |                                                                                      
------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   1 - filter("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

19 rows selected.
20:46:26 ANAND@MATRIX> VARIABLE N1 NUMBER
20:46:26 ANAND@MATRIX> EXEC :N1:=99

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
20:46:26 ANAND@MATRIX> 
20:46:26 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
20:46:26   2    C1,
20:46:26   3    C2
20:46:26   4  FROM
20:46:26   5    T
20:46:26   6  WHERE
20:46:26   7    C2 = :N1;

        C1         C2                                                                                                                                                     
---------- ----------                                                                                                                                                     
      6499         99                                                                                                                                                     
      6599         99      
      ...................
20:46:26 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1601196873                                                                                                                                               
                                                                                                                                                                          
------------------------------------------------------------------------------------                                                                                      
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                      
------------------------------------------------------------------------------------                                                                                      
|   0 | SELECT STATEMENT  |      |      1 |        |    100 |00:00:00.01 |     210 |                                                                                      
|*  1 |  TABLE ACCESS FULL| T    |      1 |   9900 |    100 |00:00:00.01 |     210 |                                                                                      
------------------------------------------------------------------------------------                                                                                      
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   1 - filter("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

19 rows selected.
20:46:34 ANAND@MATRIX> select
20:46:42   2   sql_id
20:46:42   3   , child_number
20:46:42   4   , executions
20:46:42   5   , parse_calls
20:46:42   6   , buffer_gets
20:46:42   7   , is_bind_sensitive
20:46:42   8   , is_bind_aware
20:46:42   9   from
20:46:42  10   v$sql
20:46:42  11   where
20:46:42  12   sql_id = '3wy8vdgf80ysw';

SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I                                                                                                         
------------- ------------ ---------- ----------- ----------- - -                                                                                                         
3wy8vdgf80ysw            0          3           3        1317 N N     

Interesting to note,the execution plan showing “TABLE ACCESS FULL” even for value 99, which showed “INDEX RANGE INDEX” with OPTIMIZER_FEATURES_ENABLE=’11.2.0.1′.

What happens if i change the pattern.

21:14:42 ANAND@MATRIX> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.07
21:14:47 ANAND@MATRIX> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.71
21:14:49 ANAND@MATRIX> VARIABLE N1 NUMBER
21:14:50 ANAND@MATRIX> EXEC :N1:=99

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
21:14:50 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
21:14:51   2    C1,
21:14:51   3    C2
21:14:52   4  FROM
21:14:52   5    T
21:14:53   6  WHERE
21:14:53   7    C2 = :N1;

        C1         C2                                                                                                                                                     
---------- ----------                                                                                                                                                     
      6499         99                                                                                                                                                     
      6399         99                                                                                                                                                     

100 rows selected.

Elapsed: 00:00:00.34
21:14:54 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1487524476                                                                                                                                               
                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------                                                              
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |                                                              
------------------------------------------------------------------------------------------------------------                                                              
|   0 | SELECT STATEMENT            |           |      1 |        |    100 |00:00:00.02 |      19 |      5 |                                                              
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |      1 |    100 |    100 |00:00:00.02 |      19 |      5 |                                                              
|*  2 |   INDEX RANGE SCAN          | INDX_T_C2 |      1 |    100 |    100 |00:00:00.02 |       9 |      2 |                                                              
------------------------------------------------------------------------------------------------------------                                                              
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   2 - access("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

20 rows selected.

The execution plans looks good as it used index INDX_T_C2 to select 100 rows from table.For selecting 9900 rows the optimizer should do a full table scan.

21:14:56 ANAND@MATRIX> VARIABLE N1 NUMBER
21:14:57 ANAND@MATRIX> EXEC :N1:=2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
21:14:58 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
21:14:59   2    C1,
21:14:59   3    C2
21:15:00   4  FROM
21:15:00   5    T
21:15:01   6  WHERE
21:15:02   7    C2 = :N1;

no rows selected

Elapsed: 00:00:00.01
21:15:02 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1487524476                                                                                                                                               
                                                                                                                                                                          
---------------------------------------------------------------------------------------------------                                                                       
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                       
---------------------------------------------------------------------------------------------------                                                                       
|   0 | SELECT STATEMENT            |           |      1 |        |      0 |00:00:00.01 |       2 |                                                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |      1 |    100 |      0 |00:00:00.01 |       2 |                                                                       
|*  2 |   INDEX RANGE SCAN          | INDX_T_C2 |      1 |    100 |      0 |00:00:00.01 |       2 |                                                                       
---------------------------------------------------------------------------------------------------                                                                       
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   2 - access("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

20 rows selected.

Elapsed: 00:00:00.12
21:15:03 ANAND@MATRIX> VARIABLE N1 NUMBER
21:15:04 ANAND@MATRIX> EXEC :N1:=1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
21:15:04 ANAND@MATRIX> SELECT /*+ GATHER_PLAN_STATISTICS */
21:15:04   2    C1,
21:15:05   3    C2
21:15:05   4  FROM
21:15:06   5    T
21:15:07   6  WHERE
21:15:07   7    C2 = :N1;

        C1         C2                                                                                                                                                     
---------- ----------                                                                                                                                                     
         1          1                                                                                                                                                     
         2          1                                                                                                                                                     
      7369          1                                                                                                                                                     

9900 rows selected.

Elapsed: 00:00:05.70
21:15:13 ANAND@MATRIX> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wy8vdgf80ysw, child number 0                                                                                                                                     
-------------------------------------                                                                                                                                     
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2 FROM   T WHERE   C2 =                                                                                                     
:N1                                                                                                                                                                       
                                                                                                                                                                          
Plan hash value: 1487524476                                                                                                                                               
                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------                                                              
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |                                                              
------------------------------------------------------------------------------------------------------------                                                              
|   0 | SELECT STATEMENT            |           |      1 |        |   9900 |00:00:05.59 |    1486 |    173 |                                                              
|   1 |  TABLE ACCESS BY INDEX ROWID| T         |      1 |    100 |   9900 |00:00:05.59 |    1486 |    173 |                                                              
|*  2 |   INDEX RANGE SCAN          | INDX_T_C2 |      1 |    100 |   9900 |00:00:00.03 |     681 |     19 |                                                              
------------------------------------------------------------------------------------------------------------                                                              
                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                       
---------------------------------------------------                                                                                                                       
                                                                                                                                                                          
   2 - access("C2"=:N1)                                                                                                                                                   
                                                                                                                                                                          

20 rows selected.

Elapsed: 00:00:00.12

For N1=1 which selects 9900 rows still the execution plan shows “INDEX RANGE SCAN”.

21:15:23 ANAND@MATRIX> select
21:22:39   2   sql_id
21:22:39   3   ,plan_hash_value
21:22:39   4   , child_number
21:22:39   5   , executions
21:22:39   6   , parse_calls
21:22:39   7   , buffer_gets
21:22:39   8   , is_bind_sensitive
21:22:39   9   , is_bind_aware
21:22:39  10   from
21:22:39  11   v$sql
21:22:39  12   where
21:22:39  13   sql_id = '3wy8vdgf80ysw';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- --------------- ------------ ---------- ----------- ----------- - -
3wy8vdgf80ysw      1487524476            0          3           3        1559 N N

References and must read-

http://oracle-randolf.blogspot.com/2011/01/adaptive-cursor-sharing.html

http://hoopercharles.wordpress.com/2011/01/29/histograms-and-bind-variables-but-why/

http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html

http://optimizermagic.blogspot.com/2010/03/explain-adaptive-cursor-sharing.html

About these ads

2 thoughts on “Same SQL_ID with Different Execution Plans

    1. Hi,

      Thanks for visiting the blog and your inputs.Yes, i agree with you,that different (non-default) optimizer setting can also lead to new plans.

      Regards,
      Anand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s