共計 10892 個字符，預計需要花費 28 分鐘才能閱讀完成。
這篇文章主要講解了“Adaptive Cursor Sharing 分析”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“Adaptive Cursor Sharing 分析”吧！
ACS 最佳實踐
ACS 雖然可以解決綁定變量窺探引起的問題，但是它也存在著一些缺點：
1）一旦一個 SQL 被標注為綁定敏感，優化器就要監視 SQL 語句處理的行數，一旦行數發生“巨變”，就要更新 v$sql_cs_histogram 視圖。但是這代價似乎并不大，因為 v$sql_cs_histogram 視圖只在 SQL 執行完成后才會被更新，不占用解析時間，因此這個更新完全可以是異步的。
2）Cursor 被標注為 bind aware 后，在解析階段要窺探變量的值, 計算謂詞的選擇率，計算后的值要與對應的 v$sql_cs_selectivity 視圖中的值做比較，看是否已經在已存的選擇率范圍內，如果不在會發生硬解析。由于要窺探變量的值，計算選擇率，一定程度上加大了解析的時間。這里的解析，指的是軟解析。但是這個影響可能也沒有那么大，因為一個數據庫中并不是所有的 SQL 都會被標注為 bind aware，只有操作的數據集發生過巨大變化的 SQL 才會被標注為 bind aware。
3）使用 ACS 存在一個不穩定期，也就是 SQL 執行計劃要經歷先變糟再變好的過程。v$sql_cs_histogram 視圖記錄了 SQL 處理的數據量，用 3 個 bucket 來表示，如果 SQL 處理的行數發生巨變，也就是說處理的行數散落在了至少 2 個桶內，下一次解析時，就要窺探綁定變量的值，重新硬解析生成執行計劃。
4）一旦 SQL 被刷出共享池，這個 SQL 還需要重復經歷 ACS 不穩定期到穩定器的過程。
5）PL/SQL 中存在 bug 如果不調整 session_cached_cursors 參數為 0 將不能使用到 ACS 特性。
在我所負責管理的生產環境下，ACS 都是關閉的，雖然我本人也對 ACS 做過一些研究和測試，但是始終還是有點敬而遠之。那有沒有什么辦法既能使用到 ACS 的特性，又能一定程度避免這些缺點呢？首先我們需要介紹一個 hint-bind_aware。
bind_aware 的用法和作用
使用了 ACS 的功能后，一個游標從 bind sensitive 到 bind aware，中間有著不穩定期，如果在游標中使用 bind_aware hint 后，將會使游標的狀態直接進入 bind aware，而不會經歷 bind sensitive 狀態。我們來通過一個例子看一看：
SQL CREATE TABLE test
2 AS
3 SELECT ROWNUM id,
4 DBMS_RANDOM.STRING(A , 12) name,
5 DECODE(MOD(ROWNUM, 500), 0, Inactive , Active ) status
6 FROM all_objects
7 WHERE ROWNUM = 50000;
Table created.
SQL CREATE INDEX test_id_ind ON test(status);
Index created.
SQL begin
2 dbms_stats.gather_table_stats(user,
3 test ,
4 method_opt = for columns status size 254 ,
5 cascade = true);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL SELECT COUNT (*) cnt, status
2 FROM test
3 GROUP BY status
4 /
CNT STATUS
———- —————-
49900 Active
100 Inactive
上面的代碼本章已經出現過幾次，主要作用是：創建了一張表，表上有一列 STATUS 有數據傾斜，列上創建了索引，并在這列上收集直方圖。我們來看看在對 SQL 增加 bind aware 的 hint 后，ACS 的表現會是什么樣。
我們首先查詢 STATUS 為 Inactive 的情況，這個值在表里占少數。
SQL alter system flush shared_pool;
System altered.
SQL var a varchar2(100)
SQL exec :a := Inactive
PL/SQL procedure successfully completed.
SQL
SQL select /*+ bind_aware */ count(name) from test where status=:a;
COUNT(NAME)
———–
100
SQL select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT
——————————————————————————————-
SQL_ID a5fy4g63j8vzr, child number 0
————————————-
select /*+ bind_aware */ count(name) from test where status=:a
Plan hash value: 2948918962
——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 133 | 3325 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 133 | | 1 (0)| 00:00:01 |
——————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
3 – access(STATUS =:A)
SQL SELECT child_number, executions, buffer_gets, is_bind_sensitive,
2 is_bind_aware
3 FROM v$sql
4 WHERE sql_id= a5fy4g63j8vzr
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
———— ———- ———– — —
0 1 254 Y Y
從 v$sql 的 is_bind_aware 輸出為 Y 可以看出，SQL 僅執行了一次就已經被標注為 bind aware，沒有經歷不穩定期。我們再看下執行 STATUS 為 Active 時的表現：
SQL exec :a := Active
PL/SQL procedure successfully completed.
SQL select /*+ bind_aware */ count(name) from test where status=:a;
COUNT(NAME)
———–
49900
SQL select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT
——————————————————————————-
SQL_ID a5fy4g63j8vzr, child number 1
————————————-
select /*+ bind_aware */ count(name) from test where status=:a
Plan hash value: 1950795681
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 51 (100)| |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TEST | 49862 | 1217K| 51 (2)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter(STATUS =:A)
19 rows selected.
SQL SELECT child_number, executions, buffer_gets, is_bind_sensitive,
2 is_bind_aware
3 FROM v$sql
4 WHERE sql_id= a5fy4g63j8vzr
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
———— ———- ———– — —
0 1 1486 Y Y
1 1 210 Y Y
非常棒！我們更換綁定變量的值為 Active 后，第一次執行就已經得到了正確的執行計劃，在 v$sql 中也已經新增了一個 entry，用來記錄新產生的游標的執行計劃。因此使用 bind aware 這個 hint 后，游標將不會經歷不穩定期，SQL 每次解析的時候都要窺探綁定變量的值，然后計算選擇率，如果計算選擇率與現有的游標的選擇率不符，就會基于窺探到的綁定變量的值硬解析重新產生了一個新的游標。如果你確認一個 SQL 需要使用 ACS 功能，但是又不想讓它經歷不穩定期，那么你可以通過 bind aware 這個 hint 做到這一點。還有著一些手段可以嘗試，例如我們可以關閉 ACS 的功能，對有需要的 SQL 單獨打開 ACS 的功能。可能是從阿里做 DBA 沿襲來的習慣，喜歡直接關閉綁定變量窺探，綁定變量窺探被關閉后，ACS 也就自動關閉了。然后對有需要使用 ACS 的 SQL，通過增加 hint，OPT_PARAM(_optim_peek_user_binds true) bind_aware 來使用到 ACS 的特性，OPT_PARAM(_optim_peek_user_binds true) 用來在 SQL 語句級別打開綁定變量窺探的功能。綁定變量窺探和 ACS 關閉后，就規避了上面提到的 ACS 的缺點。DBA 可以有選擇性的對某些 SQL 使用 ACS。當然這個對 DBA 要求較高，需要了解應用，了解表的數據分布特點，了解表上的 SQL 的查詢特點。有些大公司已經配備了應用 DBA 的角色，負責開發的 SQL REVIEW 等工作，可以在 SQL REVIEW 階段里 DBA 通過了解應用的 SQL，對有需要的 SQL 增加 ACS 功能。如果不能第一時間增加 hint 進去，也可以通過 sql profile，sql patch 的方式在不修改 SQL 語句的情況下增加這些 hint 綁定到 SQL 語句上去。（SPM baseline 無效在這里）。
如我們可以通過 sql profile 來對一個 SQL 增加 ACS 的功能：
SQL show parameter binds
NAME TYPE VALUE
———————————— ———————- ——————-
_optim_peek_user_binds boolean FALSE
SQL var a varchar2(100)
SQL exec :a := Active
PL/SQL procedure successfully completed.
SQL select count(name) from test where status=:a;
COUNT(NAME)
———–
49900
SQL select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT
——————————————————————————-
SQL_ID 7yjf9wt1rt8a6, child number 0
————————————-
select count(name) from test where status=:a
Plan hash value: 1950795681
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 51 (100)| |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TEST | 25000 | 610K| 51 (2)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter(STATUS =:A)
SQL SELECT child_number, executions, buffer_gets, is_bind_sensitive,
2 is_bind_aware
3 FROM v$sql
4 WHERE sql_id= 7yjf9wt1rt8a6
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
———— ———- ———– — —
0 2 540 N N
關閉綁定變量窺探后，也就關閉了 ACS 的功能，SQL 的 bind sensitive 語句被標注為 N。我們通過 sql profile 增加 hint 看看。
SQL @profile
Enter value for sql_id: 7yjf9wt1rt8a6
PLAN_TABLE_OUTPUT
——————————————————————————-
SQL_ID 7yjf9wt1rt8a6, child number 0
————————————-
select count(name) from test where status=:a
Plan hash value: 1950795681
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 51 (100)| |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TEST | 25000 | 610K| 51 (2)| 00:00:01 |
—————————————————————————
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(11.2.0.3)
DB_VERSION(11.2.0.3)
OPT_PARAM(_optim_peek_user_binds false)
OPT_PARAM(_optimizer_skip_scan_enabled false)
ALL_ROWS
OUTLINE_LEAF(@ SEL$1)
FULL(@ SEL$1 TEST @ SEL$1)
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
—————————————————
2 – filter(STATUS =:A)
35 rows selected.
Enter value for hint_text: OPT_PARAM(_optim_peek_user_binds true) bind_aware
Profile profile_7yjf9wt1rt8a6_dwrose created.
SQL select count(name) from test where status=:a;
COUNT(NAME)
———–
49900
1 row selected.
SQL SELECT child_number, executions, buffer_gets, is_bind_sensitive,
2 is_bind_aware
3 FROM v$sql
4 WHERE sql_id= 7yjf9wt1rt8a6
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
———— ———- ———– — —
0 1 270 Y Y
1 row selected.
SQL select * from table(dbms_xplan.display_cursor( 7yjf9wt1rt8a6 ,null));
PLAN_TABLE_OUTPUT
——————————————————————————-
SQL_ID 7yjf9wt1rt8a6, child number 0
————————————-
select count(name) from test where status=:a
Plan hash value: 1950795681
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 51 (100)| |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| TEST | 49862 | 1217K| 51 (2)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter(STATUS =:A)
Note
—–
– SQL profile profile_7yjf9wt1rt8a6_dwrose used for this statement
23 rows selected.
SQL exec :a := Inactive
PL/SQL procedure successfully completed.
SQL select count(name) from test where status=:a;
COUNT(NAME)
———–
100
1 row selected.
SQL select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT
——————————————————————————-
SQL_ID 7yjf9wt1rt8a6, child number 5
————————————-
select count(name) from test where status=:a
Plan hash value: 2948918962
——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 133 | 3325 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 133 | | 1 (0)| 00:00:01 |
——————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
3 – access(STATUS =:A)
Note
—–
– SQL profile profile_7yjf9wt1rt8a6_dwrose used for this statement
24 rows selected.
但是可惜的是，11.2.0.3 版本存在 BUG（其他版本沒做測試），在 session 或 system 級關閉綁定變量窺探的情況下，如果使用了 hint OPT_PARAM(_optim_peek_user_binds true) bind_aware 來使用 ACS 功能，每執行一次 SQL，就會在共享池中新生成一個執行計劃，之前產生的計劃被標注為不能共享，不能共享的原因是：user_bind_peek_mismatch。此 BUG 在 12.0.1 版本已經被修復。因此如果使用筆者所說的方式，使用前一定要做好測試，防止產生過多的子游標。如果對于有數據傾斜的列，唯一值非常少，可以考慮直接使用文本變量，放棄綁定變量的使用。
SQL select child_number,user_bind_peek_mismatch from v$sql_shared_cursor where sql_id= 7yjf9wt1rt8a6
CHILD_NUMBER US
———— —
0 N
1 Y
2 Y
3 Y
4 Y
5 Y
如何關閉 ACS 的特性：
alter system set _optimizer_extended_cursor_sharing_rel =none scope=both;
如果你的系統關閉了綁定變量窺探的功能也會自動關閉 ACS。
alter system set _optim_peek_user_binds =false scope=both;
感謝各位的閱讀，以上就是“Adaptive Cursor Sharing 分析”的內容了，經過本文的學習后，相信大家對 Adaptive Cursor Sharing 分析這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！
