对于自适应游标共享的一点补充

来源：曲趣美食

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, effectively 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 would benefit from bind-aware cursor sharing. Adding the hint to queries that will not benefit from it is a waste.

在进一步实验之前，创建表t1

SQL> create table t1 as select * from t2 where 1 =2;

表已创建。

SQL> alter table t1 modify rtype number;

表已更改。

SQL> insert into t1 select * from t2;

已创建 131071 行。

SQL> commit;

提交完成。

SQL> create index i1 on t1(rtype);

索引已创建。

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');

PL/SQL 过程已成功完成。

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_NAME	ENDPOINT_NUMBER TO_CHAR(ENDPOINT_VALUE) 	 ENDPOINT_ACTUAL_VALUE
---------- -------------------- --------------- ---------------------------------------- ------------------------------
T1	 RTYPE	 1 1
T1	 RTYPE	 3 2
T1	 RTYPE	 7 3
T1	 RTYPE	 15 4
T1	 RTYPE	 31 5
T1	 RTYPE	 63 6
T1	 RTYPE	 127 7
T1	 RTYPE	 255 8
T1	 RTYPE	 511 9
T1	 RTYPE	 1023 10
T1	 RTYPE	 2047 11

TABLE_NAME COLUMN_NAME	ENDPOINT_NUMBER TO_CHAR(ENDPOINT_VALUE) 	 ENDPOINT_ACTUAL_VALUE
---------- -------------------- --------------- ---------------------------------------- ------------------------------
T1	 RTYPE	 4095 12
T1	 RTYPE	 8191 13
T1	 RTYPE	 16383 14
T1	 RTYPE	 131071 17
T1	 ID	 0 1
T1	 SEL	 0 .00000762951094834821
T1	 ID	 1 131071
T1	 SEL	 1 .875013351416

已选择19行。

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	  .0004882 .0004882
	 8	 128 .000976577 .000976577
	 9	 256 .001953155 .001953155
	10	 512 .00390631 .00390631
	11	 1024 .007812619 .007812619

 RTYPE COUNT(1)	MIN(SEL) MAX(SEL)
---------- ---------- ---------- ----------
	12	 2048 .015625238 .015625238
	13	 4096 .031250477 .031250477
	14	 8192 .062500954 .062500954
	17 114688 .875013352 .875013352

对bind_aware的实验过程如下：

SQL> alter system flush shared_pool;

系统已更改。

SQL> var vr number;
SQL> exec :vr := 1

PL/SQL 过程已成功完成。

SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;

 SUM(ID)
----------
	 1

SQL> @show_sql

SQL_TEXT	 EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y Y --由于使用了bind_aware HINT 

SQL> @show_sel

ADDRESS 	 HASH_VALUE SQL_ID	 CHILD_NUMBER PREDICATE	 RANGE_ID LOW	 HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 26791014 082txyqgv2bhq	 0 =VR	 0 0.000007 0.000008 --根据直方图计算出rtype=1的选择性

SQL> exec :vr := 2

PL/SQL 过程已成功完成。

SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;

 SUM(ID)
----------
	 5

SQL> @show_sql

SQL_TEXT	 EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N --逐步淘汰出内存
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y Y

SQL> @show_sel

ADDRESS 	 HASH_VALUE SQL_ID	 CHILD_NUMBER PREDICATE	 RANGE_ID LOW	 HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 26791014 082txyqgv2bhq	 1 =VR	 0 0.000007 0.000017 --由于rtype=2的选择性不再0.00007~0.00008之间，所以生成新的子游标，由于新游标和旧游标的执行计划相同，所以进行合并，子游标0被设置为非共享，逐步淘汰出内存
00000000DD40C0E0 26791014 082txyqgv2bhq	 0 =VR	 0 0.000007 0.000008

SQL> exec :vr := 1

PL/SQL 过程已成功完成。

SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;

 SUM(ID)
----------
	 1

SQL> @show_sql

SQL_TEXT	 EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y Y --使用新的子游标，不再使用0号子游标

SQL> @sho_sel
SP2-0310: 无法打开文件 "sho_sel.sql"
SQL> @show_sel

ADDRESS 	 HASH_VALUE SQL_ID	 CHILD_NUMBER PREDICATE	 RANGE_ID LOW	 HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 26791014 082txyqgv2bhq	 1 =VR	 0 0.000007 0.000017
00000000DD40C0E0 26791014 082txyqgv2bhq	 0 =VR	 0 0.000007 0.000008

SQL> exec :vr := 3

PL/SQL 过程已成功完成。

SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;

 SUM(ID)
----------
	22

SQL> @show_sql

SQL_TEXT	 EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y Y

SQL> @show_sel

ADDRESS 	 HASH_VALUE SQL_ID	 CHILD_NUMBER PREDICATE	 RANGE_ID LOW	 HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 26791014 082txyqgv2bhq	 2 =VR	 0 0.000007 0.000034
00000000DD40C0E0 26791014 082txyqgv2bhq	 1 =VR	 0 0.000007 0.000017
00000000DD40C0E0 26791014 082txyqgv2bhq	 0 =VR	 0 0.000007 0.000008

SQL> exec :vr := 1

PL/SQL 过程已成功完成。

SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;

 SUM(ID)
----------
	 1

SQL> @show_sql

SQL_TEXT	 EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y Y

SQL> exec :vr := 8

PL/SQL 过程已成功完成。

SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;

 SUM(ID)
----------
 24512

SQL> @show_sql

SQL_TEXT	 EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y Y

SQL> @show_sel

ADDRESS 	 HASH_VALUE SQL_ID	 CHILD_NUMBER PREDICATE	 RANGE_ID LOW	 HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 26791014 082txyqgv2bhq	 3 =VR	 0 0.000007 0.001074
00000000DD40C0E0 26791014 082txyqgv2bhq	 2 =VR	 0 0.000007 0.000034
00000000DD40C0E0 26791014 082txyqgv2bhq	 1 =VR	 0 0.000007 0.000017
00000000DD40C0E0 26791014 082txyqgv2bhq	 0 =VR	 0 0.000007 0.000008

SQL> exec :vr := 14

PL/SQL 过程已成功完成。

SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;

 SUM(ID)
----------
 100659200

SQL> @show_sql

SQL_TEXT	 EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y Y

SQL> @show_sel

ADDRESS 	 HASH_VALUE SQL_ID	 CHILD_NUMBER PREDICATE	 RANGE_ID LOW	 HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 26791014 082txyqgv2bhq	 4 =VR	 0 0.000007 0.068751
00000000DD40C0E0 26791014 082txyqgv2bhq	 3 =VR	 0 0.000007 0.001074
00000000DD40C0E0 26791014 082txyqgv2bhq	 2 =VR	 0 0.000007 0.000034
00000000DD40C0E0 26791014 082txyqgv2bhq	 1 =VR	 0 0.000007 0.000017
00000000DD40C0E0 26791014 082txyqgv2bhq	 0 =VR	 0 0.000007 0.000008

SQL> exec :vr := 15

PL/SQL 过程已成功完成。

SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;

 SUM(ID)
----------

SQL> @show_sql

SQL_TEXT	 EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y Y

已选择6行。

SQL> @show_sel

ADDRESS 	 HASH_VALUE SQL_ID	 CHILD_NUMBER PREDICATE	 RANGE_ID LOW	 HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 26791014 082txyqgv2bhq	 5 =VR	 0 0.000003 0.068751 --由于15不存在，所以选择性向下扩充
00000000DD40C0E0 26791014 082txyqgv2bhq	 4 =VR	 0 0.000007 0.068751
00000000DD40C0E0 26791014 082txyqgv2bhq	 3 =VR	 0 0.000007 0.001074
00000000DD40C0E0 26791014 082txyqgv2bhq	 2 =VR	 0 0.000007 0.000034
00000000DD40C0E0 26791014 082txyqgv2bhq	 1 =VR	 0 0.000007 0.000017
00000000DD40C0E0 26791014 082txyqgv2bhq	 0 =VR	 0 0.000007 0.000008

已选择6行。

SQL> exec :vr := 17

PL/SQL 过程已成功完成。

SQL> select /*+ bind_aware */ sum(id) from t1 where rtype = :vr;

 SUM(ID)
----------
8455659520

SQL> @show_sql

SQL_TEXT	 EXECUTIONS I I I
------------------------------------------------------------ ---------- - - -
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 2 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y N
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y Y
select /*+ bind_aware */ sum(id) from t1 where rtype = :vr	 1 Y Y Y

已选择7行。

SQL> @show_sel

ADDRESS 	 HASH_VALUE SQL_ID	 CHILD_NUMBER PREDICATE	 RANGE_ID LOW	 HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
00000000DD40C0E0 26791014 082txyqgv2bhq	 6 =VR	 0 0.787503 0.962503 --新的子游标的执行计划于旧子游标不同，所以均保留
00000000DD40C0E0 26791014 082txyqgv2bhq	 5 =VR	 0 0.000003 0.068751
00000000DD40C0E0 26791014 082txyqgv2bhq	 4 =VR	 0 0.000007 0.068751
00000000DD40C0E0 26791014 082txyqgv2bhq	 3 =VR	 0 0.000007 0.001074
00000000DD40C0E0 26791014 082txyqgv2bhq	 2 =VR	 0 0.000007 0.000034
00000000DD40C0E0 26791014 082txyqgv2bhq	 1 =VR	 0 0.000007 0.000017
00000000DD40C0E0 26791014 082txyqgv2bhq	 0 =VR	 0 0.000007 0.000008

已选择7行。

由此可见，计算绑定变量的谓词选择性在bind_aware中扮演者重要角色