[20141213]11g ACS的一些問題3.txt
--11G下Adaptive Cursor Sharing簡稱ACS能很好的解決綁定變量窺視的帶來的問題，前一段時間看了2篇blog
<a href="https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/">https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/</a>
<a href="https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/">https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/</a>
--我以前也寫過一篇blog,連結如下：
<a href="http://blog.itpub.net/267265/viewspace-721817/">http://blog.itpub.net/267265/viewspace-721817/</a>
--如果綁定變量值出現傾斜，如果執行次數很大，很難改變執行計劃，當時提到最好的方法是加入提示/*+ BIND_AWARE */ .
--比如查詢status=:b1 ,status='N'的值很少，執行計劃走索引更佳，而status='Y'的值很多，執行計劃走全表掃描更佳。
--但是由于查詢以status='N'次數居多，查詢計劃幾乎很難改變。導緻當status='Y'的執行計劃依舊是走索引。
--作者最後給出二個結論或者是規律：
查詢v$sql_cs_histogram視圖，如果ROWS_PROCESSED存在某種規律：
規律1：
0 COUNT of BUCKET_ID 0 will be incremented
1000 COUNT of BUCKET_ID 1 will be incremented
ROWS_PROCESSED > 1e6 --> COUNT of BUCKET_ID 2 will be incremented
規律2：
1.僅僅兩個BUCKET_ID，count不等于0的情況下，BUCKET_ID貼近時count相等,下一次執行才會生成新的執行計劃(子光标)。
2.僅僅兩個BUCKET_ID，count不等于0的情況下，BUCKET_ID不貼近時, 大的count ceil(count/3)=小的count,下一次執行如果
大的count ceil(count/3)小的count，才會生成新的執行計劃(子光标)。
--我昨天在測試另外一些情況時，遇到一些問題，做一個記錄：
--如果我使用PL/SQL執行遇到一些奇怪的問題。
1.建立測試環境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t_acs(n1 number, vc2 varchar2(10));
BEGIN
for j in 1..1200150 loop
if j = 1 then
insert into t_acs values (j, 'j1');
elsif j>1 and j insert into t_acs values(j, 'j100');
elsif j>101 and j insert into t_acs values (j, 'j1000');
elsif j>10001 and j insert into t_acs values(j,'j10000');
else
insert into t_acs values(j, 'j>million');
end if;
end loop;
commit;
END;
/
create index t_acs_i1 on t_acs(vc2);
SCOTT@test> select vc2, count(1) from t_acs group by vc2 order by 2;
VC2 COUNT(1)
---------- ----------
j1 1
j100 100
j1000 1000
j10000 100000
j>million 1099049
--可以發現vc2的資料分布情況。建立直方圖：
dbms_stats.gather_table_stats
(user
,'t_acs'
,method_opt => 'for all columns size skewonly'
,estimate_percent => null
,cascade => true
,no_invalidate => false
);
--注：我做全統計，這樣統計比較準确。
--帶入不同的值，主要查詢v$sql_cs_statistics,v$sql_cs_histogram,v$sql_cs_selectivity三個視圖。
2.為了測試友善，編寫一個PLSQL腳本：
-- test acs1
-- 參數1查詢條件，參數2執行次數。
declare
m number :=0 ;
lvc varchar2(10) = '&1' ;
begin
for j in 1..&2
loop
select count(n1) into m from t_acs where vc2 = lvc;
end;
@acs1 j10000 20
@acs1 j>million 20
SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF85E260 4146042997 2488vrgvjz63p 0 0 0
00000000AF85E260 4146042997 2488vrgvjz63p 0 1 20
00000000AF85E260 4146042997 2488vrgvjz63p 0 2 20
SCOTT@test> @dpc 2488vrgvjz63p ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 2488vrgvjz63p, child number 0
-------------------------------------
SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1
Plan hash value: 3774726486
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
| 0 | SELECT STATEMENT | | | 569 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_ACS | 100K| 569 (1)|
|* 3 | INDEX RANGE SCAN | T_ACS_I1 | 100K| 293 (1)|
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=852): 'j10000'
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("VC2"=:B1)
--說明:sql_id='2488vrgvjz63p',注意plsql代碼會格式化sql語句，實際執行的sql語句是SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 ;
--可以發現執行計劃走索引，而實際上查詢vc2='j>million',走全表掃描更佳。繼續測試：
SCOTT@test> @acs1 j10000 1
PL/SQL procedure successfully completed.
--從這裡看count沒有變化。而實際上已經産生了子光标。
SCOTT@test> select sql_id,plan_hash_value,sql_text,executions from v$sql where sql_id='2488vrgvjz63p';
SQL_ID PLAN_HASH_VALUE SQL_TEXT EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------
2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 40
2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 1
SCOTT@test> @acs1 j>million 1
--從這裡看count沒有變化。
2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 2
2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 3
SCOTT@test> @acs1 j>million 18
2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 21
--可以發現執行計劃依舊沒有變化。
2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 22
SCOTT@test> @acs1 j>million 20
2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 42
--可以發現執行計劃依舊沒有變化。v$sql_cs_histogram的count也不變化。
SCOTT@test> @acs1 j>million 21
2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 63
--問題依舊。執行計劃使用索引。
--當查詢v$sql_cs_histogram的2個相近的BUCKET_ID如果count相等，如果查詢條件導緻oracle再分析，這個時候如果走索引，
--無論以後帶入條件如何，都選擇走索引，再次說明11g 目前的acs存在許多缺陷。而且奇怪的是v$sql_cs_histogram不會看到
--CHILD_NUMBER=1的情況，裡面的count不再變化，視乎這個時候關閉了acs，oracle的bug嗎？
--如果這樣執行呢？
alter system flush SHARED_POOL;
00000000BE5A05D8 4146042997 2488vrgvjz63p 0 0 0
00000000BE5A05D8 4146042997 2488vrgvjz63p 0 1 20
00000000BE5A05D8 4146042997 2488vrgvjz63p 0 2 20
SCOTT@test> select sql_id,plan_hash_value,sql_text,executions,child_number from v$sql where sql_id='2488vrgvjz63p';
SQL_ID PLAN_HASH_VALUE SQL_TEXT EXECUTIONS CHILD_NUMBER
------------- --------------- ------------------------------------------------------------ ---------- ------------
2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 40 0
@acs1 j>million 1
2488vrgvjz63p 535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 1 1
--執行計劃發生了變化(看PLAN_HASH_VALUE)。選擇的是全表掃描。如果這次帶入j1000參數情況如何呢？
2488vrgvjz63p 535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 2 1
00000000BE932468 4146042997 2488vrgvjz63p 0 0 0
00000000BE932468 4146042997 2488vrgvjz63p 0 1 20
00000000BE932468 4146042997 2488vrgvjz63p 0 2 20
--當查詢v$sql_cs_histogram的2個相近的BUCKET_ID如果count相等，如果查詢條件導緻oracle再分析，這個時候如果全表掃描，
--無論以後帶入條件如何，都選擇全表掃描。而且奇怪的是v$sql_cs_histogram不會看到CHILD_NUMBER=1的情況，裡面的count不再
--變化，視乎這個時候關閉了acs，oracle的bug嗎？
難道使用PL/SQL下有什麼不同嗎？
才用執行sql語句方式測試。
$ cat acs2.sql
-- test acs2
var B1 varchar2(10);
exec :B1 := '&1';
SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 ;
@acs1 j10000
@acs1 j>million
00000000AFC2D388 4146042997 2488vrgvjz63p 0 0 0
00000000AFC2D388 4146042997 2488vrgvjz63p 0 1 20
00000000AFC2D388 4146042997 2488vrgvjz63p 0 2 20
SCOTT@test> var B1 varchar2(10);
SCOTT@test> exec :B1 := 'j>million' ;
SCOTT@test> SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 ;
COUNT(N1)
----------
1099049
--分号前有空格。
00000000AFC2D388 4146042997 2488vrgvjz63p 1 0 0
00000000AFC2D388 4146042997 2488vrgvjz63p 1 1 0
00000000AFC2D388 4146042997 2488vrgvjz63p 1 2 1
6 rows selected.
--可以發現使用PLSQL測試與SQL語句的執行方式存在不同。v$sql_cs_histogram看到CHILD_NUMBER=1的情況，裡面的count有變化，生成
--的新執行計劃也發生了變化。
![linux-svn解除安裝與安裝[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)