關於該特性，其實並不是11gR2開始引入的，其實在11gR1就引入了，只不過其問題較多，
並未引起太多關注而已（bug不少）。該特性主要解決了哪些問題？
● data skew （資料傾斜）
● bind peeking （繫結變數窺視）– oracle 9i 引入
如何理解ACS？
ACS will allow multiple execution plans for a statement that use bind variables
ensuring that the best execution plan will be used for a specific value of the bind variable.
通俗的講，就是會根據繫結變數的值來智慧判斷選擇最優的執行計劃
例如：select * from tab_a where a=:x 可能存在多個不同的執行計劃
|
關於該特性也是透過幾個隱含引數來控制的，11gR2 預設為true，如下：
SQL> SHOW parameter _optimizer_adaptive
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _optimizer_adaptive_cursor_sharing BOOLEAN TRUE
SQL> SHOW parameter optim_peek
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _optim_peek_user_binds BOOLEAN TRUE
說明：
_optimizer_adaptive_cursor_sharing ==> ACS特性
_optim_peek_user_binds ==> 繫結變數窺視
'建立測試表'
SQL> CREATE TABLE ht1 AS SELECT owner,object_id,object_name FROM dba_objects; TABLE created.
SQL> SELECT COUNT(object_id) FROM ht1;
COUNT(OBJECT_ID) ---------------- 71878
SQL> SELECT MAX(object_id) FROM ht1;
MAX(OBJECT_ID) -------------- 73406
SQL> UPDATE ht1 SET object_id=100 WHERE object_id < 73405; 71876 ROWS updated.
SQL> commit;
Commit complete.
SQL> UPDATE ht1 SET object_id=100 WHERE object_id < 73000; 71679 ROWS updated.
SQL> commit;
Commit complete.
SQL> UPDATE ht1 SET object_id=1000 WHERE object_id > 73000 AND object_id < 73300; 150 ROWS updated.
SQL> commit;
Commit complete.
SQL> UPDATE ht1 SET object_id=10000 WHERE object_id > 73329; 34 ROWS updated.
SQL> commit;
Commit complete.
SQL> UPDATE ht1 SET object_id=10000 WHERE object_id > 70000; 15 ROWS updated.
SQL> commit;
Commit complete.
SQL> SELECT object_id,COUNT(*) FROM ht1 GROUP BY object_id;
OBJECT_ID COUNT(*) ---------- ---------- 100 71679 1000 150 10000 49
SQL> CREATE INDEX idx_id ON ht1(object_id); INDEX created.
SQL> EXEC dbms_stats.gather_table_stats(USER,'HT1',method_opt=>'for all columns size skewonly');
PL/SQL PROCEDURE successfully completed.
SQL> SELECT TABLE_NAME,COLUMN_NAME,DENSITY,HISTOGRAM 2 FROM user_tab_columns 3 WHERE TABLE_NAME='HT1';
TABLE_NAME COLUMN_NAME DENSITY HISTOGRAM ------------------------------ ------------------------------ ---------- --------------- HT1 OWNER 6.9461E-06 FREQUENCY
HT1 OBJECT_ID 6.9461E-06 FREQUENCY
HT1 OBJECT_NAME .000035426 HEIGHT BALANCED
SQL> ALTER system FLUSH shared_pool;
System altered.
SQL> var a NUMBER;
SQL> EXEC :a :=1000;
PL/SQL PROCEDURE successfully completed.
SQL> SELECT * FROM ht1 WHERE object_id =:a;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ----------------------------------- APEX_030200 1000 WWV_MIG_ACC_LOAD ... ... ... ... ... ... SYS 1000 WRH$_SQLSTAT_PK
SYS 1000 WRH$_SYSTEM_EVENT
150 ROWS selected.
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ SQL_ID 9zq6asm9yfrc9, child NUMBER 0 ------------------------------------- SELECT * FROM ht1 WHERE object_id =:a
Plan hash VALUE: 2446245938 -------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| HT1 | 223 | 7582 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ID | 223 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=:A) 19 ROWS selected.
SQL> SELECT hash_value FROM v$sql WHERE sql_id='9zq6asm9yfrc9';
HASH_VALUE ---------- 3555155337
SQL> SELECT CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS, 2 BUFFER_GETS/EXECUTIONS BG_PER_EX, 3 IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S 4 FROM v$sql 5 WHERE hash_value='&hash_value';
Enter VALUE FOR hash_value: 3555155337 CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX B B S
------------ --------------- ---------- ---------- - - - 0 2446245938 1 137 Y N Y
SQL> SELECT IS_OBSOLETE ,IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE,BIND_DATA 2 FROM v$sql 3 WHERE SQL_ID='9zq6asm9yfrc9';
I I I I BIND_DATA - - - - ------------------------------------------------------------ N Y N Y BEDA0B2001004DFE20CD000101C0021602C20B
SQL> SELECT * FROM v$sql_cs_selectivity WHERE sql_id='9zq6asm9yfrc9';
no ROWS selected
說明：
這裡有幾個欄位，需要解釋一下，如下：
IS_OBSOLETE ==> 是否廢棄（cursor被廢棄，起執行計劃可能發生變化，通常情況下，其child cursor太大了，該遊標將被obsolete）
IS_BIND_SENSITIVE ==> 為Y表示啟用了繫結變數窺視，SQL的執行計劃取決於變數值
IS_BIND_AWARE ==> 表示是否啟動extended cursor sharing
IS_SHAREABLE ==> 是否共享，如果不能共享，那麼該SQL被page OUT出shared pool。
關於 extended cursor sharing，有2個引數，如下：
SQL> SHOW parameter extended_cursor
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _optimizer_extended_cursor_sharing string UDO
_optimizer_extended_cursor_sharing_rel string SIMPLE
關於這2個隱含引數，還需要進一步研究。
SQL> var a NUMBER;
SQL> EXEC :a :=100;
PL/SQL PROCEDURE successfully completed.
SQL> SELECT * FROM ht1 WHERE object_id =:a;
OWNER OBJECT_ID OBJECT_NAME ------------------------------ ---------- ----------------------------------- ... ... ... ... ... ... ... ... ... ... ... ... APEX_030200 100 APEX_MIGRATION_ACC_RPTS
APEX_030200 100 APEX_MIGRATION_ACC_QUERIES
APEX_030200 100 APEX_MIGRATION_REV_FORMS
APEX_030200 100 APEX_MIGRATION_REV_RPTS
APEX_030200 100 APEX_MIGRATION_REV_QUERIES
71679 ROWS selected.
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------- SQL_ID 9zq6asm9yfrc9, child NUMBER 0 ------------------------------------- SELECT * FROM ht1 WHERE object_id =:a
Plan hash VALUE: 2446245938 -------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| HT1 | 223 | 7582 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ID | 223 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=:A) ###### 這裡居然是INDEX range scan ######
###### 由於id=100的選擇性非常差，此時走全表掃描才是正常的執行計劃 ######
19 ROWS selected.
SQL> SELECT CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS, 2 BUFFER_GETS/EXECUTIONS BG_PER_EX, 3 IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S 4 FROM v$sql 5 WHERE sql_id='9zq6asm9yfrc9';
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX B B S ------------ --------------- ---------- ---------- - - - 0 2446245938 2 5101 Y N Y
這裡EXECUTIONS為2，說明這次的執行計劃實際上是沿用的上次object_id為1000的執行計劃。
再次執行相同的繫結變數值：
SQL> EXEC :a :=100;
PL/SQL PROCEDURE successfully completed.
SQL> SELECT * FROM ht1 WHERE object_id =:a;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ----------------------------------- ... ... ... ... ... ... ... ... ... ... ... ... APEX_030200 100 APEX_MIGRATION_ACC_PROJECTS
APEX_030200 100 APEX_MIGRATION_ACC_TABLES
APEX_030200 100 APEX_MIGRATION_ACC_FORMS
APEX_030200 100 APEX_MIGRATION_ACC_RPTS
APEX_030200 100 APEX_MIGRATION_ACC_QUERIES
APEX_030200 100 APEX_MIGRATION_REV_FORMS
APEX_030200 100 APEX_MIGRATION_REV_RPTS
APEX_030200 100 APEX_MIGRATION_REV_QUERIES
71679 ROWS selected.
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID 9zq6asm9yfrc9, child NUMBER 1 ------------------------------------- SELECT * FROM ht1 WHERE object_id =:a
Plan hash VALUE: 3708914037 -------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 116 (100)| | |* 1 | TABLE ACCESS FULL| HT1 | 71590 | 2377K| 116 (1)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - FILTER("OBJECT_ID"=:A) ###### 此時的執行計劃就正確，變成了全表掃描 ######
18 ROWS selected.
SQL> SELECT CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS, 2 BUFFER_GETS/EXECUTIONS BG_PER_EX, 3 IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S 4 FROM v$sql 5 WHERE sql_id='9zq6asm9yfrc9';
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX B B S
------------ --------------- ---------- ---------- - - - 0 2446245938 2 5101 Y N Y 1 3708914037 1 5159 Y Y Y
這裡我們可以發現，該遊標多了一個child，child 1就是對於前面的全表掃描執行計劃，其執行次數為1. 如果我們再次執行相同的SQL，那麼child 1的executions 必然會增加1. 如下：
SQL> EXEC :a :=100;
PL/SQL PROCEDURE successfully completed.
SQL> SELECT * FROM ht1 WHERE object_id =:a;
OWNER OBJECT_ID OBJECT_NAME ------------------------------ ---------- ----------------------------------- ... ... ... ... ... ... ... ... ... ... ... ... APEX_030200 100 APEX_MIGRATION_REV_RPTS
APEX_030200 100 APEX_MIGRATION_REV_QUERIES
71679 ROWS selected.
SQL> SELECT CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS, 2 BUFFER_GETS/EXECUTIONS BG_PER_EX, 3 IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S 4 FROM v$sql 5 WHERE sql_id='9zq6asm9yfrc9';
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX B B S ------------ --------------- ---------- ---------- - - - 0 2446245938 2 5101 Y N Y 1 3708914037 2 5159 Y Y Y
SQL> SELECT CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH 2 FROM v$sql_cs_selectivity 3 WHERE hash_value='3555155337';
CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------------------------------------- ---------- ---------- ---------- 1 =A 0 0.896393 1.095591
|
在11.2的官方文件中，居然沒有v$sql_cs_selectivity的說明，oracle也太扯淡了。
為什麼說11gR2之前，這個新功能問題相對比較多多，metalink 搜尋v$sql_cs_selectivity，居然有3個跟這個新特性相關的bug。
Bug 7213010 Adaptive cursor sharing generates lots of child cursors --11.1.0.6
Bug 6644714 - High number of child cursors with adaptive cursor sharing --11.1.0.6
Bug 8491399 - Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype --11.1.0.7
意外的收穫是發現了一個查詢V$SQL_CS_SELECTIVITY的 bug，如下：
Bug 10058195 - V$SQL_CS_SELECTIVITY columns are padded with chr(0) characters
不過這個bug不影響資料庫正常使用。
補充：
跟11g自適應遊標共享功能相關的有幾個新的檢視，平時我們可以藉此來進行監控，如下：
V$SQL_CS_SELECTIVITY
V$SQL_CS_STATISTICS
V$SQL_CS_HISTOGRAM
關於這3個檢視，oracle metalink的解釋如下：
V$SQL_CS_SELECTIVITY exposes the valid selectivity ranges for a child cursor in extended
cursor sharing mode. A valid range consists of a low and high value
for each predicate containing binds. Each predicate's selectivity (with the current bind value) must
fall between the corresponding low and high values in order for the child cursor to be shared.
V$SQL_CS_STATISTICS contains the raw execution statistics used by the monitoring component
of adaptive cursor sharing. A sample of the executions is monitored.
This view exposes which executions were sampled, and what the statistics were for those
executions. The statistics are cumulative for each distinct set of bind values.
V$SQL_CS_HISTOGRAM summarizes the monitoring information stored by adaptive cursor
sharing. This information is used to decide whether to enable extended cursor sharing for a query. It
is stored in a histogram, whose bucket's contents are exposed by this view.
|
下面來查詢一下看看;
SQL> SELECT * FROM V$SQL_CS_SELECTIVITY;
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH -------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- 2998C51C 3555155337 9zq6asm9yfrc9 1 =A 0 0.896393 1.095591
SQL> SELECT * FROM V$SQL_CS_STATISTICS WHERE sql_id='9zq6asm9yfrc9';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME -------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ---------- 2998C51C 3555155337 9zq6asm9yfrc9 1 336594526 Y 1 71679 5159 0 2998C51C 3555155337 9zq6asm9yfrc9 0 3036353656 Y 1 300 137 0
SQL> SELECT * FROM V$SQL_CS_HISTOGRAM 2 WHERE sql_id='9zq6asm9yfrc9' 3 ORDER BY CHILD_NUMBER;
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 2998C51C 3555155337 9zq6asm9yfrc9 0 1 1 2998C51C 3555155337 9zq6asm9yfrc9 0 0 1 2998C51C 3555155337 9zq6asm9yfrc9 0 2 0 2998C51C 3555155337 9zq6asm9yfrc9 1 1 2 2998C51C 3555155337 9zq6asm9yfrc9 1 0 0 2998C51C 3555155337 9zq6asm9yfrc9 1 2 0 6 ROWS selected.
|
從上面我們就可以很得出如下的結論：
V$SQL_CS_SELECTIVITY 用於查詢cursor的最高值和最低值的選擇性，oracle也正是根據其選擇性來決定起執行計劃的，不過內部機制現
在我還無法得知，比如 object_id 有1000個值，不可能每次不同的繫結變數值，oracle都去生成一個執行計劃或產生一個child cursor，
那樣的話，代價就非常高了。– 這個需要進一步研究。
V$SQL_CS_STATISTICS 從上面的查詢，我們就可以看出，該檢視用於查詢每個child cursor的統計資訊，比如buffer gets。
其實，從這個我們也可以用來判斷sql的效率，這個不就是我們常說的邏輯讀嗎？
V$SQL_CS_HISTOGRAM 類似直方圖一樣，用於記錄cursor的執行次數，從上面的查詢，我們可以發現每個child cursor一共有3個bucket。
關於這裡的bucket，目前還不知道是不是就是固定的3個bucket。– 這裡也需要進一步研究證明。
另外如果修改了引數curso_sharing為similar或force的話，也可能會導致比較嚴重的後果，可能會出現大量的 mutex X waits for cursor等待。
故我們仍然建議設定為EXACT，從應用角度進行繫結變數。
既然我們說ACS功能很強悍，假如不想用這個功能呢，是否能關閉呢？ 回答是肯定的，透過如下的方式：
ALTER system SET "_optimizer_extended_cursor_sharing_rel"=NONE; ALTER system SET "_optimizer_extended_cursor_sharing"=NONE; ALTER system SET "_optimizer_adaptive_cursor_sharing"=FALSE;
|
另外我在閱讀metalink 文件Adaptive Cursor Sharing Overview [ID 740052.1] 的時候，還發現瞭如下的資訊：
If any of the following checks fail ECS will be disabled
- Extended cursor sharing is disabled
- The query has no binds
- Parallel query is used
- Certain parameters like ("bind peeking"=false) are set
- Hints are in use
- Outlines are being used
- It is a recursive query
- The number of binds in a given sql statement are greater than 14.
|
換句話說，就是ACS功能，在上面幾種情況下是起作用的。
摘自：%E6%96%B0%E7%89%B9%E6%80%A7%E4%B9%8B%EF%BC%88%E4%B8%80%EF%BC%89adaptive-cursor-sharing%EF%BC%88acs%EF%BC%89.html
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2124756/，如需轉載，請註明出處，否則將追究法律責任。