Oracle

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 [email protected]> show parameter cursor_sharing
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
17:01:59 [email protected]> 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 [email protected]> 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 [email protected]> 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 [email protected]> select c2,count(1) from t group by c2;
        C2   COUNT(1)
---------- ----------
         1       9900
        99        100
Elapsed: 00:00:00.01
17:02:19 [email protected]> CREATE INDEX INDX_T_C2 ON T(C2);
Index created.
Elapsed: 00:00:00.04
17:02:20 [email protected]> 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 [email protected]> 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 [email protected]> 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 [email protected]> VARIABLE N1 NUMBER
17:02:26 [email protected]> EXEC :N1:=1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
17:02:26 [email protected]> 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 [email protected]> 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 [email protected]> VARIABLE N1 NUMBER
17:03:08 [email protected]> EXEC :N1:=2

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
17:03:08 [email protected]>
17:03:08 [email protected]> 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 [email protected]>
17:03:09 [email protected]> 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 [email protected]> VARIABLE N1 NUMBER
17:03:22 [email protected]> EXEC :N1:=99
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
17:03:22 [email protected]>
17:03:22 [email protected]> 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 [email protected]> 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 [email protected]> 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 [email protected]> 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 [email protected]>
17:06:14 [email protected]>
17:06:14 AN[email protected]> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.15
17:06:24 [email protected]>
17:06:24 [email protected]> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.75
17:06:26 [email protected]>
17:06:26 [email protected]>
17:06:26 [email protected]> VARIABLE N1 NUMBER
17:07:01 [email protected]> EXEC :N1:=1

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
17:07:01 [email protected]>
17:07:01 [email protected]> 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 [email protected]>
17:07:06 [email protected]>
17:07:06 [email protected]> 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 [email protected]> VARIABLE N1 NUMBER
17:07:22 [email protected]> EXEC :N1:=2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
17:07:22 [email protected]>
17:07:22 [email protected]> 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 [email protected]> 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 [email protected]> VARIABLE N1 NUMBER
17:07:34 [email protected]> EXEC :N1:=99
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
17:07:34 [email protected]>
17:07:34 [email protected]> 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 [email protected]>
17:07:34 [email protected]>
17:07:34 [email protected]> 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 [email protected]> 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 [email protected]>

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 [email protected]> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.4';
Session altered.
20:45:52 [email protected]> 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 [email protected]>
20:46:04 [email protected]> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.25
20:46:04 [email protected]>
20:46:04 [email protected]> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.78
20:46:06 [email protected]>
20:46:06 [email protected]>
20:46:06 [email protected]> VARIABLE N1 NUMBER
20:46:18 [email protected]> EXEC :N1:=1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
20:46:18 [email protected]>
20:46:18 [email protected]> 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 [email protected]>
20:46:24 [email protected]>
20:46:24 [email protected]> 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 [email protected]> VARIABLE N1 NUMBER
20:46:26 [email protected]> EXEC :N1:=2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
20:46:26 [email protected]>
20:46:26 [email protected]> 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 [email protected]>
20:46:26 [email protected]>
20:46:26 [email protected]> 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 [email protected]> VARIABLE N1 NUMBER
20:46:26 [email protected]> EXEC :N1:=99
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
20:46:26 [email protected]>
20:46:26 [email protected]> 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 [email protected]> 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 [email protected]> 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 [email protected]> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.07
21:14:47 [email protected]> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.71
21:14:49 [email protected]> VARIABLE N1 NUMBER
21:14:50 [email protected]> EXEC :N1:=99
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
21:14:50 [email protected]> 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 [email protected]> 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 [email protected]> VARIABLE N1 NUMBER
21:14:57 [email protected]> EXEC :N1:=2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
21:14:58 [email protected]> 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 [email protected]> 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 [email protected]> VARIABLE N1 NUMBER
21:15:04 [email protected]> EXEC :N1:=1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
21:15:04 [email protected]> 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 [email protected]> 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 [email protected]> 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

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