A supplement to adaptive cursor sharing

For more information about adaptive cursor sharing, see blog. csdn. netyidian815articledetails17959907. I think the difficulty lies in understanding BIND_SENSITIVE and BIND_AWARE. Next, let's review: bind_sensitive: oracle believes that this statement may be required because of different variables bound.

For adaptive cursor sharing, please participate in: http://blog.csdn.net/yidian815/article/details/17959907's understanding of adaptive cursor sharing, I think the difficulty lies in the understanding of BIND_SENSITIVE and BIND_AWARE. Next, let's review: bind_sensitive: oracle believes that this statement may be required because of different variables bound.

About adaptive cursor sharing please join: http://blog.csdn.net/yidian815/article/details/17959907

In my opinion, the difficulty lies in understanding BIND_SENSITIVE and BIND_AWARE. Let's review:

Bind_sensitive: oracle believes that the statement may need to adopt different execution plans because of different variables bound. Therefore, it is necessary to monitor the execution of the statement and make judgments based on the execution results.

Bind_aware: oracle has determined through monitoring that this statement requires different execution plans for different Bind Variable values.

Now the following problems occur:

What kind of SQL statement does oracle determine is bind_sensitve?

For the bind_aware statement, How Does oracle select an execution plan based on different variable values?

Before answering these questions, let's take a look at the test environment.

SQL> desc t2; is the name blank? Type: Optional -------- ---------------------------------- IDNUMBER RTYPEVARCHAR2 (20) SELNUMBERSQL> select column_name, histogram from dba_tab_cols where table_name = 't2 'and owner = 'easy1 '; COLUMN_NAME HISTOGRAM values --------------- id nonertype frequencysel nonesql> select rtype, count (1), min (sel), max (sel) from t2 group by rtype order by 3; rtype count (1) MIN (SEL) MAX (SEL) -------------------- ---------- 1 1 7.6295E-06 7.6295E-062 2. 000015259. 0000152593 4. 000030518. 0000305184 8. 000061036. 0000610365 16. 000122072. 0001220726 32. 000244144. 0002441447 64. 000488289. 0004882898 128. 000976577. 0009765779 256. 001953155. 00195315510 512. 00390631. 0039063111 1024. 007812619. 007812619 rtype count (1) MIN (SEL) MAX (SEL) ---------------------- ---------- 12 2048. 015625238. 01562523813 4096. 031250477. 03125047714 8192. 062500954. 06250095415 16384. 125001907. 12500190716 32768. 250003815. 25000381517 65536. 50000763. 50000763

Sel represents the selectivity of rype in the entire table.

First, let's guess the answer to the first question.

Because bind_sensitive is applicable to different values of bind variables, we believe that only statements with bind_sensitive variables can be used.

SQL> select sum(id) from t2 where rtype=16;   SUM(ID)----------1610596352SQL> select sum(id) from t2 where rtype=1;   SUM(ID)---------- 1

SQL> l  1* select sql_text,is_bind_sensitive,is_bind_aware from v$sql where sql_text like 'select sum(id) from t2%'SQL> /SQL_TEXT     I I------------------------------------------------------------ - -select sum(id) from t2 where rtype=1     N Nselect sum(id) from t2 where rtype=16     N N

Is it possible that bind_sensitive is bound to a variable?

SQL> var v varchar2(100)SQL> select sum(id) from t2 where rtype=:v;   SUM(ID)----------SQL> select sum(id) from t2 where id=:v;   SUM(ID)----------

SQL> l  1* select sql_text,is_bind_sensitive,is_bind_aware from v$sql where sql_text like 'select sum(id) from t2%'SQL> /SQL_TEXT     I I------------------------------------------------------------ - -select sum(id) from t2 where rtype=1     N Nselect sum(id) from t2 where id=:v     N Nselect sum(id) from t2 where rtype=:v     Y Nselect sum(id) from t2 where rtype=16     N N

It seems that you not only need to bind variables, but also need to have histogram statistics on the column where the bind variables are located. Of course, this is in the case of equivalent operations. In other cases, what? Here we will not analyze it. At least in the current situation, oracle has the following restrictions on bind_sensitive (official document ):

The optimizer has peeked at the bind values to generate selecti#estimates.

A histogram exists on the column containing the bind value.

• The following text is from the network. For more information, see

  Q: What triggers a cursor to be marked "bind sensitive "?

  A: Our goal is to consider inclutypes of predicates where the selectivity can change when the bind value changes. in this first version of the feature, we only handle capacity ity predicates where a histogram exists on the column and range predicates (with or without histogram ). we do not currently consider LIKE predicates, but it is on the top of our list for future work.

  Next, let's take a look at how the bind_aware statement works. to simplify the operation, we use BIND_AWARE hint directly. The usage of this hint is explained as follows:

  From 11.1.0.7 onward it is possible to skip the monitoring that is required to detect bind-sensitive queries by using the BIND_AWARE hint. in the following example, the presence of the hint tells the optimizer that we believe the query is bind-sensitive, so it shocould use bind-aware cursor sharing from the first execution.

  SELECT /*+ BIND_AWARE */ MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;

  The hint will only work if the query uses bind variables in WHERE clause predicates referencing columns with histograms.

  There is also a NO_BIND_AWARE hint that tells the optimizer to ignore bind-sensitive queries, whose tively hiding the query from the adaptive cursor sharing functionality.

  Bind-aware cursor sharing has a small overhead associated with it, which is why Oracle use the "adaptive" approach to identifying queries that wowould benefit from bind-aware cursor sharing. adding the hint to queries that will not benefit from it is a waste.

  Before further experiments, create table t1

  SQL> create table t1 as select * from t2 where 1 = 2; the table has been created. SQL> alter table t1 modify rtype number; the table has been changed. SQL> insert into t1 select * from t2; 131071 rows have been created. SQL> commit; submitted completely. SQL> create index i1 on t1 (rtype); the index has been created.

  SQL> exec dbms_stats.gather_table_stats (user, 't1', cascade => true, estimate_percent => null, method_opt => 'for all columns size auto, for columns rtype size 40 '); the PL/SQL process is successfully completed. SQL> select table_name, column_name, endpoint_number, to_char (endpoint_value), endpoint_actual_value from user_histograms where table_name = 't1'; TABLE_NAME COLUMN_NAMEENDPOINT_NUMBER TO_CHAR (ENDPOINT_VALUE) ENDPOINT_ACTUAL_VALUE ---------- -------------------- --------------- ------------------------------ T1 RTYPE 1 1T1 RTYPE 3 2T1 RTYPE 7 3T1 RTYPE 15 4T1 RTYPE 3 1 5T1 RTYPE 63 6T1 RTYPE 127 7T1 RTYPE 255 8T1 RTYPE 511 9T1 RTYPE 1023 10T1 RTYPE 2047 11TABLE_NAME COLUMN_NAMEENDPOINT_NUMBER TO_CHAR (ENDPOINT_VALUE) ENDPOINT_ACTUAL_VALUE ---------- -------------------- --------------- ------------------------------ T1 RTYPE 4095 12T1 RTYPE 8191 13T1 RTYPE 16383 14T1 RTYPE 131071 17T1 ID 0 1T1 SEL 0. 00000762954254834821t1 ID 1 131071T1 SEL 1. 87501335164416 selected 19 rows.

  SQL> select rtype,count(1),min(sel),max(sel) from t1 group by rtype order by 3;     RTYPE   COUNT(1)MIN(SEL)   MAX(SEL)---------- ---------- ---------- ---------- 1    1 7.6295E-06 7.6295E-06 2    2 .000015259 .000015259 3    4 .000030518 .000030518 4    8 .000061036 .000061036 5   16 .000122072 .000122072 6   32 .000244144 .000244144 7   64 .000488289 .000488289 8  128 .000976577 .000976577 9  256 .001953155 .00195315510  512  .00390631  .0039063111 1024 .007812619 .007812619     RTYPE   COUNT(1)MIN(SEL)   MAX(SEL)---------- ---------- ---------- ----------12 2048 .015625238 .01562523813 4096 .031250477 .03125047714 8192 .062500954 .06250095417     114688 .875013352 .875013352

  The experiment process of bind_aware is as follows:

  SQL> alter system flush shared_pool; the system has been changed. SQL> var vr number; SQL> exec: vr: = 1PL/SQL process completed successfully. SQL> select/* + bind_aware */sum (id) from t1 where rtype =: vr; SUM (ID) ---------- 1SQL> @ show_sqlSQL_TEXT executions I have -------------select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y -- Because bind_aware hint SQL> @ show_selADDRESS HASH_VALUE SQL _ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ----------------------------- ---------- ------------ ---------- Zookeeper dd40c0e0 2679189014 082txyqgv2bhq 0 = VR 0 0.000007 0.000008 -- select SQL> exec: vr: = 2PL/SQL based on Histogram calculation. SQL> select/* + bind_aware */sum (id) from t1 where rtype =: vr; SUM (ID) ---------- 5SQL> @ show_sqlSQL_TEXT executions I have -------------select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 y n -- select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 y ysql> @ show_selADDRESS HASH_VALUE SQL _ID CHILD_NUMBER PREDIC ATE RANGE_ID low high ---------------- ---------- ------------- ------------ ---------- running dd40c0e0 2679189014 082txyqgv2bhq 1 = VR 0 0.000007 0.000017 -- The rtype = 2 selectivity is no longer 0.00007 ~ Between 0.00008, so a new sub-cursor is generated. Because the new cursor is the same as the execution plan of the old game, the sub-cursor 0 is set to non-shared, the memory running dd40c0e0 2679189014 082txyqgv2bhq 0 = VR 0 0.000007 0.000008SQL> exec: vr: = 1PL/SQL process has been completed successfully. SQL> select/* + bind_aware */sum (id) from t1 where rtype =: vr; SUM (ID) ---------- 1SQL> @ show_sqlSQL_TEXT executions I have -------------select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y -- use the new Child cursor, no more subcursor 0 SQL> @ sho_selSP2-0310: Unable to open file "sho_sel. SQL" SQL> @ Show_selADDRESS HASH_VALUE SQL _ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH temperature ------------ ------------- ------------ upper ---------- upper 2679189014 lower 1 = VR 0 0.000007 lower 2679189014 lower 0 = VR 0 0.000007 0.000008SQL exec: vr: = 3PL/SQL process completed successfully. SQL> select/* + bind_aware */sum (id) from t1 where rtype =: vr; SUM (ID) ---------- 22SQL> @ show_sqlSQL_TEXT executions I have -------------select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y YSQL> @ Show_selADDRESS HASH_VALUE SQL _ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH temperature ------------ ------------- ------------ upper ---------- upper 2679189014 lower 2 = VR 0 0.000007 lower 2679189014 lower 1 = VR 0 0.000007 lower 2679189014 lower 0 = VR 0 0.000007 0. 000008SQL> exec: vr: = 1PL/SQL process completed successfully. SQL> select/* + bind_aware */sum (id) from t1 where rtype =: vr; SUM (ID) ---------- 1SQL> @ show_sqlSQL_TEXT executions I have -------------select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y YSQL> Exec: vr: = 8PL/SQL process completed successfully. SQL> select/* + bind_aware */sum (id) from t1 where rtype =: vr; SUM (ID) ---------- 24512SQL> @ show_sqlSQL_TEXT executions I have ------------select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 y ysql> @ show_selADDRESS HASH_VALUE SQL _ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH temperature ---------- --------------- ------------ ---------- limit 2679189014 limit 3 = VR 0 0.000007 limit 2679189014 limit 2 = VR 0 0.000007 0.20.341_1_dd40c0e0 2679189014 082txyqgv2bhq 1 = VR 0 0.000007 0.20.171_1_dd40c0e0 2679189014 082txyqgv2bhq 0 = VR 0 0.000007 0.000008SQL> exec: vr: = 14PL/SQL process completed successfully. SQL> select/* + bind_aware */sum (id) from t1 where rtype =: vr; SUM (ID) ---------- 100659200SQL> @ show_sqlSQL_TEXT executions I have ------------select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 y ysql> @ show_selADDRESS HASH_VALUE SQL _ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ---------------- ---------- ------------- ------------ summary ---------- Summary dd40c0e0 2679189014 limit 4 = VR 0 0.000 007 listen 2679189014 082txyqgv2bhq 3 = VR 0 0.000007 then 2679189014 082txyqgv2bhq 2 = VR 0 0.000007 then 2679189014 then 1 = VR 0 0.000007 then 2679189014 082txyqgv2bhq 0 = VR 0 0.000007 0.000008SQL> exec: vr: = 15PL/SQL process completed successfully. SQL> select/* + bind_aware */sum (id) from t1 where rtype =: vr; SUM (ID) ---------- SQL> @ show_sqlSQL_TEXT executions I have -------------select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselec T/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y has selected 6 rows. SQL> @ show_selADDRESS HASH_VALUE SQL _ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ---------------- ---------- ------------- -------------- ---------- average 2679189014 limit 5 = VR 0 0.000003 0.068751 -- Because 15 does not exist, so we can selectively expand 00000000DD40C0E0 2679189014 082txyqgv2bhq 4 = VR 0 0.000007 0.068751_1_dd40c0e0 2679189014 082txyqgv2b Hq 3 = VR 0 0.000007 running 2679189014 082txyqgv2bhq 2 = VR 0 0.000007 0.20.341_dd40c0e0 2679189014 082txyqgv2bhq 1 = VR 0 0.000007 running 2679189014 082txyqgv2bhq 0 = VR 0 0.000007 0.000008 has selected 6 rows. SQL> exec: vr: = 17PL/SQL process completed successfully. SQL> select/* + bind_aware */sum (id) from t1 where rtype =: vr; SUM (ID) ---------- 8455659520SQL> @ show_sqlSQL_TEXT executions I have -------------select/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 2 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Nselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y Yselect/* + bind_aware */sum (id) from t1 where rtype =: vr 1 Y seven rows have been selected. SQL> @ show_selADDRESS HASH_VALUE SQL _ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH temperature ---------- --------------- ------------ ---------- limit 2679189014 limit 6 = VR 0 0.787503 -- the new sub-game subject execution plan is different from the old sub-cursor, therefore, the parameter dd40c0e0 2679189014 082txyqgv2bhq 5 = VR 0 0.000003 0.068751_1_dd40c0e0 2679189014 082tx is retained. Yqgv2bhq 4 = VR 0 0.000007 running 2679189014 082txyqgv2bhq 3 = VR 0 0.000007 running 2679189014 running 2 = VR 0 0.000007 running 2679189014 082txyqgv2bhq 1 = VR 0 0.000007 running 2679189014 082txyqgv2bhq 0 = VR 0 0.000007 0.000008 already select 7 rows.

  It can be seen that the predicate for variable binding plays an important role in bind_aware.