Bind variable peeking in 10G and 11G

SQL ID with bind variable, when generate or refresh the execution plan, it would use the captured value of bind variable, this captured value of bind variables link to the SQL ID. Even value of bind variable value had been changed when execution plan be generated, it still look back to the captured value of bind variables. And the captured value of bind variable is controlled by  “_cursor_bind_capture_interval” (900 seconds as default )  and re-capture triggered if the last capture time for the SQL ID is “_cursor_bind_capture_interval” .

 Find out the value of captured bind variable :

========== Execute plan with Bind variable in 10g/11g ======================

SQL> select distinct sql_id,  t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
  2   from  v$sql t  join v$sql_bind_capture b  using (sql_id)
  3   where b.value_string is not null and sql_id='4k29quznbg6pa';

SQL_ID        SQL_TEXT                                          PLAN_HASH_VALUE  BIND_NAME
------------- ---------------------------------------------------   ---------------  ------------
BIND_STRING          LAST_CAPTURED        WAS
-------------------   ----------------      --------
4k29quznbg6pa select * from pt1 where created > sysdate - :vdate            1267018005 :VDATE
800                  23-JAN-2013:14:39:54  YES

=== changed :vdate value and captured after _ cursor_bind_capture_interval ==========

 

SQL> r
  1  select distinct sql_id,  t.sql_text SQL_TEXT,  t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
    from  v$sql t  join v$sql_bind_capture b  using (sql_id)
 where b.value_string is not null and sql_id='4k29quznbg6pa'
 
SQL_ID        SQL_TEXT                                          PLAN_HASH_VALUE  BIND_NAME
------------- ---------------------------------------------------   ---------------  ------------
BIND_STRING          LAST_CAPTURED        WAS
-------------------   ----------------      --------
4k29quznbg6pa select * from pt1 where created > sysdate - :vdate            1267018005 :VDATE
900                  23-JAN-2013:15:08:51 YES 

 In ORACLE 10G, for ALL bind variables in “where” clause with ANY operator on the column(s), current captured bind variables would not trigger the new execution plan or plan switch even value(s) had been changed  and existing execution plan might not be optimized. Execution plan switching or re-gen  only control by “invalidation interval” as described above.  Below examples is to show no dependency on the captured value and execution plan.

 === Can find bind data in v$SQL also, but in binary format =====

=== Make two diff SQL ID with add <space> in front, to find out how the existing plan changes ===

 SQL> r

  1  select SQL_ID, OBJECT_STATUS, IS_OBSOLETE, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, BIND_DATA, PLAN_HASH_VALUE  from v$sql
  2* where sql_id in ('4k29quznbg6pa','50msx60hgkxjt')
 
SQL_ID        OBJECT_STATUS       I FIRST_LOAD_TIME       LAST_LOAD_TIME        LAST_ACTIVE_TIME
------------- ------------------- - ----------------      --------------------  --------------------- 
BIND_DATA                                             PLAN_HASH_VALUE      
--------------------------------------------------------   ----------------  
50msx60hgkxjt VALID               N 2013-01-23/12:11:01   2013-01-23/12:11:01   23-JAN-2013:15:05:54                                                    
BEDA0A20030050FF8BCC000101C0021602C20A                2821530121     
 
4k29quznbg6pa VALID               N 2013-01-23/14:39:56   2013-01-23/14:39:56   23-JAN-2013:15:08:54                                                       
BEDA0A20030050FF8C83000101C0021602C20A                1267018005

     

SQL> select * from table(dbms_xplan.display_cursor('50msx60hgkxjt',null,'advanced'));
 PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  50msx60hgkxjt, child number 0
-------------------------------------
select * from pt1 where created > sysdate - :vdate
 
Plan hash value: 2821530121
 
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| PT1      |     1 |    93 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | PT1_IDX1 |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / PT1@SEL$1
   2 - SEL$1 / PT1@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('_complex_view_merging' 'false')
      OPT_PARAM('star_transformation_enabled' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "PT1"@"SEL$1" ("PT1"."CREATED"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
   1 - :VDATE (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CREATED">SYSDATE@!-:VDATE)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "PT1"."OWNER"[VARCHAR2,30], "PT1"."OBJECT_NAME"[VARCHAR2,128],
       "PT1"."SUBOBJECT_NAME"[VARCHAR2,30], "PT1"."OBJECT_ID"[NUMBER,22],
       "PT1"."DATA_OBJECT_ID"[NUMBER,22], "PT1"."OBJECT_TYPE"[VARCHAR2,19],
       "CREATED"[DATE,7], "PT1"."LAST_DDL_TIME"[DATE,7],
       "PT1"."TIMESTAMP"[VARCHAR2,19], "PT1"."STATUS"[VARCHAR2,7],
       "PT1"."TEMPORARY"[VARCHAR2,1], "PT1"."GENERATED"[VARCHAR2,1],
       "PT1"."SECONDARY"[VARCHAR2,1]
   2 - "PT1".ROWID[ROWID,10], "CREATED"[DATE,7]
 
 
57 rows selected.
 
SQL> r
  1* select * from table(dbms_xplan.display_cursor('4k29quznbg6pa',null,'advanced'))
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  4k29quznbg6pa, child number 0
-------------------------------------
 select * from pt1 where created > sysdate - :vdate
 
Plan hash value: 1267018005
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   129 (100)|          |
|*  1 |  TABLE ACCESS FULL| PT1  | 45031 |  4089K|   129   (4)| 00:00:02 |
--------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / PT1@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('_complex_view_merging' 'false')
      OPT_PARAM('star_transformation_enabled' 'true')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "PT1"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :VDATE (NUMBER): 800
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CREATED">SYSDATE@!-:VDATE)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "PT1"."OWNER"[VARCHAR2,30], "PT1"."OBJECT_NAME"[VARCHAR2,128],
       "PT1"."SUBOBJECT_NAME"[VARCHAR2,30], "PT1"."OBJECT_ID"[NUMBER,22],
       "PT1"."DATA_OBJECT_ID"[NUMBER,22], "PT1"."OBJECT_TYPE"[VARCHAR2,19],
       "CREATED"[DATE,7], "PT1"."LAST_DDL_TIME"[DATE,7],
       "PT1"."TIMESTAMP"[VARCHAR2,19], "PT1"."STATUS"[VARCHAR2,7],
       "PT1"."TEMPORARY"[VARCHAR2,1], "PT1"."GENERATED"[VARCHAR2,1],
       "PT1"."SECONDARY"[VARCHAR2,1]
 
 
54 rows selected.

In 10G, after object statistics gathering, when SQL re-run after “invalid interval”, SQL plan would switch to existing plan first and final generate the new version of plan (may just duplicated from history). All the bind variable peeking value in history plan would be updated to currently even execution plan actually generated using old peeking value.

============== bind peeking in 10G with statistics re-gathering =========

   1  select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE

  2* from v$sql where sql_id ='276x596m9384u'
  3  ;
 
SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME     
------------- ---------- ---------- --------------- ------------ - -------------------- 
BIND_DATA                                LAST_LOAD_TIME       I
---------------------------------------- -------------------- -
276x596m9384u         10 2794561690      2734209795            0 N 15-FEB-2013:16:04:27 
BEDA0A200300511DEC0B000101C0021602C102   2013-02-15/11:04:29  N
276x596m9384u         10 2794561690      3214516708            1 N 15-FEB-2013:16:05:51 
BEDA0A200300511DEC5E000101C0021602C102   2013-02-15/11:23:59  N
276x596m9384u          6 2794561690      2734209795            2 N 15-FEB-2013:16:15:36 
BEDA0A200300511DEEA8000101C0021602C102   2013-02-15/11:57:05  N
276x596m9384u          9 2794561690      3214516708            3 N 15-FEB-2013:16:13:42 
BEDA0A200300511DEE36000101C0021602C102   2013-02-15/15:11:50  N
276x596m9384u          2 2794561690      2734209795            4 N 15-FEB-2013:16:09:06 
BEDA0A200300511DED22000101C0021602C102   2013-02-15/15:59:23  N
276x596m9384u          1 2794561690      3214516708            5 N 15-FEB-2013:17:46:38 
BEDA0A200300511E03FE000101C0021602C102   2013-02-15/17:46:39  N
 
6 rows selected.
 
r
  1  select distinct t.CHILD_NUMBER,t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
  2  from  v$sql t , v$sql_bind_capture b
  3  where b.value_string is not null and t.sql_id= '276x596m9384u'
  4*    and t.sql_id=b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER order by b.LAST_CAPTURED
 
CHILD_NUMBER PLAN_HASH_VALUE BIND_NAME  BIND_STRIN LAST_CAPTURED        WAS
------------ --------------- ---------- ---------- -------------------- ---
           0      2734209795 :VID       1          15-FEB-2013:16:04:27 YES
           1      3214516708 :VID       1          15-FEB-2013:16:05:50 YES
           4      2734209795 :VID       1          15-FEB-2013:16:09:06 YES
           3      3214516708 :VID       1          15-FEB-2013:16:13:42 YES
           2      2734209795 :VID       1          15-FEB-2013:16:15:36 YES
           5      3214516708 :VID       1          15-FEB-2013:17:46:38 YES
 
6 rows selected.

 In 11G, Execution plan version processing had description as above, it would not loop back to existing versions/children plan in order to generate new version of plan. When bind variable peeking happens in 11G, 11G had improved the optimizer to handle this. It will identify whether such bind variable peeking is “sensitive” or not. If it is “sensitive”, Optimizer will check the current value of variables and compare with history of “peeking” values to pick up the correct plan or generate new plan. If bind variable is not “sensitive”, it will use last peeking value and execution plan until new version of plan handling be trigger, and at that time, the current value of bind variables will be used to generate new version of execution plan, and existing plan history would not be changed on the bind variables peeking value, the history of plan will remain the “peeking” values.

 Below is the case of  NOT sensitive “Bind Variable Peeking” in 11G (11.2.0.2), The execution plans will not switch or re-gen until trigger (statistics gathering)  for re-gen (not switching) happen.

 Change vtyp  value on the sequence 0, 1,2,0 , re-run the SQL after bind variable changed, and with gather statistics on the object_list, re-run SQL after “ invalid interval”

 Type_ID is the column with stewed data and histogram statistics. When column histogram statistics is not captured, then the execution plan will not changed by bind variable value changed and statistics re-gathered.  Repeated run below in one session , Check version and bind variable values.

 select max(object_id) from object_list where type_id = :vtyp –  1

 SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE,
  2  LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
  3* from v$sql where sql_id='9nhany360wxs1'
 
SQL_ID        EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME
------------- ---------- ---------- -------------- --------------- ------------ - --------------------
BIND_DATA                                LAST_LOAD_TIME                    I I I I
----------------------------------------     --------------------------    - - - -
9nhany360wxs1          2 3423500033     4199617170       599033881            0 N 26-FEB-2013:11:11:41
BEDA0B200100512C27EC000101C002160180            2013-02-26/10:48:39           N N N Y
 
9nhany360wxs1          4 3423500033     4199617170        46243151            1 N 26-FEB-2013:11:17:55
BEDA0B200100512C2801000101C0021602C102   2013-02-26/11:12:01           N N N Y
 
9nhany360wxs1          3 3423500033     4199617170        46243151            2 N 26-FEB-2013:11:30:26
BEDA0B200100512C2AA5000101C0021602C102   2013-02-26/11:23:16           N N N Y
 
9nhany360wxs1          3 3423500033     4199617170       599033881            3 N 26-FEB-2013:11:40:00
BEDA0B200100512C2D58000101C0021602C103   2013-02-26/11:34:49           N N N Y
 
9nhany360wxs1          1 3423500033     4199617170        46243151            4 N 26-FEB-2013:11:42:17
BEDA0B200100512C2F19000101C002160180            2013-02-26/11:42:17           N N N Y
 
 
SQL> select distinct t.CHILD_NUMBER,t.PLAN_HASH_VALUE, b.name BIND_NAME,  b.value_string BIND_STRING, b.LAST_CAPTURED, b.WAS_CAPTURED
  2  from  v$sql t , v$sql_bind_capture b
  3  where b.value_string is not null and t.sql_id= '9nhany360wxs1'
  4  and t.sql_id=b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER order by b.LAST_CAPTURED;
 
CHILD_NUMBER PLAN_HASH_VALUE BIND_NAME   BIND_STRING          LAST_CAPTURED        WAS
------------ --------------- ------------       -------------------- -------------------- ---
           0       599033881 :VTYP       0                    26-FEB-2013:11:11:40 YES
           1        46243151 :VTYP       1                    26-FEB-2013:11:12:01 YES
           2        46243151 :VTYP       1                    26-FEB-2013:11:23:17 YES
           3       599033881 :VTYP       2                    26-FEB-2013:11:34:48 YES
           4        46243151 :VTYP       0                    26-FEB-2013:11:42:17 YES

Even  11G had improved on the bind peeking problem in 10G, but the limition for new imrovment still cause many problem special with histogram statistics.

Appendix   Test case on ORACLE 10G for stewed data and execution plan 
 
alter system set "_optimizer_invalidation_period" = 60 scope=memory;
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479             20
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479             20
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46479             20
 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, LAST_LOAD_TIME, IS_OBSOLETE
  2* from v$sql where sql_id='9fx3nzvp2scyn'
 
SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME      LAST_LOAD_TIME            I
------------- ---------- ---------- --------------- ------------ - --------------------  ------------------        -
9fx3nzvp2scyn          5 3928765396      3347831050            0 N 01-FEB-2013:17:57:34  2013-02-01/17:42:04   N                        9fx3nzvp2scyn          3 3928765396      3347831050            1 N 01-FEB-2013:18:01:01  2013-02-01/17:46:34   N                                                       9fx3nzvp2scyn          2 3928765396      3347831050            2 N 01-FEB-2013:17:59:10  2013-02-01/17:54:35   N                                                  9fx3nzvp2scyn          1 3928765396      3347831050            3 N 01-FEB-2013:18:03:13  2013-02-01/18:03:14   N                                                         
 
Now, all the child plans are same, in this case using “index” since created>sysdate -20 only has 3 records
Update the table to exchange the histogram for each data point.
 
 
SQL> update pt1 set created = sysdate - 40 where created > sysdate - 20;
3 rows updated.

SQL> commit;
Commit complete.
 
SQL> update pt1 set created = sysdate -10 where created > sysdate - 40;
45028 rows updated.

SQL> commit;
exec dbms_stats.gather_table_stats('SCOTT','PT1');
 
The correct plan should be changed from “index scan” to full table scan since 45028 of 45031 records need be accessed, however, the correct execution plan only be generated after fifth call (each call pause for 1  minute) !!
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
Pause 1 minute

SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
Pause 1 minute
 
SQL> r
  1* select max(object_id), min(object_id) from pt1 where created > sysdate - 20
 
MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
         46478              2
 
===== check execution plans history ===== 
SQL> r
  1  select SQL_ID, EXECUTIONS, HASH_VALUE, OLD_HASH_VALUE, PLAN_HASH_VALUE, CHILD_NUMBER, REMOTE, LAST_ACTIVE_TIME, BIND_DATA, LAST_LOAD_TIME, IS_OBSOLETE
  2* from v$sql where sql_id='9fx3nzvp2scyn'
 
SQL_ID        EXECUTIONS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME      LAST_LOAD_TIME   I
------------- ---------- ---------- --------------  ------------ - --------------------  -------------------   -
9fx3nzvp2scyn          6 3928765396      3347831050            0 N 01-FEB-2013:18:09:32  2013-02-01/17:42:04       N                                               
9fx3nzvp2scyn          4 3928765396      3347831050            1 N 01-FEB-2013:18:13:05  2013-02-01/17:46:34       N
9fx3nzvp2scyn          3 3928765396      3347831050            2 N 01-FEB-2013:18:16:02  2013-02-01/17:54:35       N
9fx3nzvp2scyn          2 3928765396      3347831050            3 N 01-FEB-2013:18:14:11  2013-02-01/18:03:14       N
9fx3nzvp2scyn          1 3928765396      3214516708            4 N 01-FEB-2013:18:17:44  2013-02-01/18:17:45       N
 
About these ads

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