最佳化器革命之- adaptive cursor sharing （四）
請先閱讀最佳化器革命之- adaptive cursor sharing （一）
http://blog.itpub.net/22034023/viewspace-1218980/
請先閱讀最佳化器革命之- adaptive cursor sharing （二）
http://blog.itpub.net/22034023/viewspace-1222110/
請先閱讀最佳化器革命之- adaptive cursor sharing （三）
http://blog.itpub.net/22034023/viewspace-1222146/
從上面的介紹我們可以看出，ACS雖然可以解決繫結變數窺探引起的問題，更好程度的解決繫結值資料傾斜導致的問題，但是它也存在著一些缺點：
1）一旦一個SQL被標註為繫結敏感，最佳化器就要監視SQL語句處理的行數，一旦行數發生“鉅變”，就要更新v$sql_cs_histogram檢視。
2）在被標註為bind aware後，在解析階段要窺探變數的值,計算謂詞的選擇率，計算後的值要與v$sql_cs_selectivity檢視中的值做比較，看是否在已存的選擇率範圍內。一定程度上加大瞭解析的時間。這裡的解析，指的是軟解析。
3）使用ACS存在一個不穩定期，也就是SQL執行計劃一定先變糟被最佳化器識別到後，再次執行才能產生正確的執行計劃。
4）一旦SQL被刷出共享池，這個SQL還需要重複經歷ACS不穩定期到穩定器的過程。
ACS最佳實踐（很可惜有個BUG，後面會講到）
在我所負責管理的生產環境下，ACS都是關閉的，雖然我本人也對ACS做過一些研究和測試，但是就像對上面說的那些缺陷，始終還是有點敬而遠之。那有沒有什麼辦法既能使用到ACS的特性，又能一定程度避免這些缺點呢？首先我們需要介紹一個hint-bind_aware。
bind_aware的用法和作用
使用了ACS的功能後，一個遊標從bind sensitive 到 bind aware，中間有著不穩定期，如果在遊標中使用bind_aware hint後，將會使遊標的狀態直接進入bind aware，而不會經歷bind sensitive狀態。我們來透過一個例子看一看：
CREATE TABLE test
AS
SELECT ROWNUM id,
DBMS_RANDOM.STRING('A', 12) name,
DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status
FROM all_objects
WHERE ROWNUM <= 50000;
CREATE INDEX test_id_ind ON test(status);
EXEC dbms_stats.gather_table_stats(user, 'test', method_opt => 'for columns status size 254', cascade => true);
SELECT COUNT (*) cnt, status
FROM test
GROUP BY status
/
CNT STATUS
---------- ----------------
14862 Active
29 Inactive
上面的程式碼本章已經出現過幾次，主要作用是：建立了一張表，表上有一列STATUS有資料傾斜，在這列上收集直方圖。
我們來看看在對SQL增加bind aware的hint後，ACS的表現會是什麼樣。
我們首先查詢STATUS為Inactive的情況，這個值在表裡佔少數。
var a varchar2(100)
exec :a :='Inactive';
select /*+ bind_aware */ count(name) from test where status=:a;
COUNT(NAME)
-----------
29
test@DLSP>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 | 24 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 29 | 696 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 29 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STATUS"=:A)
test@DLSP>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 31 Y Y
從v$sql的is_bind_aware輸出為Y可以看出，SQL僅執行了一次就已經被標註為bind aware，沒有經歷不穩定期。我們再看下執行STATUS為Active時的表現：
test@DLSP>exec :a :='Active'
PL/SQL procedure successfully completed.
test@DLSP>select /*+ bind_aware */ count(name) from test where status=:a;
COUNT(NAME)
-----------
14862
test@DLSP>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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 14862 | 348K| 17 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"=:A)
test@DLSP>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 31 Y Y
1 1 63 Y Y
非常棒！我們更換繫結變數的值為Active後，第一次執行就已經得到了正確的執行計劃，在v$sql中也已經新增了一個entry，用來記錄新產生的遊標的執行計劃。
因此我想說什麼？
我們可以關閉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階段裡瞭解應用的SQL，對有需要的SQL增加ACS功能。如果不能第一時間增加hint進去，也可以透過sql profile，sql patch的方式在不修改SQL語句的情況下增加一些hint繫結到SQL語句上去。（SPM baseline無效在這裡）。
如我們可以透過sql profile來對一個SQL增加ACS的功能：
test@DLSP>show parameter binds
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
_optim_peek_user_binds boolean FALSE
test@DLSP>alter system flush shared_pool;
System altered.
test@DLSP>select count(name) from test where status=:a;
test@DLSP>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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 7446 | 174K| 17 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"=:A)
test@DLSP>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 113 N N
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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 7446 | 174K| 17 (0)| 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.
test@DLSP>select count(name) from test where status=:a;
COUNT(NAME)
-----------
14862
test@DLSP>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 123 Y Y
test@DLSP>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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 14862 | 348K| 17 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"=:A)
Note
-----
- SQL profile profile_7yjf9wt1rt8a6_dwrose used for this statement
test@DLSP>exec :a :='Inactive'
PL/SQL procedure successfully completed.
test@DLSP>select count(name) from test where status=:a;
COUNT(NAME)
-----------
29
test@DLSP>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7yjf9wt1rt8a6, child number 16
--------------------------------------
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 | 24 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 29 | 696 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 29 | | 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
但是可惜的是，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版本已經被修復。因此如果使用筆者所說的方式，使用前一定要做好測試。如果對於有資料傾斜的列，唯一值非常少，可以考慮直接使用文字變數，放棄繫結變數的使用。
sys@DLSP>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
6 Y
7 Y
8 Y
http://blog.itpub.net/22034023/viewspace-1218980/
請先閱讀最佳化器革命之- adaptive cursor sharing （二）
http://blog.itpub.net/22034023/viewspace-1222110/
請先閱讀最佳化器革命之- adaptive cursor sharing （三）
http://blog.itpub.net/22034023/viewspace-1222146/
從上面的介紹我們可以看出，ACS雖然可以解決繫結變數窺探引起的問題，更好程度的解決繫結值資料傾斜導致的問題，但是它也存在著一些缺點：
1）一旦一個SQL被標註為繫結敏感，最佳化器就要監視SQL語句處理的行數，一旦行數發生“鉅變”，就要更新v$sql_cs_histogram檢視。
2）在被標註為bind aware後，在解析階段要窺探變數的值,計算謂詞的選擇率，計算後的值要與v$sql_cs_selectivity檢視中的值做比較，看是否在已存的選擇率範圍內。一定程度上加大瞭解析的時間。這裡的解析，指的是軟解析。
3）使用ACS存在一個不穩定期，也就是SQL執行計劃一定先變糟被最佳化器識別到後，再次執行才能產生正確的執行計劃。
4）一旦SQL被刷出共享池，這個SQL還需要重複經歷ACS不穩定期到穩定器的過程。
ACS最佳實踐（很可惜有個BUG，後面會講到）
在我所負責管理的生產環境下，ACS都是關閉的，雖然我本人也對ACS做過一些研究和測試，但是就像對上面說的那些缺陷，始終還是有點敬而遠之。那有沒有什麼辦法既能使用到ACS的特性，又能一定程度避免這些缺點呢？首先我們需要介紹一個hint-bind_aware。
bind_aware的用法和作用
使用了ACS的功能後，一個遊標從bind sensitive 到 bind aware，中間有著不穩定期，如果在遊標中使用bind_aware hint後，將會使遊標的狀態直接進入bind aware，而不會經歷bind sensitive狀態。我們來透過一個例子看一看：
CREATE TABLE test
AS
SELECT ROWNUM id,
DBMS_RANDOM.STRING('A', 12) name,
DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status
FROM all_objects
WHERE ROWNUM <= 50000;
CREATE INDEX test_id_ind ON test(status);
EXEC dbms_stats.gather_table_stats(user, 'test', method_opt => 'for columns status size 254', cascade => true);
SELECT COUNT (*) cnt, status
FROM test
GROUP BY status
/
CNT STATUS
---------- ----------------
14862 Active
29 Inactive
上面的程式碼本章已經出現過幾次，主要作用是：建立了一張表，表上有一列STATUS有資料傾斜，在這列上收集直方圖。
我們來看看在對SQL增加bind aware的hint後，ACS的表現會是什麼樣。
我們首先查詢STATUS為Inactive的情況，這個值在表裡佔少數。
var a varchar2(100)
exec :a :='Inactive';
select /*+ bind_aware */ count(name) from test where status=:a;
COUNT(NAME)
-----------
29
test@DLSP>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 | 24 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 29 | 696 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 29 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STATUS"=:A)
test@DLSP>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 31 Y Y
從v$sql的is_bind_aware輸出為Y可以看出，SQL僅執行了一次就已經被標註為bind aware，沒有經歷不穩定期。我們再看下執行STATUS為Active時的表現：
test@DLSP>exec :a :='Active'
PL/SQL procedure successfully completed.
test@DLSP>select /*+ bind_aware */ count(name) from test where status=:a;
COUNT(NAME)
-----------
14862
test@DLSP>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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 14862 | 348K| 17 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"=:A)
test@DLSP>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 31 Y Y
1 1 63 Y Y
非常棒！我們更換繫結變數的值為Active後，第一次執行就已經得到了正確的執行計劃，在v$sql中也已經新增了一個entry，用來記錄新產生的遊標的執行計劃。
因此我想說什麼？
我們可以關閉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階段裡瞭解應用的SQL，對有需要的SQL增加ACS功能。如果不能第一時間增加hint進去，也可以透過sql profile，sql patch的方式在不修改SQL語句的情況下增加一些hint繫結到SQL語句上去。（SPM baseline無效在這裡）。
如我們可以透過sql profile來對一個SQL增加ACS的功能：
test@DLSP>show parameter binds
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
_optim_peek_user_binds boolean FALSE
test@DLSP>alter system flush shared_pool;
System altered.
test@DLSP>select count(name) from test where status=:a;
test@DLSP>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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 7446 | 174K| 17 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"=:A)
test@DLSP>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 113 N N
關閉繫結變數窺探後，也就關閉了ACS的功能，SQL的bind sensitive語句被標註為N。我們透過sql profile增加hint看看。
test@DLSP>@profileEnter 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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 7446 | 174K| 17 (0)| 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.
test@DLSP>select count(name) from test where status=:a;
COUNT(NAME)
-----------
14862
test@DLSP>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 123 Y Y
test@DLSP>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 | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | TABLE ACCESS FULL| TEST | 14862 | 348K| 17 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATUS"=:A)
Note
-----
- SQL profile profile_7yjf9wt1rt8a6_dwrose used for this statement
test@DLSP>exec :a :='Inactive'
PL/SQL procedure successfully completed.
test@DLSP>select count(name) from test where status=:a;
COUNT(NAME)
-----------
29
test@DLSP>select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7yjf9wt1rt8a6, child number 16
--------------------------------------
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 | 24 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 29 | 696 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 29 | | 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
但是可惜的是，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版本已經被修復。因此如果使用筆者所說的方式，使用前一定要做好測試。如果對於有資料傾斜的列，唯一值非常少，可以考慮直接使用文字變數，放棄繫結變數的使用。
sys@DLSP>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
6 Y
7 Y
8 Y
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1349507/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 最佳化器革命之- adaptive cursor sharing （三）APT
- 優化器革命之- adaptive cursor sharing （三）優化APT
- 優化器革命之- adaptive cursor sharing （二）優化APT
- Adaptive Cursor Sharing（第四篇）APT
- Adaptive Cursor SharingAPT
- oracle11g中SQL最佳化新特性之Adaptive Cursor Sharing (ACS)OracleSQLAPT
- Adaptive Cursor Sharing（第二篇）APT
- Adaptive Cursor Sharing（第三篇）APT
- Adaptive Cursor Sharing（第五篇）APT
- Adaptive Cursor Sharing （第一篇）APT
- Adaptive Cursor Sharing: Worked Example (Doc ID 836256.1)APT
- 11gR2 新特性之（一）Adaptive Cursor Sharing（ACS）APT
- 11G Adaptive Cursor Sharing(ACS)的研究APT
- 11GR1 新特性 adaptive cursor sharingAPT
- 11G Adaptive Cursor Sharing(ACS)的研究 (轉)APT
- 驗證11g的adaptive cursor sharing功能APT
- 繫結變數之自適應遊標共享(Adaptive Cursor Sharing)變數APT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（上）OracleAPT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（下）OracleAPT
- [20120327]Adaptive Cursor Sharing 的問題APT
- 揭秘自適應遊標共享技術(Adaptive Cursor Sharing)APT
- Oracle 11g SQL效能的新特性（一）- Adaptive Cursor SharingOracleSQLAPT
- 揭祕自適應遊標共享技術(Adaptive Cursor Sharing)APT
- [20221227]Adaptive Cursor Sharing & 直方圖.txtAPT直方圖
- 11G Adaptive Cursor Sharing(ACS)自適應遊標測試APT
- 繫結變數的正確使用，bind peeking，Adaptive Cursor Sharing變數APT
- 【DBA】Oracle 11g 針對SQL效能的新特性（一）- Adaptive Cursor SharingOracleSQLAPT
- V$SQL_遊標、adaptive cursor sharing、bind peeking、直方圖的概念SQLAPT直方圖
- [20120220]Adaptive Cursor Sharing 與hints.txtAPT
- CURSOR_SHARING=SIMILARMILA
- Postgresql的CURSOR SHARINGSQL
- oracle cursor_sharing [轉]Oracle
- Cursor_sharing，Histogram，Analyze之間的關係Histogram
- Cursor_sharing，Histogram，Analyze之間的關係（轉）Histogram
- 優化器革命之-Dynamic Sampling（四）優化
- 關於 cursor_sharing = similarMILA
- oracle引數-cursor_sharingOracle
- cursor_sharing=similar深度剖析MILA