Adapter 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
下面还有。。。。。
221
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
