這篇文章主要講解了“Adaptive Cursor Sharing分析”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Adaptive Cursor Sharing分析”吧！
成都創(chuàng)新互聯(lián)公司于2013年開始，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都做網(wǎng)站、網(wǎng)站建設(shè)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元永修做網(wǎng)站,已為上家服務(wù),為永修各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:028-86922220
ACS雖然可以解決綁定變量窺探引起的問題，但是它也存在著一些缺點(diǎn)：
1）一旦一個(gè)SQL被標(biāo)注為綁定敏感，優(yōu)化器就要監(jiān)視SQL語句處理的行數(shù)，一旦行數(shù)發(fā)生“巨變”，就要更新v$sql_cs_histogram視圖。但是這代價(jià)似乎并不大，因?yàn)関$sql_cs_histogram視圖只在SQL執(zhí)行完成后才會(huì)被更新，不占用解析時(shí)間，因此這個(gè)更新完全可以是異步的。
2）Cursor被標(biāo)注為bind aware后，在解析階段要窺探變量的值,計(jì)算謂詞的選擇率，計(jì)算后的值要與對(duì)應(yīng)的v$sql_cs_selectivity視圖中的值做比較，看是否已經(jīng)在已存的選擇率范圍內(nèi)，如果不在會(huì)發(fā)生硬解析。由于要窺探變量的值，計(jì)算選擇率，一定程度上加大了解析的時(shí)間。這里的解析，指的是軟解析。但是這個(gè)影響可能也沒有那么大，因?yàn)橐粋€(gè)數(shù)據(jù)庫(kù)中并不是所有的SQL都會(huì)被標(biāo)注為bind aware，只有操作的數(shù)據(jù)集發(fā)生過巨大變化的SQL才會(huì)被標(biāo)注為bind aware。
3）使用ACS存在一個(gè)不穩(wěn)定期，也就是SQL執(zhí)行計(jì)劃要經(jīng)歷先變?cè)阍僮兒玫倪^程。v$sql_cs_histogram視圖記錄了SQL處理的數(shù)據(jù)量，用3個(gè)bucket來表示，如果SQL處理的行數(shù)發(fā)生巨變，也就是說處理的行數(shù)散落在了至少2個(gè)桶內(nèi)，下一次解析時(shí)，就要窺探綁定變量的值，重新硬解析生成執(zhí)行計(jì)劃。
4）一旦SQL被刷出共享池，這個(gè)SQL還需要重復(fù)經(jīng)歷ACS不穩(wěn)定期到穩(wěn)定器的過程。
5）PL/SQL中存在bug如果不調(diào)整session_cached_cursors參數(shù)為0將不能使用到ACS特性。
在我所負(fù)責(zé)管理的生產(chǎn)環(huán)境下，ACS都是關(guān)閉的，雖然我本人也對(duì)ACS做過一些研究和測(cè)試，但是始終還是有點(diǎn)敬而遠(yuǎn)之。那有沒有什么辦法既能使用到ACS的特性，又能一定程度避免這些缺點(diǎn)呢？首先我們需要介紹一個(gè)hint-bind_aware。
bind_aware的用法和作用
使用了ACS的功能后，一個(gè)游標(biāo)從bind sensitive 到 bind aware，中間有著不穩(wěn)定期，如果在游標(biāo)中使用bind_aware hint后，將會(huì)使游標(biāo)的狀態(tài)直接進(jìn)入bind aware，而不會(huì)經(jīng)歷bind sensitive狀態(tài)。我們來通過一個(gè)例子看一看：
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 / ---------- ---------------- 49900 Active 100 Inactive |
上面的代碼本章已經(jīng)出現(xiàn)過幾次，主要作用是：創(chuàng)建了一張表，表上有一列STATUS有數(shù)據(jù)傾斜，列上創(chuàng)建了索引，并在這列上收集直方圖。我們來看看在對(duì)SQL增加bind aware的hint后，ACS的表現(xiàn)會(huì)是什么樣。
我們首先查詢STATUS為Inactive的情況，這個(gè)值在表里占少數(shù)。
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僅執(zhí)行了一次就已經(jīng)被標(biāo)注為bind aware，沒有經(jīng)歷不穩(wěn)定期。我們?cè)倏聪聢?zhí)行STATUS為Active時(shí)的表現(xiàn)：
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后，第一次執(zhí)行就已經(jīng)得到了正確的執(zhí)行計(jì)劃，在v$sql中也已經(jīng)新增了一個(gè)entry，用來記錄新產(chǎn)生的游標(biāo)的執(zhí)行計(jì)劃。因此使用bind aware這個(gè)hint后，游標(biāo)將不會(huì)經(jīng)歷不穩(wěn)定期，SQL每次解析的時(shí)候都要窺探綁定變量的值，然后計(jì)算選擇率，如果計(jì)算選擇率與現(xiàn)有的游標(biāo)的選擇率不符，就會(huì)基于窺探到的綁定變量的值硬解析重新產(chǎn)生了一個(gè)新的游標(biāo)。如果你確認(rèn)一個(gè)SQL需要使用ACS功能，但是又不想讓它經(jīng)歷不穩(wěn)定期，那么你可以通過bind aware這個(gè)hint做到這一點(diǎn)。還有著一些手段可以嘗試，例如我們可以關(guān)閉ACS的功能，對(duì)有需要的SQL單獨(dú)打開ACS的功能?？赡苁菑陌⒗镒鯠BA沿襲來的習(xí)慣，喜歡直接關(guān)閉綁定變量窺探，綁定變量窺探被關(guān)閉后，ACS也就自動(dòng)關(guān)閉了。然后對(duì)有需要使用ACS的SQL，通過增加hint，OPT_PARAM('_optim_peek_user_binds' 'true') bind_aware來使用到ACS的特性，OPT_PARAM('_optim_peek_user_binds' 'true')用來在SQL語句級(jí)別打開綁定變量窺探的功能。綁定變量窺探和ACS關(guān)閉后，就規(guī)避了上面提到的ACS的缺點(diǎn)。DBA可以有選擇性的對(duì)某些SQL使用ACS。當(dāng)然這個(gè)對(duì)DBA要求較高，需要了解應(yīng)用，了解表的數(shù)據(jù)分布特點(diǎn)，了解表上的SQL的查詢特點(diǎn)。有些大公司已經(jīng)配備了應(yīng)用DBA的角色，負(fù)責(zé)開發(fā)的SQL REVIEW等工作，可以在SQL REVIEW階段里DBA通過了解應(yīng)用的SQL，對(duì)有需要的SQL增加ACS功能。如果不能第一時(shí)間增加hint進(jìn)去，也可以通過sql profile，sql patch的方式在不修改SQL語句的情況下增加這些hint綁定到SQL語句上去。（SPM baseline無效在這里）。
如我們可以通過sql profile來對(duì)一個(gè)SQL增加ACS的功能：
SQL>show parameter binds
NAME TYPE VALUE ------------------------------------ ---------------------- ------------------- _optim_peek_user_binds boolean FALSE
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 |
關(guān)閉綁定變量窺探后，也就關(guān)閉了ACS的功能，SQL的bind sensitive語句被標(biāo)注為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（其他版本沒做測(cè)試），在session或system級(jí)關(guān)閉綁定變量窺探的情況下，如果使用了hint OPT_PARAM('_optim_peek_user_binds' 'true') bind_aware來使用ACS功能，每執(zhí)行一次SQL，就會(huì)在共享池中新生成一個(gè)執(zhí)行計(jì)劃，之前產(chǎn)生的計(jì)劃被標(biāo)注為不能共享，不能共享的原因是：user_bind_peek_mismatch。此BUG在12.0.1版本已經(jīng)被修復(fù)。因此如果使用筆者所說的方式，使用前一定要做好測(cè)試，防止產(chǎn)生過多的子游標(biāo)。如果對(duì)于有數(shù)據(jù)傾斜的列，唯一值非常少，可以考慮直接使用文本變量，放棄綁定變量的使用。
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 |
如何關(guān)閉ACS的特性：
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
如果你的系統(tǒng)關(guān)閉了綁定變量窺探的功能也會(huì)自動(dòng)關(guān)閉ACS。
alter system set "_optim_peek_user_binds"=false scope=both;
感謝各位的閱讀，以上就是“Adaptive Cursor Sharing分析”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)Adaptive Cursor Sharing分析這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
重慶分公司
重慶分公司
