oracle exacc,【学习笔记】Oracle 11GR2新特性Adaptive Cursor Sharing（ACS）
天萃荷凈
Oracle研究中心學習筆記：分享一篇關于Oracle 11.2.0.1 11Gr2數據庫最新版本中最新特性Adaptive Cursor Sharing(ACS)深入研究筆記。
本站文章除注明轉載外，均為本站原創： 轉載自love wife & love life —Roger 的Oracle技術博客
本文鏈接地址: 11gR2 新特性之(一)Adaptive Cursor Sharing(ACS)
關于該特性，其實并不是11gR2開始引入的，其實在11gR1就引入了，只不過其問題較多，
并未引起太多關注而已(bug不少)。該特性主要解決了哪些問題？
● data skew (數據傾斜)
● bind peeking (綁定變量窺視)– oracle 9i 引入
1.如何理解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???????????? ==> 綁定變量窺視
2.創建測試表
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
3.測試說明：
這里有幾個字段，需要解釋一下，如下：
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. Ahttp://www.oracleplus.net 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.
4.如下結論
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功能，在上面幾種情況下是起作用的。
--------------------------------------ORACLE-DBA----------------------------------------
最權威、專業的Oracle案例資源匯總之【學習筆記】Oracle 11GR2新特性Adaptive Cursor Sharing(ACS)
總結
以上是生活随笔為你收集整理的oracle exacc,【学习笔记】Oracle 11GR2新特性Adaptive Cursor Sharing（ACS）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 斗鱼直播链接提取
- 下一篇： sql server使用杂记