揭秘自适应游标共享技术(Adaptive Cursor Sharing)-阿里云开发者社区

dapter cursor sharing，自适应游标共享的引入是为了解决bind peeking在数据有明显倾斜的时候会生成次优执行计划的问题，Adapter cursor sharing使具有不同绑定变量值的SQL语句使用不同的执行计划成为可能。使用ACS前不得不提的两个概念：bind sensitive和bind aware，他们的状态体现在v$sql.is_bind_sensitive和v$sql.is_bind_aware列
is_bind_sensitive=y表示这条带绑定变量的SQL的执行计划对与绑定变量的取值是敏感的，即如果为绑定变量注入不同的值可能会引起不同的执行计划，当与绑定变量关联的字段上有统计信息时执行出来的语句都是is_bind_sensitive=y。
is_bind_aware=y表示这条带绑定变量的SQL对应的cursor执行计划已经作为后续相同SQL语句生成执行计划时的候选对象，即后续完全有可能使用软解析沿用此执行计划。
bind_sensitive是bind aware的前提
缺省情况下ACS功能是打开的，如果发现没有启用ACS，请检查一下参数值是否按以下要求进行设置：
_optim_peek_user_binds=TRUE
_optimizer_adaptive_cursor_sharing=TRUE
_optimizer_extended_cursor_sharing=UDO
_optimizer_extended_cursor_sharing_rel string=SIMPLE

我们由浅入深的探究一下Adaptive Cursor Sharing技术
//////////////////////////////////////////////
////第一部分 Adaptive cursor sharing 基本功能测试
//////////////////////////////////////////////
---创建测试表，在object_type字段制造出明显的skew data
drop table acstab;
create table acstab tablespace ts_acct_dat_01 as select * from dba_objects;

SQL> select count(*) from acstab;

COUNT(*)
----------
198320

set pagesize 300
SQL> select object_type,count(*) from acstab group by object_type order by 2 desc;
OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 63734
INDEX 61246
SYNONYM 25211
JAVA CLASS 20754
VIEW 6645
TABLE PARTITION 6605
INDEX PARTITION 4631
TYPE 2215
LOB 1387
PACKAGE 1090
PACKAGE BODY 1049
PROCEDURE 1004
JAVA RESOURCE 762
SEQUENCE 497
JAVA DATA 317
FUNCTION 254
TRIGGER 240
TYPE BODY 181
LIBRARY 167
QUEUE 37
TABLE SUBPARTITION 32
OPERATOR 30
DATABASE LINK 30
CONSUMER GROUP 26
XML SCHEMA 21
RULE SET 19
PROGRAM 19
JOB CLASS 14
EVALUATION CONTEXT 14
JOB 12
UNDEFINED 11
RESOURCE PLAN 11
CLUSTER 10
WINDOW 9
INDEXTYPE 7
CONTEXT 7
DIRECTORY 7
SCHEDULER GROUP 4
SCHEDULE 3
DESTINATION 2
JAVA SOURCE 2
MATERIALIZED VIEW 1
RULE 1
LOB PARTITION 1
EDITION 1

update acstab set object_type='TABLE' where rownum<=140000;
commit;

SQL> select object_type,count(*) from acstab group by object_type order by 2 desc;

OBJECT_TYPE COUNT(*)
------------------- ----------
TABLE 167639
INDEX 24128
TABLE PARTITION 2702
INDEX PARTITION 1945
VIEW 647
PROCEDURE 279
TYPE 244
SEQUENCE 133
SYNONYM 130
PACKAGE BODY 123
PACKAGE 103
LOB 90
TRIGGER 82
FUNCTION 38
DATABASE LINK 30
QUEUE 4
DIRECTORY 2
MATERIALIZED VIEW 1

create index ind_acstab on acstab(object_type) tablespace ts_Acct_dat_01;

exec dbms_stats.gather_table_stats(ownname=>'AD',tabname=>'ACSTAB',cascade=>TRUE,method_opt=>'for all columns size skewonly');

col column_name format a15
col endpoint_actual_value format a20
set linesize 170
set pagesize 120
SQL> select * from dba_tab_histograms where table_name='ACSTAB' and column_name='OBJECT_TYPE';

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ --------------- --------------- -------------- --------------------
AD ACSTAB OBJECT_TYPE 2 3.5440E+35 DATABASE LINK
AD ACSTAB OBJECT_TYPE 5 3.6519E+35 FUNCTION
AD ACSTAB OBJECT_TYPE 955 3.8063E+35 INDEX
AD ACSTAB OBJECT_TYPE 1050 3.8063E+35 INDEX PARTITION
AD ACSTAB OBJECT_TYPE 1052 3.9622E+35 LOB
AD ACSTAB OBJECT_TYPE 1056 4.1671E+35 PACKAGE
AD ACSTAB OBJECT_TYPE 1061 4.1671E+35 PACKAGE BODY
AD ACSTAB OBJECT_TYPE 1074 4.1705E+35 PROCEDURE
AD ACSTAB OBJECT_TYPE 1079 4.3237E+35 SEQUENCE
AD ACSTAB OBJECT_TYPE 1085 4.3277E+35 SYNONYM
AD ACSTAB OBJECT_TYPE 7891 4.3748E+35 TABLE
AD ACSTAB OBJECT_TYPE 7998 4.3748E+35 TABLE PARTITION
AD ACSTAB OBJECT_TYPE 8001 4.3782E+35 TRIGGER
AD ACSTAB OBJECT_TYPE 8013 4.3796E+35 TYPE
AD ACSTAB OBJECT_TYPE 8036 4.4802E+35 VIEW

---object_type对应的是频率直方图，直方图在estimate_pecent=>AUTO_SAMPLE_SIZE的时候是抽样进行统计的，所以这里的sample_size不等于total_rows
SQL> select table_name,column_name,sample_size,num_distinct,histogram from DBA_TAB_COL_STATISTICS where table_name='ACSTAB';

TABLE_NAME COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT HISTOGRAM
------------------------------ --------------- ----------- ------------ ---------------
ACSTAB EDITION_NAME 0 NONE
ACSTAB NAMESPACE 8035 20 FREQUENCY
ACSTAB SECONDARY 8037 2 FREQUENCY
ACSTAB GENERATED 8036 2 FREQUENCY
ACSTAB TEMPORARY 8036 2 FREQUENCY
ACSTAB STATUS 8036 2 FREQUENCY
ACSTAB TIMESTAMP 8034 42080 HEIGHT BALANCED
ACSTAB LAST_DDL_TIME 8034 41888 HEIGHT BALANCED
ACSTAB CREATED 8036 42456 HEIGHT BALANCED
ACSTAB OBJECT_TYPE 8036 18 FREQUENCY
ACSTAB DATA_OBJECT_ID 5547 137376 HEIGHT BALANCED
ACSTAB OBJECT_ID 8034 198320 HEIGHT BALANCED
ACSTAB SUBOBJECT_NAME 11269 770 HEIGHT BALANCED
ACSTAB OBJECT_NAME 8036 160288 HEIGHT BALANCED
ACSTAB OWNER 8036 40 FREQUENCY

###使用绑定变量:v1:='VIEW'执行查询
variable v1 varchar2(100);
exec :v1:='VIEW';
set linesize 170
select /* acs */ max(object_type),count(*) from acstab where object_type=:v1;

MAX(OBJECT_TYPE) COUNT(*)
------------------- ----------
VIEW 647

set pagesize 100
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6zcu0f9qrfnfm, child number 0
-------------------------------------
select /* acs */ max(object_type),count(*) from acstab where
object_type=:v1

Plan hash value: 3779426319

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IND_ACSTAB | 555 | 3885 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------

col sql_text format a6070
set linesize 170
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,last_active_time from v$sql where sql_text like 'select /* acs */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED LAST_ACTIVE_TIME
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- -----------------
select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 0 1 Y N Y 64 1 20150108 00:36:18
pe=:v1

###使用绑定变量:v1:='TABLE'执行查询
variable v1 varchar2(100);
exec :v1:='TABLE';
set linesize 170
select /* acs */ max(object_type),count(*) from acstab where object_type=:v1;

MAX(OBJECT_TYPE) COUNT(*)
------------------- ----------
TABLE 167639

---查看执行计划同object_type='VIEW'
SQL> set pagesize 100
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6zcu0f9qrfnfm, child number 0
-------------------------------------
select /* acs */ max(object_type),count(*) from acstab where
object_type=:v1

Plan hash value: 3779426319

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IND_ACSTAB | 555 | 3885 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------

---查看没有新的child cursor生成,executions变为了2
col sql_text format a60
set linesize 170
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,last_active_time from v$sql where sql_text like 'select /* acs */%';

SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED LAST_ACTIVE_TIME
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- -----------------
select /* acs */ max(object_type),count(*) from acstab where 6zcu0f9qrfnfm 0 2 Y N Y 464 2 20150108 00:36:18
object_type=:v1

###使用绑定变量:v1:='TABLE'执行第二次查询
variable v1 varchar2(100);
exec :v1:='TABLE';
set linesize 170
select /* acs */ max(object_type),count(*) from acstab where object_type=:v1;

MAX(OBJECT_TYPE) COUNT(*)
------------------- ----------
TABLE 167639

---查看执行计划从index range scan.变为了index fast full scan
SQL> set pagesize 100
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6zcu0f9qrfnfm, child number 1
-------------------------------------
select /* acs */ max(object_type),count(*) from acstab where
object_type=:v1

Plan hash value: 413337124

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 86 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX FAST FULL SCAN| IND_ACSTAB | 167K| 1148K| 86 (3)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_TYPE"=:V1)

---新生成了一个child cursor，且原先child_number=0的cursor变为了is_bind_aware=N and is_shareable=N
col sql_text format a60
set linesize 170
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,last_active_time from v$sql where sql_text like 'select /* acs */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED LAST_ACTIVE_TIME
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- -----------------
select /* acs */ max(object_type),count(*) from acstab where 6zcu0f9qrfnfm 0 2 Y N N 464 2 20150108 00:36:18
object_type=:v1

select /* acs */ max(object_type),count(*) from acstab where 6zcu0f9qrfnfm 1 1 Y Y Y 482 1 20150108 00:36:18
object_type=:v1

---查看一下此时v$sql_cs开头的三个视图内容
SQL> select * from v$sql_cs_histogram where sql_id='6zcu0f9qrfnfm';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 0 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 1 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 2 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 0 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 1 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 2 0

6 rows selected.

SQL> select * from v$sql_cs_selectivity where sql_id='6zcu0f9qrfnfm';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 =V1 0 0.762245 0.931633

SQL> select * from v$sql_cs_statistics where sql_id='6zcu0f9qrfnfm';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 3197905255 Y 1 167640 482 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 123367636 Y 1 648 64 0

先来看一下v$sql_cs_histogram，每一个child cursor有编号为0-2的三个bucket，在bucket_id列可以看到这个值，每当这个cursor对应的sql语句执行后就会给某个的bucket的计数器增加1，计数器值存在count列，具体是给bucket 0、bucket 1还是bucket 2的计数器加1，取决于以下条件(注意是处理的行数，即row_processed，不是返回的行数)，当"X>sql处理的行数>=0"时，给bucket 0计数器加1；当"Y>sql处理的行数>=X"时，给bucket 1计数器加1；当"sql处理的行数>=Y"时，给bucket 2计数器加1，这里的X、Y是多少后面会论证。从v$sql_cs_histogram也能够统计出每个cursor执行的次数，本例中select sql_id,child_number,count(*) from v$sql_cs_histogram where sql_id='6zcu0f9qrfnfm' group by sql_id,child_number的结果应该和select sql_id,child_number,executions from v$sql where sql_id='6zcu0f9qrfnfm'的执行结果相同

再来看下v$sql_cs_selectivity，只有标记为bind-aware的cursor才会记入这个视图，表示cursor里的predicate选择性(selectivity)的范围，这个范围就是根据predicate中涉及列的histogram统计信息计算而来的，当:v1:='TABLE'，执行select /* acs */ max(object_type),count(*) from acstab where object_type=:v1时，"TABLE"这个值在查询语句里的selectivity可以由dba_tab_histograms里"TABLE值所占用的Bucket数量/Bucket总数"所得到，即selectivity('TABLE')=(7891-1085)/8036=0.8469387755102041，v$sql_cs_selectivity里的low、high分别在这个值的基础上加减10%得到，low=0.8469387755102041*0.9=0.7622448979591837，high=0.8469387755102041*1.1=0.9316326530612245，v$sql_cs_selectivity.low和v$sql_cs_selectivity.high实际值分别为0.762245和0.931633是四舍五入的结果和我们的计算结果完全相符

SQL> select * from dba_tab_histograms where table_name='ACSTAB' and column_name='OBJECT_TYPE';

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ --------------- --------------- -------------- --------------------
AD ACSTAB OBJECT_TYPE 2 3.5440E+35 DATABASE LINK
AD ACSTAB OBJECT_TYPE 5 3.6519E+35 FUNCTION
AD ACSTAB OBJECT_TYPE 955 3.8063E+35 INDEX
AD ACSTAB OBJECT_TYPE 1050 3.8063E+35 INDEX PARTITION
AD ACSTAB OBJECT_TYPE 1052 3.9622E+35 LOB
AD ACSTAB OBJECT_TYPE 1056 4.1671E+35 PACKAGE
AD ACSTAB OBJECT_TYPE 1061 4.1671E+35 PACKAGE BODY
AD ACSTAB OBJECT_TYPE 1074 4.1705E+35 PROCEDURE
AD ACSTAB OBJECT_TYPE 1079 4.3237E+35 SEQUENCE
AD ACSTAB OBJECT_TYPE 1085 4.3277E+35 SYNONYM
AD ACSTAB OBJECT_TYPE 7891 4.3748E+35 TABLE
AD ACSTAB OBJECT_TYPE 7998 4.3748E+35 TABLE PARTITION
AD ACSTAB OBJECT_TYPE 8001 4.3782E+35 TRIGGER
AD ACSTAB OBJECT_TYPE 8013 4.3796E+35 TYPE
AD ACSTAB OBJECT_TYPE 8036 4.4802E+35 VIEW

v$sql_cs_statistics视图放到后面再讲

###使用绑定变量:v1:='PROCEDURE'进行查询
variable v1 varchar2(100);
exec :v1:='PROCEDURE';
set linesize 170
select /* acs */ max(object_type),count(*) from acstab where object_type=:v1;

MAX(OBJECT_TYPE) COUNT(*)
------------------- ----------
PROCEDURE 279

---使用index range scan，与一开始:v1:='VIEW'的执行计划相同
set pagesize 100
select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6zcu0f9qrfnfm, child number 2
-------------------------------------
select /* acs */ max(object_type),count(*) from acstab where
object_type=:v1

Plan hash value: 3779426319

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IND_ACSTAB | 321 | 2247 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

---从v$sql可以看到新增了child_number=2的cursor，其is_bind_sensitive、is_bind_aware、is_shareable三个值均为Y，虽然child_number=2的cursor与child_number=0的cursor对应的执行计划一样，但由于child_number=0的cursor已经是不可共享了(is_shareable=N)即将被逐出shared pool，所以child_number=2的cursor完全替代了child_number=0的cursor，这就是cursor merge
col sql_text format a70
set linesize 170
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,last_active_time from v$sql where sql_text like 'select /* acs */%';

SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED LAST_ACTIVE_TIME
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- -----------------
select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 0 2 Y N N 464 2 20150108 00:36:18
pe=:v1

select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 1 2 Y Y Y 964 2 20150108 00:36:18
pe=:v1

select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 2 1 Y Y Y 4 1 20150108 00:36:18
pe=:v1

---v$sql_cs系列视图
SQL> select * from v$sql_cs_histogram where sql_id='6zcu0f9qrfnfm';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 0 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 1 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 2 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 0 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 1 2
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 2 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 0 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 1 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='6zcu0f9qrfnfm';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 =V1 0 0.001456 0.001779
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 =V1 0 0.762245 0.931633

select * from v$sql_cs_statistics where sql_id='6zcu0f9qrfnfm';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 1764485445 Y 1 280 4 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 3197905255 Y 1 167640 482 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 123367636 Y 1 648 64 0

说一下v$sql_cs_statistics，观察child_number=2，对应的row_processeed=280，虽然我们执行的语句select /* acs */ max(object_type),count(*) from acstab where where object_type=:v1实际只返回1行，但从v$sql_cs_statistics里仍然能够精确的反映出为了得到最后的结果实际访问过多少行，这个和v$sql里的rows_processed不一样，v$sql里child_number=2对应的rows_processed仅为1，表示结果返回了多少行，并不体现为了得到这个结果实际处理的行数，这点和v$sql_cs_statistics是有区别的
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,last_active_time from v$sql where sql_text like 'select /* acs */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED LAST_ACTIVE_TIME
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- -----------------
select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 0 2 Y N N 464 2 20150108 00:36:18
pe=:v1

select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 1 2 Y Y Y 964 2 20150108 00:36:18
pe=:v1

select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 2 1 Y Y Y 4 1 20150108 00:36:18
pe=:v1

当然v$sql_cs_statistics也有它的局限性，对于同一个child_number来说无论被执行多少次，无论每次执行的时候绑定变量值为多少，v$sql_cs_statistics里关于这个child_number的信息只保留最早那一次执行的信息，我们来验证一下，使用v1:='TYPE'作为predicate执行一次select，因为cardinality与v1:='PROCEDURE'相近，完全能共享child_number=2的cursor
---使用绑定变量:v1:='TYPE'进行查询
variable v1 varchar2(100);
exec :v1:='TYPE';
set linesize 170
select /* acs */ max(object_type),count(*) from acstab where object_type=:v1;
MAX(OBJECT_TYPE) COUNT(*)
------------------- ----------
TYPE 244

---v$sql里child_number=2的cursor其executions从1增加为2，buffer_gets、row_processed也相应变大了
SQL> select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,last_active_time from v$sql where sql_text like 'select /* acs */%';

SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED LAST_ACTIVE_TIME
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- -----------------
select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 0 2 Y N N 464 2 20150108 00:36:18
pe=:v1

select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 1 2 Y Y Y 964 2 20150108 00:36:18
pe=:v1

select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 2 2 Y Y Y 8 2 20150108 00:36:18
pe=:v1

---但v$sql_cs_statistics里，child_number=2的executions,row_processed、buffer_gets还是维持着v1='PROCEDURE'时的状态
SQL> select * from v$sql_cs_statistics where sql_id='6zcu0f9qrfnfm';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 1764485445 Y 1 280 4 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 3197905255 Y 1 167640 482 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 123367636 Y 1 648 64 0

###执行计划相同时不同的cursor会进行merge，merge一般是后进入share pool的Cursor合并掉先进入share pool的Cursor
---使用selectivity更大的v1:='VIEW'作为predicate进行查询
variable v1 varchar2(100);
exec :v1:='VIEW';
set linesize 170
select /* acs */ max(object_type),count(*) from acstab where object_type=:v1;

MAX(OBJECT_TYPE) COUNT(*)
------------------- ----------
VIEW 647

---观察到新生成的child_number=3把child_number=2合并掉了,child_number=2变为了is_shareable=N,这是因为v1:='VIEW'也使用index_range_scan但其selectivity比child_number=2所对应的v1:='PROCEDURE'要大，所以child_number=2被吞并了
col sql_text format a70
set linesize 170
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,last_active_time from v$sql where sql_text like 'select /* acs */%';

SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED LAST_ACTIVE_TIME
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- -----------------
select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 0 2 Y N N 464 2 20150108 00:36:18
pe=:v1

select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 1 2 Y Y Y 964 2 20150108 00:36:18
pe=:v1

select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 2 3 Y Y N 12 3 20150108 00:36:18
pe=:v1

select /* acs */ max(object_type),count(*) from acstab where object_ty 6zcu0f9qrfnfm 3 1 Y Y Y 4 1 20150108 00:36:18
pe=:v1

---再使用selectivity比v1:='VIEW'还要大的v1:='INDEX PARTITION'作为predicate进行查询
variable v1 varchar2(100);
exec :v1:='INDEX PARTITION';
set linesize 170
select /* acs */ max(object_type),count(*) from acstab where object_type=:v1;

MAX(OBJECT_TYPE) COUNT(*)
------------------- ----------
INDEX PARTITION 1945

SQL> select * from v$sql_cs_statistics where sql_id='6zcu0f9qrfnfm';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 4 2348821916 Y 1 1946 10 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 3 123367636 Y 1 648 4 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 1764485445 Y 1 280 4 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 3197905255 Y 1 167640 482 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 123367636 Y 1 648 64 0

SQL> select * from v$sql_cs_histogram where sql_id='6zcu0f9qrfnfm';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 4 0 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 4 1 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 4 2 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 3 0 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 3 1 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 3 2 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 0 3
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 1 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 2 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 0 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 1 2
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 2 0
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 0 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 1 1
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 0 2 0

15 rows selected.

SQL> select * from v$sql_cs_selectivity where sql_id='6zcu0f9qrfnfm';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 4 =V1 0 0.001456 0.013004
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 3 =V1 0 0.001456 0.003080
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 2 =V1 0 0.001456 0.001779
07000003358F7FF0 1836536275 6zcu0f9qrfnfm 1 =V1 0 0.762245 0.931633

selectivity[v1:='VIEW']=95/8036=0.0118218018914883=>根据+10%、-10%的规则，计算出选择范围在[0.0106396217023395~0.0130039820806371]，cursor合并的过程体现在selectivity上，取最小的作为low，取最大的作为high，因此当前的selectivity范围已经从原来的[0.001456,0.001779]扩展到了[0.001456,0.013004]，随着范围的逐渐扩大，之后采用index range scan的sql的Selectivity落在这个范围的概率也逐渐变大，硬解析次数也会随之减少

////////////////////////////////////////////////
//// 第二部分：探寻v$sql_cs_histogram视图的奥秘
////////////////////////////////////////////////
这里我们需要论证几件事情：
1、前面的测试中我们提到过v$sql_cs_histogram中针对某个cursor的三个bucket计数器如何增长的问题：
当"X>sql处理的行数>=0"时，给bucket 0计数器加1；当"Y>sql处理的行数>=X"时，给bucket 1计数器加1；当"sql处理的行数>=Y"时，给bucket 2计数器加1"，这里的X和Y是多少，我们来测试一下，
---创建测试表
drop table buck1;

create table buck1(c1 varchar2(5),c2 varchar2(6),id number) tablespace ts_pub ;

begin
for i in 1..1050000 loop
insert into buck1 values(dbms_random.string('u',5),dbms_random.string('U',5),i);
end loop;
commit;
end;
/

create index ind_buck1 on buck1(id) tablespace ts_pub;

exec dbms_stats.gather_table_stats('ad','buck1',cascade=>TRUE,method_opt=>'for all columns size skewonly');

---构建查询条件，查询1100=>id>900及1000050=>id>999950的记录，把这一查询写在testbuckcnt.sh脚本里
i=901
while [ $i -le 1100 ]
do
export i
sqlplus ad/"Uiop246!" << EOF
variable v1 number;
exec :v1:=$i;
select /* buckcnt */ count(*) from buck1 where id<:v1;
EOF
(( i=i+1 ))
done

i=999951
while [ $i -le 1000050 ]
do
export i
sqlplus ad/"Uiop246!" << EOF
variable v1 number;
exec :v1:=$i;
select /* buckcnt */ count(*) from buck1 where id<:v1;
EOF
(( i=i+1 ))
done

---flush shared_pool保证测试环境是干净的
alter system flush shared_pool;

set linesize 170
col sql_text format a60
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt */%';

no rows selected

---执行testbuckcnt.sh脚本
nohup ./testbuckcnt.sh &

---执行过程中观察v$sql、v$sql_cs_histogram、v$sql_cs_selectivity视图变化情况
SQL> select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt */%';

SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt */ count(*) from buck1 where id<:v1 c9dmdd8ytjx5z 0 45 Y N Y 237 45 3116252312

SQL> r
1* select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt */%'

SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt */ count(*) from buck1 where id<:v1 c9dmdd8ytjx5z 0 52 Y N Y 265 52 3116252312

SQL> select * from v$sql_cs_histogram where sql_id='c9dmdd8ytjx5z';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
070000033574A018 1033434303 c9dmdd8ytjx5z 0 0 100
070000033574A018 1033434303 c9dmdd8ytjx5z 0 1 73
070000033574A018 1033434303 c9dmdd8ytjx5z 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='c9dmdd8ytjx5z';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
0700000335362690 1033434303 c9dmdd8ytjx5z 1 <V1 0 0.857101 1.047568

---testbuckcnt.sh脚本执行完后的v$sql、v$sql_cs_histogram、v$sql_cs_selectivity视图内容
set linesize 170
SQL> select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt */ count(*) from buck1 where id<:v1 c9dmdd8ytjx5z 0 200 Y N N 993 200 3116252312
select /* buckcnt */ count(*) from buck1 where id<:v1 c9dmdd8ytjx5z 1 100 Y Y Y 235400 100 1993484889

SQL> select * from v$sql_cs_histogram where sql_id='c9dmdd8ytjx5z';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000335362690 1033434303 c9dmdd8ytjx5z 1 0 0
0700000335362690 1033434303 c9dmdd8ytjx5z 1 1 50
0700000335362690 1033434303 c9dmdd8ytjx5z 1 2 50
0700000335362690 1033434303 c9dmdd8ytjx5z 0 0 100
0700000335362690 1033434303 c9dmdd8ytjx5z 0 1 100
0700000335362690 1033434303 c9dmdd8ytjx5z 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='c9dmdd8ytjx5z';

ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
0700000335362690 1033434303 c9dmdd8ytjx5z 1 <V1 0 0.857101 1.047568

可以看到总共执行了300次，以1000、1000000为界限，row_processed小于1000给bucket_id=0的计数器+1，row_processed大于等于1000小于1000000给bucket_id=1的计数器+1，row_processed大于等于1000000时会给bucket_id=2的计数器+1，这就回答了上面的问题：X=1000、Y=1000000，但是记住这仅对于select /* buckcnt */ count(*) from buck1 where id<:v1;而言，对于select * from buck1 where id<:v1就不一样了，下面看一下select * from buck1 where id<:v1的测试，仍然沿用上面的测试方法，只不过把testbuckcnt.sh里的count(*)用*替换掉，替换后的脚本是testbuckcnt1.sh
---testbuckcnt1.sh脚本内容
i=901
while [ $i -le 1100 ]
do
export i
sqlplus ad/"Uiop246!" << EOF
variable v1 number;
exec :v1:=$i;
select /* buckcnt */ * from buck1 where id<:v1;
EOF
(( i=i+1 ))
done

i=999951
while [ $i -le 1000050 ]
do
export i
sqlplus ad/"Uiop246!" << EOF
variable v1 number;
exec :v1:=$i;
select /* buckcnt */ * from buck1 where id<:v1;
EOF
(( i=i+1 ))
done

---测试过程同上，这里只把结果贴一下
set linesize 170
SQL> select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt */ * from buck1 where id<:v1 0gt0hsz9649wq 0 87 Y N Y 11791 82041 656603653

SQL> select * from v$sql_cs_histogram where sql_id='0gt0hsz9649wq';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000336EAE570 3529648022 0gt0hsz9649wq 0 0 0
0700000336EAE570 3529648022 0gt0hsz9649wq 0 1 200
0700000336EAE570 3529648022 0gt0hsz9649wq 0 2 100

SQL> select * from v$sql_cs_selectivity where sql_id='0gt0hsz9649wq';

no rows selected

从上面的结果可以看出来901~1100都属于bucket_id=1，999951~1000050都属于bucket_id=2，没有能够区分出bucket_id间的界限，我们重新构造一下查询，将下列脚本放在testbuckcnt2.sh里
i=452
while [ $i -le 551 ]
do
export i
sqlplus ad/"Uiop246!" << EOF
variable v1 number;
exec :v1:=$i;
select /* buckcnt */ * from buck1 where id<:v1;
EOF
(( i=i+1 ))
done

i=999997
while [ $i -le 1000006 ]
do
export i
sqlplus ad/"Uiop246!" << EOF
variable v1 number;
exec :v1:=$i;
select /* buckcnt */ * from buck1 where id<:v1;
EOF
(( i=i+1 ))
done

---执行testbuckcnt2.sh
nohup ./testbuckcnt2.sh &

set linesize 170
SQL> select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt */ * from buck1 where id<:v1 0gt0hsz9649wq 0 100 Y N N 7473 50050 656603653
select /* buckcnt */ * from buck1 where id<:v1 0gt0hsz9649wq 1 10 Y Y Y 669760 10000005 1257665044

select * from v$sql_cs_histogram where sql_id='0gt0hsz9649wq';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000336F160A8 3529648022 0gt0hsz9649wq 1 0 0
0700000336F160A8 3529648022 0gt0hsz9649wq 1 1 5
0700000336F160A8 3529648022 0gt0hsz9649wq 1 2 5
0700000336F160A8 3529648022 0gt0hsz9649wq 0 0 50
0700000336F160A8 3529648022 0gt0hsz9649wq 0 1 50
0700000336F160A8 3529648022 0gt0hsz9649wq 0 2 0

由上面的测试可以看出在select * from XXX where id<:v1的情况下各bucket_id的界限如下：500>=sql处理的行数>0时会给bucket_id=0的计数器+1、1000000>=sql处理的行数>500时会给bucket_id=1的计数器+1，当sql处理的行数>1000000时bucket_id=2的计数器+1

2、我们要揭示的第二项内容是什么时候会产生第一个bind aware cursor
---创建测试表buck2
drop table buck2;

create table buck2(c1 varchar2(5),c2 varchar2(6),id number) tablespace ts_pub ;

begin
for i in 1..10000 loop
insert into buck2 values(dbms_random.string('u',5),dbms_random.string('U',5),i);
end loop;
commit;
end;
/

create index ind_buck2 on buck2(id) tablespace ts_acct_dat_01;

exec dbms_stats.gather_table_stats('ad','buck2',cascade=>TRUE,method_opt=>'for all columns size skewonly');

---我们先运行testbuckcnt3.sh脚本，从id=1~id=6执行6遍，脚本内容如下
i=1
while [ $i -le 6 ]
do
export i
sqlplus ad/Uiop246! << EOF
variable v1 number;
exec :v1:=$i;
select /* buckcnt33 */ count(*) from buck2 where id<:v1;
EOF
(( i=i+1 ))
done

---执行完后v$sql,v$sql_cs_histogram,v$sql_cs_selectivity视图内容
set linesize 170
SQL> select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt33 */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 0 6 Y N Y 12 6 3095182543

SQL> select * from v$sql_cs_histogram where sql_id='3rn9q2qjhswjk';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000336583538 2735501874 3rn9q2qjhswjk 0 0 6
0700000336583538 2735501874 3rn9q2qjhswjk 0 1 0
0700000336583538 2735501874 3rn9q2qjhswjk 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='3rn9q2qjhswjk';

no rows selected

因为我们刚才执行了6次，每一次处理的行数rows_processed都小于1000，因计数到Bucket 0里，所以v$sql_histogram视图显示当前bucket_id=0的计数值为6，v$sql_cs_selectivity 视图内容为空表明这个cursor还没有变为bind aware，下面我们更改变量值为1001，手工执行执行下列语句6次
variable v1 number;
exec :v1:=1001;
select /* buckcnt33 */ count(*) from buck2 where id<:v1;

---6次执行完成后，v$sql,v$sql_cs_histogram,v$sql_cs_selectivity视图内容如下
set linesize 170
SQL> select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt33 */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 0 12 Y N Y 36 12 3095182543

SQL> select * from v$sql_cs_histogram where sql_id='3rn9q2qjhswjk'
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000336583538 2735501874 3rn9q2qjhswjk 0 0 6
0700000336583538 2735501874 3rn9q2qjhswjk 0 1 6
0700000336583538 2735501874 3rn9q2qjhswjk 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='3rn9q2qjhswjk';

no rows selected

v$sql_cs_histogram里bucket_id=0的计数器变成6，因为我们每次查询处理的行数都大于等于1000行，所以这6次都加到了bucket_id=1上，v$sql_cs_selectivity视图依旧为空

---下面我们执行第7次后，观察一下视图内容上的变化
variable v1 number;
exec :v1:=1001;
select /* buckcnt33 */ count(*) from buck2 where id<:v1;

set linesize 170
SQL> select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt33 */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 0 12 Y N N 36 12 3095182543
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 1 1 Y Y Y 4 1 3095182543

SQL> select * from v$sql_cs_histogram where sql_id='3rn9q2qjhswjk'
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000336583538 2735501874 3rn9q2qjhswjk 1 0 0
0700000336583538 2735501874 3rn9q2qjhswjk 1 1 1
0700000336583538 2735501874 3rn9q2qjhswjk 1 2 0
0700000336583538 2735501874 3rn9q2qjhswjk 0 0 6
0700000336583538 2735501874 3rn9q2qjhswjk 0 1 6
0700000336583538 2735501874 3rn9q2qjhswjk 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='3rn9q2qjhswjk';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
0700000336583538 2735501874 3rn9q2qjhswjk 1 <V1 0 0.090009 0.110011

当我们以id<1001为条件连续执行7次查询，在这第7次查询执行完后，出现了child_number=1的cursor，被标记为bind_aware，同时将child_number=0的cursor标记为not shareable，v$sql_cs_selectivity里记录了child_number=1这个cursor的selectivity范围。也就是说cursor从bind unaware变为bind aware的条件是：后有值的bucket的计数值大于先有值的bucket计数值的时候，会生成bind aware的cursor ，在上面的例子中当bucket_id=0最早拥有计数值，暂且看作是先有值的bucket，bucket_id=1稍后才有计数值，因此Bucket_id=1就是后有值的bucket，所以当bucket_id=1的计数值大于bucket_id=0的计数值的时候就生成了bind aware cursor(child_number=1)。当然这中机制也暗含了一个问题，那就是如果最先运行的那一批查询语句它们的绑定变量范围都集中在某个bucket，而且这批语句执行了n多次，假设后发起的语句都集中在另一个bucket那么后发起的语句至少也需要执行n多次后才能变为bind aware，也就是说后来的语句前n次执行都要沿用前面发起语句的执行计划，即使这个执行计划不是最优的。就拿buck2这个例子来说，有1W行数据，id列值从1~10000，id列上有索引，以id<10为条件执行时会使用index range scan，当下面的语句执行1000次后，这个1000次全都会记在bucket_id=0的计数器里
variable v1 number;
exec :v1:=10;
select count(*) from buck2 where id<:v1;

接着使用id<10000这个条件继续执行下列SQL，虽然以id<10000为条件执行时使用full table scan或者index Full scan效率会比较高，但实际却会发现下列语句前面1000次执行时都将会以index range scan作为access path，直到第1001次执行时才转为full table/index full scan，原理就是id<10000执行时的返回行都计数在bucket_id=1里，当bucket_id=1的计数值高于bucket_id=0的计数值时才会触发新的child cursor，才有可能用到不一样的执行计划
variable v1 number;
exec :v1:=10000;
select count(*) from buck2 where id<:v1;

所以Adaptive cursor sharing是否高效某种程度上和应用程序的访问数据的方式密切相关

3、最后我们看一下bind aware cursor是如何进行merge的 (1500/9999)
在生成了第1个Bind aware cursor之后，后续还有可能触发Bind aware Cursor生效和失效行为的就是cursor merge这个动作了，定性的讲由于输入的绑定变量值不同引起后续sql里的predicate选择范围大于之前的sql时，会产生cursor merge，相信大家在官方文档里都看到过这段描述，我们要进行的是定量的分析，即这个selectivity值大到什么程度才会触发merge，下面还是以buck2表作为例子
---使用testbuckcnt4.sh脚本执行SQL、脚本内容如下
i=1
while [ $i -le 10000 ]
do
export i
sqlplus ad/Uiop246! << EOF
variable v1 number;
exec :v1:=$i;
select /* buckcnt33 */ count(*) from buck2 where id<:v1;
EOF
(( i=i+1 ))
done

---清空shared pool
alter system flush shared_pool;

---执行testbuckcnt4.sh
nohup ./testbuckcnt4.sh > /dev/null &

---记录一下当前v$sql、v$sql_cs_histogram、v$sql_cs_selectivity视图的变化情况
set linesize 170
SQL> select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt33 */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 0 100 Y N Y 257 100 3095182543

SQL> r
1* select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt33 */%'
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 0 427 Y N Y 911 427 3095182543

SQL> select * from v$sql_cs_histogram where sql_id='3rn9q2qjhswjk'
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000336BAFE00 2735501874 3rn9q2qjhswjk 0 0 311
0700000336BAFE00 2735501874 3rn9q2qjhswjk 0 1 0
0700000336BAFE00 2735501874 3rn9q2qjhswjk 0 2 0

SQL> select * from v$sql_cs_histogram where sql_id='3rn9q2qjhswjk';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000336BAFE00 2735501874 3rn9q2qjhswjk 0 0 574
0700000336BAFE00 2735501874 3rn9q2qjhswjk 0 1 0
0700000336BAFE00 2735501874 3rn9q2qjhswjk 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='3rn9q2qjhswjk';

no rows selected

---执行完成后各视图的信息
set linesize 170
col sql_text format a60
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt33 */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 0 10000 Y N Y 119380 10000 3095182543

SQL> select * from v$sql_cs_histogram where sql_id='3rn9q2qjhswjk';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000336BAFE00 2735501874 3rn9q2qjhswjk 0 0 1000
0700000336BAFE00 2735501874 3rn9q2qjhswjk 0 1 0
0700000336BAFE00 2735501874 3rn9q2qjhswjk 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='3rn9q2qjhswjk';

no rows selected

发起虽然v$sql.executions为10000次，但v$sql_cs_histogram.count总和只有1000，且1000个计数都集中在bucket_id=0上，bucket_id=1、bucket_id=2两个计数都为0，这10000次执行中，第1~1000次返回值都小于1000行，计数在bucket_id=0没有问题，但是第1001~10000次理应计数在bucket_id=1，但bucket_id=1计数值仍然是0，猜测原因可能是oracle认为某个bucket_id执行次数达到1000次时，相对而言之后的查询落在这个Bucket_id的可能性很大，于是就不再去维护v$sql_cs_histogram表了，后续也就没有必要再生成bind aware cursor了，我们观察child_number=0的执行计划时可以看出oracle仍然使用最初的:v1:=1作为窥视值生成执行计划，对于后面传进来的bind variable并没有去peeking：

SQL> select * from table(dbms_xplan.display_cursor('3rn9q2qjhswjk',0,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3rn9q2qjhswjk, child number 0
-------------------------------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1

Plan hash value: 3095182543

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IND_BUCK2 | 1 | 4 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

。。。省略了Query Block Name、Outline Data section

Peeked Binds (identified by position):
--------------------------------------

1 - :V1 (NUMBER): 1

---为了能顺利演示cursor merge，我们把脚本内容稍微调整一下，写成testbuckcnt5.sh
i=501
while [ $i -le 5000 ]
do
export i
sqlplus ad/Uiop246! << EOF
variable v1 number;
exec :v1:=$i;
select /* buckcnt33 */ count(*) from buck2 where id<:v1;
EOF
(( i=i+1 ))
done

---清空shared pool
alter system flush shared_pool;

---执行testbuckcnt5.sh
nohup ./testbuckcnt5.sh &

---testbuckcnt5.sh执行完成后v$sql、v$sql_cs_histogram、v$sql_cs_selectivity视图内容如下：

set linesize 170
col sql_text format a60
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* buckcnt33 */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 0 1000 Y N N 3650 1000 3095182543
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 1 151 Y Y N 755 151 3095182543
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 2 166 Y Y N 830 166 3095182543
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 3 182 Y Y Y 987 182 3095182543
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 4 200 Y Y N 4800 200 2731385592
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 5 220 Y Y N 5280 220 2731385592
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 6 242 Y Y N 5808 242 2731385592
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 7 267 Y Y N 6408 267 2731385592
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 8 293 Y Y N 7032 293 2731385592
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 9 323 Y Y N 7752 323 2731385592
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 10 355 Y Y N 8520 355 2731385592
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 11 390 Y Y N 9360 390 2731385592
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 12 429 Y Y N 10296 429 2731385592
select /* buckcnt33 */ count(*) from buck2 where id<:v1 3rn9q2qjhswjk 13 282 Y Y Y 6768 282 2731385592

SQL> select * from v$sql_cs_histogram where sql_id='3rn9q2qjhswjk';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
070000033688E440 2735501874 3rn9q2qjhswjk 13 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 13 1 282
070000033688E440 2735501874 3rn9q2qjhswjk 13 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 12 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 12 1 429
070000033688E440 2735501874 3rn9q2qjhswjk 12 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 11 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 11 1 390
070000033688E440 2735501874 3rn9q2qjhswjk 11 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 10 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 10 1 355
070000033688E440 2735501874 3rn9q2qjhswjk 10 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 9 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 9 1 323
070000033688E440 2735501874 3rn9q2qjhswjk 9 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 8 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 8 1 293
070000033688E440 2735501874 3rn9q2qjhswjk 8 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 7 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 7 1 267
070000033688E440 2735501874 3rn9q2qjhswjk 7 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 6 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 6 1 242
070000033688E440 2735501874 3rn9q2qjhswjk 6 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 5 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 5 1 220
070000033688E440 2735501874 3rn9q2qjhswjk 5 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 4 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 4 1 200
070000033688E440 2735501874 3rn9q2qjhswjk 4 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 3 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 3 1 182
070000033688E440 2735501874 3rn9q2qjhswjk 3 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 2 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 2 1 166
070000033688E440 2735501874 3rn9q2qjhswjk 2 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 1 0 0
070000033688E440 2735501874 3rn9q2qjhswjk 1 1 151
070000033688E440 2735501874 3rn9q2qjhswjk 1 2 0
070000033688E440 2735501874 3rn9q2qjhswjk 0 0 500
070000033688E440 2735501874 3rn9q2qjhswjk 0 1 500
070000033688E440 2735501874 3rn9q2qjhswjk 0 2 0

SQL> select * from v$sql_cs_selectivity where sql_id='3rn9q2qjhswjk';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
070000033688E440 2735501874 3rn9q2qjhswjk 13 <V1 0 0.179928 0.519032
070000033688E440 2735501874 3rn9q2qjhswjk 12 <V1 0 0.179928 0.471837
070000033688E440 2735501874 3rn9q2qjhswjk 11 <V1 0 0.179928 0.428933
070000033688E440 2735501874 3rn9q2qjhswjk 10 <V1 0 0.179928 0.389879
070000033688E440 2735501874 3rn9q2qjhswjk 9 <V1 0 0.179928 0.354345
070000033688E440 2735501874 3rn9q2qjhswjk 8 <V1 0 0.179928 0.322112
070000033688E440 2735501874 3rn9q2qjhswjk 7 <V1 0 0.179928 0.292739
070000033688E440 2735501874 3rn9q2qjhswjk 6 <V1 0 0.179928 0.266117
070000033688E440 2735501874 3rn9q2qjhswjk 5 <V1 0 0.179928 0.241914
070000033688E440 2735501874 3rn9q2qjhswjk 4 <V1 0 0.179928 0.219912
070000033688E440 2735501874 3rn9q2qjhswjk 3 <V1 0 0.135014 0.199890
070000033688E440 2735501874 3rn9q2qjhswjk 2 <V1 0 0.135014 0.181628
070000033688E440 2735501874 3rn9q2qjhswjk 1 <V1 0 0.135014 0.165017

从上面的信息中我们可以了解到这条SQL存在两种不同的执行计划plan_hash_value=3095182543、plan_hash_value=2731385592，当前的14个child cursor里只有child_number=3和child_number=13两个cursor可以被共享，child_number=3的cursor对应plan_hash_value=3095182543这条INDEX RANGE SCAN的执行计划
set linesize 150
select * from table(dbms_xplan.display_cursor('3rn9q2qjhswjk',3,'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3rn9q2qjhswjk, child number 3
-------------------------------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1

Plan hash value: 3095182543

------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| IND_BUCK2 | 1817 |
------------------------------------------------

而child_number=13的cursor对应plan_hash_value=2731385592的执行计划，该执行计划是INDEX FAST FULL SCAN
select * from table(dbms_xplan.display_cursor('3rn9q2qjhswjk',13,'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3rn9q2qjhswjk, child number 13
--------------------------------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1

Plan hash value: 2731385592

----------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX FAST FULL SCAN| IND_BUCK2 | 4718 |
----------------------------------------------------

就拿Child_number=13的cursor来说它是从child_number=4的cursor开始一路merge而来的，通过v$sql_cs_selectivity发现其选择范围从最初的low=0.179928,high=0.219912经过9次merge最后的selectivity范围变成了low=0.179928,high=0.519032，这一路的演变是如何发展而来的，我们先来看在child_number=4的cursor存在的情况下，child_number=5的cursor是在什么情况下出现的？

---先看一下child_number=4的cursor对应的执行计划里peek的绑定变量值
set linesize 150
set pagesize 200
select * from table(dbms_xplan.display_cursor('3rn9q2qjhswjk',4,'ADVANCED'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3rn9q2qjhswjk, child number 4
-------------------------------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1

Plan hash value: 2731385592

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IND_BUCK2 | 1999 | 7996 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

。。。。省去了不相关的section
Peeked Binds (identified by position):
--------------------------------------

1 - :V1 (NUMBER): 2000

当前peek的绑定变量值是2000，说明child_number=4这个cursor是在执行到select /* buckcnt33 */ count(*) from buck2 where id<2000这个轮次时生成的，且v$sql_cs_histogram里child_number=4的count数为200，意味着从:v1=2000开始直至:v1=2199，一共200次执行里optimizer都没有尝试去重新窥视新的绑定变量值，child_number=4的selectivity值是以窥视值2000为基准计算得到的：
low=(2000-1)/(10000-1)*0.9=1999/9999*0.9=0.179928
high=(2000-1)/(10000-1)*1.1=1999/9999*1.1=0.219912

当执行到:v1=2200这个轮次时，以2200为基准计算出selectivity值为
low=(2200-1)/(10000-1)*0.9=2199/9999*0.9=0.197930
high=(2200-1)/(10000-1)*1.1=2199/9999*1.1=0.241914
因为low值0.197930大于前面的0.179928，所以low值还是保留原来的0.179928，high值0.241914大于前面的0.219912，且0.241914/0.219912=1.1000491105533122339845028920659>1.1；我们再计算一下当:v1=2199时的high值=(2199-1)/(10000-1)*1.1=2198/9999*1.1=0.241804，但是0.241804/0.219912=1.0995489104732802211793808432464<1.1；现在已经比较清楚了，每一次有新的bind变量值传入的时候，都会被optimizer窥视，当optimizer窥视到某个bind变量值，且用这个变量值计算出的Selectivity范围超过上一个cursor selectivity值的1.1倍时这个就会生成一个新的cursor，从而完成所谓的cursor merge，窥视到的这个变量值也会作为后续执行时的bind参考值。所以当我们推算:v1=2200是child_number=5这个cursor所使用的参考值，看一下child_number=5这个cursor的执行计划里的绑定变量值是否为2200

set linesize 150
set pagesize 200
SQL> select * from table(dbms_xplan.display_cursor('3rn9q2qjhswjk',5,'ADVANCED'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3rn9q2qjhswjk, child number 5
-------------------------------------
select /* buckcnt33 */ count(*) from buck2 where id<:v1

Plan hash value: 2731385592

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IND_BUCK2 | 2199 | 8796 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

。。。。省去了不相关的section
Peeked Binds (identified by position):
--------------------------------------

1 - :V1 (NUMBER): 2200

很明显child_number=5时采用了:v1=2200作为bind变量的基准值，child_number=5时的selectivity为low=0 0.179928,high=0.241914，而当:v1增长到2420的时候因为high=(2420-1)/(10000-1)*1.1=0.266117，而且0.266117/0.241914=1.1000479509247087808064022751887>1.1所以merge后生成了child_number=6，以此类推，直至child_number=13的出现。
最后再废话一句，不同执行计划(不同plan_hash_value)下的cursor不能合并，这也就是为什么我们在v$sql_cs_selectivity里观察到child_number=3=>child_number=4的low值有一个明显的跳变

////////////////////////////////////////////////////////////////
//// 第三部分：和adaptive cursor sharing有关的参数、hint
////////////////////////////////////////////////////////////////
_OPTIMIZER_EXTENDED_CURSOR_SHARING：生成的cursor是否具有bind sensitive属性，UDO表明会生成bind sensitive的cursor(默认值)，NONE表示不生成bind sensitive，由于变成bind sensitive的cursor才有可能变为bind aware，设置为NONE意味着不启用Adaptive cursor sharing特性

_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL：决定了哪些操作里cursor可以为bind sensitive属性，默认值是simple表示>,<,=,<>和like等操作里均会使cursor变为bind sensitive，如果设为None则代表cursor永远不会变成bind sensitive，由于变成bind sensitive才有可能变为bind aware所以设为None就意味着禁用ACS功能

_OPTIMIZER_ADAPTIVE_CURSOR_SHARING：默认值为TRUE，启用Adaptive Cursor sharing特性，表示能将bind sensitive的cursor在一定条件下转为bind aware的cursor。为NONE则禁用掉ACS特性

/*+ bind_aware */：强制Cursor变为bind aware

/*+ no_bind_aware */：禁止cursor变为bind aware

我们看一些例子，使用acstab作为测试表：
---acstab表里的数据分布情况，表的统计信息和object_type列的histogram均具备
set pagesize 120 linesize 170
select object_type,count(1) from acstab group by object_type order by 2 desc;
SQL> select object_type,count(1) from acstab group by object_type order by 2 desc;
OBJECT_TYPE COUNT(1)
------------------- ----------
TABLE 167639
INDEX 24128
TABLE PARTITION 2702
INDEX PARTITION 1945
VIEW 647
PROCEDURE 279
TYPE 244
SEQUENCE 133
SYNONYM 130
PACKAGE BODY 123
PACKAGE 103
LOB 90
TRIGGER 82
FUNCTION 38
DATABASE LINK 30
QUEUE 4
DIRECTORY 2
MATERIALIZED VIEW 1

###开启ACS/关闭ACS功能间的比较
variable v1 varchar2(100);
exec :v1:='LOB';
select /* acstab2 */ count(*) from acstab where object_type=:v1;

col sql_text format a70
set linesize 170
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* acstab2 */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* acstab2 */ count(*) from acstab where object_type= c314h5cgb2fuq 0 1 Y N Y 64 1 3779426319
:v1

上面的is_bind_sensitive=y

---设置三个参数后，再观察执行结果,is_bind_sensitive=n
alter session set "_OPTIMIZER_EXTENDED_CURSOR_SHARING"=NONE;
alter session set "_OPTIMIZER_ADAPTIVE_CURSOR_SHARING"=FALSE;
alter session set "_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL"=NONE;
alter system flush shared_pool;
variable v1 varchar2(100);
exec :v1:='LOB';
select /* acstab2 */ count(*) from acstab where object_type=:v1;

col sql_text format a70
set linesize 170
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* acstab2 */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* acstab2 */ count(*) from acstab where object_type=:v1 c314h5cgb2fuq 0 1 N N Y 64 1 3779426319

---代入object_type='TABLE'，执行n遍之后没有新的Cursor生成
variable v1 varchar2(100);
exec :v1:='TABLE';
select /* acstab2 */ count(*) from acstab where object_type=:v1;

select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /* acstab2 */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /* acstab2 */ count(*) from acstab where object_type=:v1 c314h5cgb2fuq 0 5 N N Y 1664 5 3779426319

###使用/*+ bind_aware */强制使用ACS功能
---_optimizer_adaptive_cursor_sharing、_optimizer_extended_cursor_sharing、_optimizer_extended_cursor_sharing_rel这三个参数依然保持禁用ACS的状态
SQL> show parameter _optimizer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_adaptive_cursor_sharing boolean FALSE
_optimizer_extended_cursor_sharing string NONE
_optimizer_extended_cursor_sharing_r string NONE
el
_optimizer_null_aware_antijoin boolean FALSE
_optimizer_use_feedback boolean FALSE

alter system flush shared_pool;

---使用bind_aware hint强制使cursor变为bind aware
variable v1 varchar2(100);
exec :v1:='TABLE';
select /*+ acstab2 bind_aware */ count(*) from acstab where object_type=:v1;

---首次执行完后cursor的is_bind_aware=true
select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /*+ acstab2 bind_aware */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /*+ acstab2 bind_aware */ count(*) from acstab where object_typ 24jkr79v4n1rj 0 1 Y Y Y 543 1 413337124
e=:v1

---第二次使用object_type='LOB'作为条件进行查询时能直接使用最适合的Range scan index，避免了先使用index full scan、之后那次执行再切换到Range scan index的冗余步骤
variable v1 varchar2(100);
exec :v1:='LOB';
select /*+ acstab2 bind_aware */ count(*) from acstab where object_type=:v1;

select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /*+ acstab2 bind_aware */%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS I I I BUFFER_GETS ROWS_PROCESSED PLAN_HASH_VALUE
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /*+ acstab2 bind_aware */ count(*) from acstab where object_typ 24jkr79v4n1rj 0 1 Y Y Y 543 1 413337124
e=:v1

select /*+ acstab2 bind_aware */ count(*) from acstab where object_typ 24jkr79v4n1rj 1 1 Y Y Y 3 1 3779426319
e=:v1

---这一次是cursor merge的效果，和/*+bind_aware */无关
variable v1 varchar2(100);
exec :v1:='QUEUE';
select /*+ acstab2 bind_aware */ count(*) from acstab where object_type=:v1;

select sql_text,sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,buffer_gets,rows_processed,plan_hash_value from v$sql where sql_text like 'select /*+ acstab2 bind_aware */%';
E
---------------------------------------------------------------------- ------------- ------------ ---------- - - - ----------- -------------- ---------------
select /*+ acstab2 bind_aware */ count(*) from acstab where object_typ 24jkr79v4n1rj 0 1 Y Y Y 543 1 413337124
e=:v1

select /*+ acstab2 bind_aware */ count(*) from acstab where object_typ 24jkr79v4n1rj 1 1 Y Y N 3 1 3779426319
e=:v1

select /*+ acstab2 bind_aware */ count(*) from acstab where object_typ 24jkr79v4n1rj 2 2 Y Y Y 6 2 3779426319
e=:v1

揭秘自适应游标共享技术(Adaptive Cursor Sharing)

热门文章

最新文章

相关电子书