[20141116]11g ACS的一些问题2.txt.txt
--11G下Adaptive Cursor Sharing简称ACS能很好的解决绑定变量窥视的带来的问题，昨天看了2篇blog
https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/
https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/
--我以前也写过一篇blog,链接如下：
http://blog.itpub.net/267265/viewspace-721817/
--如果绑定变量值出现倾斜，如果执行次数很大，很难改变执行计划，当时提到最好的方法是加入提示/*+ BIND_AWARE */ .
--比如查询status=:b1 ,status='N'的值很少，执行计划走索引更佳，而status='Y'的值很多，执行计划走全表扫描更佳。
--但是由于查询以status='N'次数居多，查询计划几乎很难改变。导致当status='Y'的执行计划依旧是走索引。
--作者最后给出一个结论，或者是规律：
查询v$sql_cs_histogram视图，如果ROWS_PROCESSED存在某种规律：
0 < ROWS_PROCESSED <= 1000 --> COUNT of BUCKET_ID 0 will be incremented
1000 < ROWS_PROCESSED <= 1e6 --> COUNT of BUCKET_ID 1 will be incremented
ROWS_PROCESSED > 1e6 --> COUNT of BUCKET_ID 2 will be incremented
--我自己也做了测试，链接如下：
--下面再重复作者的测试，看看执行计划如何改变的。
1.建立测试环境:
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<=101 then
insert into t_acs values(j, 'j100');
elsif j>101 and j<=1101 then
insert into t_acs values (j, 'j1000');
elsif j>10001 and j<= 110001 then
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);
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的数据分布情况。建立直方图：
BEGIN
dbms_stats.gather_table_stats
(user
,'t_acs'
,method_opt => 'for all columns size skewonly'
,estimate_percent => null
,cascade => true
,no_invalidate => false
);
END;
/
--注：我做全统计，这样统计比较准确。
--带入不同的值，主要查询v$sql_cs_statistics,v$sql_cs_histogram,v$sql_cs_selectivity三个视图。
2.测试1：
column address noprint
var lvc varchar2(10);
exec :lvc := 'j100';
SCOTT@test> select count(n1) from t_acs where vc2 = :lvc;
COUNT(N1)
----------
100
--说明:我修改了一下执行语句，他原来执行的是select count(1) from t_acs where vc2 = :lvc;应该不影响结论。
--这样sql_id='0rf89th8fvm4n'.
SCOTT@test> select * from v$sql_cs_statistics where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
284019860 0rf89th8fvm4n 0 1056299594 Y 1 201 55 0
--不知道ROWS_PROCESSED=201,我仅仅执行1次。搞不懂oracle如何得来的。
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 0 0 1
284019860 0rf89th8fvm4n 0 1 0
284019860 0rf89th8fvm4n 0 2 0
--我再次执行4次。select count(n1) from t_acs where vc2 = :lvc;
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 0 0 5
284019860 0rf89th8fvm4n 0 1 0
284019860 0rf89th8fvm4n 0 2 0
--BUCKET_ID=0,COUNT=5.
--修改变量:lvc := 'j10000';，继续测试：
exec :lvc := 'j10000';
select count(n1) from t_acs where vc2 = :lvc;
SCOTT@test> select * from v$sql_cs_statistics where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
284019860 0rf89th8fvm4n 0 1056299594 Y 1 201 55 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 0 0 5
284019860 0rf89th8fvm4n 0 1 1
284019860 0rf89th8fvm4n 0 2 0
--BUCKET_ID=1,COUNT=1.但是执行计划不会改变。
--再次执行4次。select count(n1) from t_acs where vc2 = :lvc;
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 0 0 5
284019860 0rf89th8fvm4n 0 1 5
284019860 0rf89th8fvm4n 0 2 0
--这个时候BUCKET_ID=0,1的count=5.再次执行：
SCOTT@test> select count(n1) from t_acs where vc2 = :lvc;
COUNT(N1)
----------
100000
SCOTT@test> select * from v$sql_cs_statistics where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
284019860 0rf89th8fvm4n 1 2397323702 Y 1 200001 489 0
284019860 0rf89th8fvm4n 0 1056299594 Y 1 201 55 0
--真搞不懂，为什么ROWS_PROCESSED=200001.生成了子游标。
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 1 0 0
284019860 0rf89th8fvm4n 1 1 1
284019860 0rf89th8fvm4n 1 2 0
284019860 0rf89th8fvm4n 0 0 5
284019860 0rf89th8fvm4n 0 1 5
284019860 0rf89th8fvm4n 0 2 0
6 rows selected.
--可以发现生成了子游标，CHILD_NUMBER=1,BUCKET_ID=1,COUNT=1.
--当两个BUCKET_ID贴近时，仅仅count相等的时候，再次执行才会生成新的子游标。
--大家有兴趣可以测试:lvc := 'j10000' 以及 :lvc := 'j>million'的情况。不再测试。
2.测试2：
--当两个BUCKET_ID不贴近时，情况如何呢？
alter system flush SHARED_POOL;
column address noprint
var lvc varchar2(10);
exec :lvc := 'j>million'
SCOTT@test> select count(n1) from t_acs where vc2 = :lvc;
COUNT(N1)
----------
1099049
SCOTT@test> select * from v$sql_cs_statistics where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
284019860 0rf89th8fvm4n 0 1976615302 Y 1 1099050 3444 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 0 0 0
284019860 0rf89th8fvm4n 0 1 0
284019860 0rf89th8fvm4n 0 2 1
--我再次执行4次。select count(n1) from t_acs where vc2 = :lvc;
SCOTT@test> select * from v$sql_cs_statistics where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
284019860 0rf89th8fvm4n 0 1976615302 Y 1 1099050 3444 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 0 0 0
284019860 0rf89th8fvm4n 0 1 0
284019860 0rf89th8fvm4n 0 2 5
--BUCKET_ID=2,COUNT=5.
--修改变量:lvc := 'j100';，继续测试：
exec :lvc := 'j100';
select count(n1) from t_acs where vc2 = :lvc;
SCOTT@test> select * from v$sql_cs_statistics where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
284019860 0rf89th8fvm4n 0 1976615302 Y 1 1099050 3444 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 0 0 1
284019860 0rf89th8fvm4n 0 1 0
284019860 0rf89th8fvm4n 0 2 5
--BUCKET_ID=0,COUNT=1.但是执行计划不会改变。仅仅BUCKET_ID=0,2的count不等于0，也就是BUCKET_ID不贴近时情况(也许说的术语不是很准确).
--再次执行select count(n1) from t_acs where vc2 = :lvc;每次都看看v$sql_cs_histogram视图的输出情况。
SCOTT@test> select count(n1) from t_acs where vc2 = :lvc;
COUNT(N1)
----------
100
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 0 0 2
284019860 0rf89th8fvm4n 0 1 0
284019860 0rf89th8fvm4n 0 2 5
SCOTT@test> select count(n1) from t_acs where vc2 = :lvc;
COUNT(N1)
----------
100
SCOTT@test> select * from v$sql_cs_histogram where sql_id='0rf89th8fvm4n';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
284019860 0rf89th8fvm4n 1 0 1
284019860 0rf89th8fvm4n 1 1 0
284019860 0rf89th8fvm4n 1 2 0
284019860 0rf89th8fvm4n 0 0 2
284019860 0rf89th8fvm4n 0 1 0
284019860 0rf89th8fvm4n 0 2 5
6 rows selected.
--可以看到第3次执行，生成了子游标。
--作者的推论：
For the same child cursor, when the count of a bucket_id is greater or equal to (trunc(count/2) – 1) of its distant
non-adjacent bucket_id, the next execution will mark the original cursor as bind aware and a new child cursor is
compiled.
--我感觉(测试)，仅仅误差1次。
-- count<=5,round(count/2-1)
-- count>=6,trunc(count/2)-1
--总结：
--1.BUCKET_ID贴近时count相等才会生成新的执行计划。
--2.BUCKET_ID不贴近时，count<=5,round(count/2-1)
-- count>=6,trunc(count/2)-1
--3.实际上这样也会导致一些问题情况出现，如果一个BUCKET_ID里面的执行次数很多，count很大，很难生成新的子光标，也就是会出现新的执行计划。这样acs变成了鸡肋。
--也就是我前面的测试情况。http://blog.itpub.net/267265/viewspace-721817/
--更正一下： BUCKET_ID不贴近时, ceil(count)/3,前面的结论不对。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-1336353/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
