揭祕自適應遊標共享技術(Adaptive Cursor Sharing)
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
說一下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 y="" n="">
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 y="" n="">
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
---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 y="" n=""> select /* buckcnt */ count(*) from buck1 where id<:v1 y="">
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
可以看到總共執行了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 from="" buck1="" where="" id="">，下面看一下select * from buck1 where id<:v1>
---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 y="" n="">
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 y="" n=""> select /* buckcnt */ * from buck1 where id<:v1 y="">
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>=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 y="" n="">
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 y="" n="">
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 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="">
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
當我們以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 y="" n="">
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 y="" n="">
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 y="" n="">
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 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="">
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
從上面的資訊中我們可以瞭解到這條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
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 y="" n="">
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 y="" n="">
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
---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 y="" n=""> select /* buckcnt */ count(*) from buck1 where id<:v1 y="">
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
可以看到總共執行了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 from="" buck1="" where="" id="">，下面看一下select * from buck1 where id<:v1>
---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 y="" n="">
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 y="" n=""> select /* buckcnt */ * from buck1 where id<:v1 y="">
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>=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 y="" n="">
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 y="" n="">
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 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="">
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
當我們以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 y="" n="">
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 y="" n="">
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 y="" n="">
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 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="" n=""> select /* buckcnt33 */ count(*) from buck2 where id<:v1 y="">
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
從上面的資訊中我們可以瞭解到這條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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-1384122/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 揭秘自適應遊標共享技術(Adaptive Cursor Sharing)APT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（上）OracleAPT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（下）OracleAPT
- 繫結變數之自適應遊標共享(Adaptive Cursor Sharing)變數APT
- Oracle遊標共享（Cursor Sharing）--常規遊標共享和自適應遊標共享（ACS）Oracle
- 11G Adaptive Cursor Sharing(ACS)自適應遊標測試APT
- Oracle自適應共享遊標Oracle
- Adaptive Cursor SharingAPT
- V$SQL_遊標、adaptive cursor sharing、bind peeking、直方圖的概念SQLAPT直方圖
- Adaptive Cursor Sharing（第二篇）APT
- Adaptive Cursor Sharing（第三篇）APT
- Adaptive Cursor Sharing（第四篇）APT
- Adaptive Cursor Sharing（第五篇）APT
- Adaptive Cursor Sharing （第一篇）APT
- Adaptive Cursor Sharing: Worked Example (Doc ID 836256.1)APT
- zt_Oracle9i,10g,11g 使用繫結變數的區別及與cursor_sharing的關係_自適應遊標共享Oracle變數
- 11G Adaptive Cursor Sharing(ACS)的研究APT
- 優化器革命之- adaptive cursor sharing （三）優化APT
- 優化器革命之- adaptive cursor sharing （二）優化APT
- 11GR1 新特性 adaptive cursor sharingAPT
- Oracle 11.1 自適應遊標Oracle
- 最佳化器革命之- adaptive cursor sharing （三）APT
- 最佳化器革命之- adaptive cursor sharing （四）APT
- 11G Adaptive Cursor Sharing(ACS)的研究 (轉)APT
- 驗證11g的adaptive cursor sharing功能APT
- 自適應遊標共享(ACS)與sql計劃管理(SPM)的相互影響SQL
- [20120327]Adaptive Cursor Sharing 的問題APT
- VMware的雲原生應用技術揭祕
- SQL 遊標cursorSQL
- 11gR2 新特性之（一）Adaptive Cursor Sharing（ACS）APT
- 揭祕TPM安全晶片技術及加密應用晶片加密
- 揭祕《Arduino技術內幕》UI
- 華章揭祕系列精品圖書（《Android應用開發揭祕》、《GWT揭祕》、《Spring技術內幕》）AndroidSpring
- Oracle 11g SQL效能的新特性（一）- Adaptive Cursor SharingOracleSQLAPT
- 【CURSOR】Oracle 遊標 （cursor）知識梳理Oracle
- 從共享遊標shared cursor角度看forall批量繫結
- 遊戲反外掛技術揭祕遊戲
- Taro 技術揭祕：taro-cli