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

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

| 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（第五篇）

ACS最佳實踐

相關文章