In 10G, even bind variable peeking plus stewed column data, the bind variable values changing would not trigger SQL plan switching or re-gen. Execution re-gen/switch is triggered by statistics, and when it just switches, the current value of bind variables would not be used by optimizer, only new version of plan will be affected by the current captured values (captured interval trigger system to capture current bind variable value).
In 11G, Optimizer add one attribute to check the bind variable is sensitive or not, in v$SQL view, a few columns be add in to host the bind variable new attributes : IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE
If IS_BIND_SENSITIVE = ‘N’, then bind variable current value would not change the execution plans. ORACLE only add SQL with bind variable sensitive for simple filter as <column i> = <variable> and <column i> had stewed data with histogram statistics. Otherwise, Optimizer handle execution plan as Non bind variable SQL, and current bind variables’ values only be used when new version of plan be re-gen.
For “sensitive” bind variable, new version of plan base on the actual value would be generated when value be changed. The existing execution plans in cache has one attribute “IS_SHAREABLE”, when “sensitive” bind variable value changed, Optimizer will re-use the plan if column statistics shows the value has same distribution bulk with the captured bind variable value for this version/child number of SQL ID. If the captured time exceeds the “_cursor_bind_capture_interval”, then new captured value for this version of SQL on the execution plan will be logged, and be used in future for plan switching.
Run :
select max(object_id),min(object_id) from object_list where type_id < :vtype + 1
Check the bind value and execution plan:
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= '8abcnfd53uwpn' 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 --------------------------------------- -------------------------- - - - - 8abcnfd53uwpn 5 1245541044 2104998663 46243151 0 N 08-FEB-2013:09:36:54 BEDA0B20010051145657000101C002160180 2013-02-08/09:35:19 N N N Y
For Table “object_list”, the “TYPE_ID” had stewed data and histogram had been created. The bind variable peeking only be defined as “SENSITIVE”, if the column filter expression is directly compare with bind variable and the column data is stewed distributed, and the column had histogram statistics.
SQL> select * from DBA_TAB_HISTOGRAMS where table_name=’OBJECT_LIST’ and column_name=’TYPE_ID’;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE --------- ----------------- ------------ --------------- -------------- ----------------------- DBA1 OBJECT_LIST TYPE_ID 16 0 DBA1 OBJECT_LIST TYPE_ID 1281200 1
Run below with /* vtyp in (0,1,2,3) */
select max(object_id) from object_list where type_id = :vtyp
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= 'ck03fdksfnrtw' 4* 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 ---------------------------------------- -------------------- - - - - ck03fdksfnrtw 2 2968149820 847886462 599033881 0 N 26-FEB-2013:18:10:55 BEDA0B200100512C8A2F000101C0021602C102 2013-02-26/10:50:01 N Y N Y ck03fdksfnrtw 4 2968149820 847886462 46243151 1 N 26-FEB-2013:18:15:19 BEDA0B200100512C8AA5000101C002160180 2013-02-26/18:12:53 N Y N N ck03fdksfnrtw 2 2968149820 847886462 599033881 2 N 26-FEB-2013:18:16:46 BEDA0B200100512C8B37000101C0021602C102 2013-02-26/18:15:18 N Y Y Y ck03fdksfnrtw 1 2968149820 847886462 46243151 3 N 26-FEB-2013:18:16:27 BEDA0B200100512C8B53000101C0021602C104 2013-02-26/18:15:46 N Y Y N ck03fdksfnrtw 3 2968149820 847886462 46243151 4 N 26-FEB-2013:18:18:41 BEDA0B200100512C8B7B000101C002160180 2013-02-26/18:16:27 N Y Y 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 from v$sql t , v$sql_bind_capture b where b.value_string is not null and t.sql_id= 'ck03fdksfnrtw' 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 1 26-FEB-2013:18:10:55 YES 1 46243151 :VTYP 0 26-FEB-2013:18:12:53 YES 2 599033881 :VTYP 1 26-FEB-2013:18:15:19 YES 3 46243151 :VTYP 3 26-FEB-2013:18:15:47 YES 4 46243151 :VTYP 0 26-FEB-2013:18:16:27 YES
In ORACLE 11G, new view be created for store “validate” plan selectivity for the execution plans.
SQL> select hash_value, sql_id, child_number, range_id, low, high, predicate from v$sql_cs_selectivity where sql_id='ck03fdksfnrtw'; HASH_VALUE SQL_ID CHILD_NUMBER RANGE_ID LOW HIGH PREDICATE ---------- ------------- ------------ ---------- ---------- ---------- ------------------------------ 2968149820 ck03fdksfnrtw 4 0 0.000006 0.000014 =VTYP 2968149820 ck03fdksfnrtw 3 0 0.000006 0.000007 =VTYP 2968149820 ck03fdksfnrtw 2 0 0.899988 1.099986 =VTYP
Another new view store the histogram for execution times, and each version/child has three buckets
SQL> select hash_value, sql_id, child_number, bucket_id, count from v$sql_cs_histogram where sql_id='ck03fdksfnrtw'; 2 3 HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT ---------- ------------- ------------ ---------- ---------- 2968149820 ck03fdksfnrtw 4 0 3 2968149820 ck03fdksfnrtw 4 1 0 2968149820 ck03fdksfnrtw 4 2 0 2968149820 ck03fdksfnrtw 3 0 1 2968149820 ck03fdksfnrtw 3 1 0 2968149820 ck03fdksfnrtw 3 2 0 2968149820 ck03fdksfnrtw 2 0 0 2968149820 ck03fdksfnrtw 2 1 0 2968149820 ck03fdksfnrtw 2 2 2 2968149820 ck03fdksfnrtw 1 0 3 2968149820 ck03fdksfnrtw 1 1 0 2968149820 ck03fdksfnrtw 1 2 1 2968149820 ck03fdksfnrtw 0 0 1 2968149820 ck03fdksfnrtw 0 1 0 2968149820 ck03fdksfnrtw 0 2 1 15 rows selected.
ORACLE 11g also store the running time resource usage statistics such as return rows, buffer usage CPU in the view
SQL> select hash_value, sql_id, child_number, bind_set_hash_value, peeked, executions, rows_processed, buffer_gets, cpu_time from v$sql_cs_statistics where sql_id='ck03fdksfnrtw'; 2 3 HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ---------- 2968149820 ck03fdksfnrtw 4 1475158189 Y 1 33 19 0 2968149820 ck03fdksfnrtw 3 2706503459 Y 1 1 3 0 2968149820 ck03fdksfnrtw 2 2342552567 Y 1 1281185 9087 0 2968149820 ck03fdksfnrtw 1 1475158189 Y 1 33 19 0 2968149820 ck03fdksfnrtw 0 1475158189 Y 1 17 9087 0
ORACLE 11G has owe rule to set SQL as “SENSITIVE”, ORACLE 11g only manage those SQL marked as “SENSITIVE” bind variable as above.
Appendix --- Test case on ORACLE 10G for stewed data and Bind variable peeking
alter system set "_optimizer_invalidation_period" = 60 scope=memory; SQL> var vdate number; SQL> exec :vdate := 20; PL/SQL procedure successfully completed. SQL> select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate; MAX(OBJECT_ID) MIN(OBJECT_ID) -------------- -------------- 46478 2 SQL> 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 from v$sql where sql_id='bbjqg2f62vz6z'; SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I ----------------------------------------- --------------------- - bbjqg2f62vz6z 1 2351824095 3130352737 3347831050 0 N 01-FEB-2013:18:26:59 BEDA0A200300510B9873000101C0021602C115 2013-02-01/18:27:00 N SQL> select distinct t.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, , b.CHILD_NUMBER from v$sql t , v$sql_bind_capture b where b.value_string is not null and t.sql_id='bbjqg2f62vz6z' and t.sql_id = b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER SQL_ID SQL_TEXT ------------- ------------------------------------------------------------------------------------------ PLAN_HASH_VALUE BIND_NAME BIND_STRING LAST_CAPTURED WAS CHILD_NUMBER --------------- --------------- -------------------------- -------------------- --- ------------ bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate 3347831050 :VDATE 20 01-FEB-2013:18:26:59 YES 0 SQL> exec :vdate := 200; PL/SQL procedure successfully completed. SQL> select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate; MAX(OBJECT_ID) MIN(OBJECT_ID) -------------- -------------- 46479 2 SQL> 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 from v$sql where sql_id='bbjqg2f62vz6z'; SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I ----------------------------------------- --------------------- - bbjqg2f62vz6z 2 2351824095 3130352737 3347831050 0 N 01-FEB-2013:18:30:47 BEDA0A200300510B9873000101C0021602C115 2013-02-01/18:27:00 N SQL> select distinct t.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, , b.CHILD_NUMBER from v$sql t , v$sql_bind_capture b where b.value_string is not null and t.sql_id='bbjqg2f62vz6z' and t.sql_id = b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER SQL_ID SQL_TEXT ------------- ------------------------------------------------------------------------------------------ PLAN_HASH_VALUE BIND_NAME BIND_STRING LAST_CAPTURED WAS CHILD_NUMBER --------------- --------------- -------------------------- -------------------- --- ------------ bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate 3347831050 :VDATE 200 01-FEB-2013:18:30:47 YES 0 SQL> exec dbms_stats.gather_table_stats('SCOTT','PT1'); /* Make cursor plan in cache invalid */ PL/SQL procedure successfully completed. SQL> r 1* select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate MAX(OBJECT_ID) MIN(OBJECT_ID) -------------- -------------- 46479 2 SQL> 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 from v$sql where sql_id='bbjqg2f62vz6z'; SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I ----------------------------------------- --------------------- - bbjqg2f62vz6z 3 2351824095 3130352737 3347831050 0 N 01-FEB-2013:18:33:38 BEDA0A200300510B9873000101C0021602C115 2013-02-01/18:27:00 N SQL> select distinct t.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, , b.CHILD_NUMBER from v$sql t , v$sql_bind_capture b where b.value_string is not null and t.sql_id='bbjqg2f62vz6z' and t.sql_id = b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER SQL_ID SQL_TEXT ------------- ------------------------------------------------------------------------------------------ PLAN_HASH_VALUE BIND_NAME BIND_STRING LAST_CAPTURED WAS CHILD_NUMBER --------------- --------------- -------------------------- -------------------- --- ------------ bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate 3347831050 :VDATE 200 01-FEB-2013:18:33:35 YES 0 SQL> r 1* select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate MAX(OBJECT_ID) MIN(OBJECT_ID) -------------- -------------- 46479 2 SQL> 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 from v$sql where sql_id='bbjqg2f62vz6z'; SQL_ID EXECUTIONS HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER R LAST_ACTIVE_TIME ------------- ---------- ---------- -------------- --------------- ------------ - -------------------- BIND_DATA LAST_LOAD_TIME I ----------------------------------------- --------------------- - bbjqg2f62vz6z 3 2351824095 3130352737 3347831050 0 N 01-FEB-2013:18:33:38 BEDA0A200300510B9873000101C0021602C115 2013-02-01/18:27:00 N bbjqg2f62vz6z 1 2351824095 3130352737 3347831050 1 N 01-FEB-2013:18:34:59 BEDA0A200300510B9A53000101C0021602C203 2013-02-01/18:35:01 N SQL> select distinct t.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, , b.CHILD_NUMBER from v$sql t , v$sql_bind_capture b where b.value_string is not null and t.sql_id='bbjqg2f62vz6z' and t.sql_id = b.sql_id and t.CHILD_NUMBER = b.CHILD_NUMBER SQL_ID SQL_TEXT ------------- ------------------------------------------------------------------------------------------ PLAN_HASH_VALUE BIND_NAME BIND_STRING LAST_CAPTURED WAS CHILD_NUMBER --------------- --------------- -------------------------- -------------------- --- ------------ bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate 3347831050 :VDATE 200 01-FEB-2013:18:34:59 YES 1 bbjqg2f62vz6z select max(object_id), min(object_id) from pt1 where created > sysdate - :vdate 3347831050 :VDATE 20 01-FEB-2013:18:33:35 YES 0
Fred's ORACLE DBA Works 