分享程序员开发的那些事...
更新时间：2022-09-12 12:52:35
[20141213]11g ACS的一些问题4.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存在某种规律：
规律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，才会生成新的执行计划(子光标)。
--但是前面仅仅考虑出现两个BUCKET_ID，出现count0的情况，如果3个backup_id,count都不等于0呢？作者做一次探究，
--视乎没有找到规律，我自己也做一些探究，看看是否找到规律。
https://hourim.wordpress.com/2014/12/10/bind-aware-part-iii/
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的数据分布情况。建立直方图：
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.为了测试方便，编写一个脚本：
--注意不能使用plsql代码，测试有问题。
http://blog.itpub.net/267265/viewspace-1368531/
$ cat acs3.sql
var B1 varchar2(10);
exec :B1 := '&1';
SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1;
$ cat acs4.sh
#! /bin/bash
for i in ` seq $2 `
do
sqlplus -S scott/btbtms @acs3.sql $1
done
3.建立测试例子1：
alter system flush SHARED_POOL;
host acs4.sh j10000 20
host acs4.sh j\>million 6
host acs4.sh j100 14
--注意执行的顺序。然后上面的规律2触发。
SCOTT@test> select * from v$sql_cs_histogram where sql_id='6ms7yvkbsvf03';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE757558 2542647299 6ms7yvkbsvf03 0 0 14
00000000BE757558 2542647299 6ms7yvkbsvf03 0 1 20
00000000BE757558 2542647299 6ms7yvkbsvf03 0 2 6
SCOTT@test> select sql_id,plan_hash_value,sql_text,executions,child_number from v$sql where sql_id='6ms7yvkbsvf03';
SQL_ID PLAN_HASH_VALUE SQL_TEXT EXECUTIONS CHILD_NUMBER
------------- --------------- ------------------------------------------------------------ ---------- ------------
6ms7yvkbsvf03 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 40 0
host acs4.sh j100 1
SCOTT@test> select * from v$sql_cs_histogram where sql_id='6ms7yvkbsvf03';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE757558 2542647299 6ms7yvkbsvf03 1 0 1
00000000BE757558 2542647299 6ms7yvkbsvf03 1 1 0
00000000BE757558 2542647299 6ms7yvkbsvf03 1 2 0
00000000BE757558 2542647299 6ms7yvkbsvf03 0 0 14
00000000BE757558 2542647299 6ms7yvkbsvf03 0 1 20
00000000BE757558 2542647299 6ms7yvkbsvf03 0 2 6
6 rows selected.
SCOTT@test> select sql_id,plan_hash_value,sql_text,executions,child_number from v$sql where sql_id='6ms7yvkbsvf03';
SQL_ID PLAN_HASH_VALUE SQL_TEXT EXECUTIONS CHILD_NUMBER
------------- --------------- ------------------------------------------------------------ ---------- ------------
6ms7yvkbsvf03 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 40 0
6ms7yvkbsvf03 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 1 1
--我做了许多测试,结果都是一样。
alter system flush SHARED_POOL;
host acs4.sh j10000 20
host acs4.sh j\>million 1
host acs4.sh j100 20
SCOTT@test> select * from v$sql_cs_histogram where sql_id='6ms7yvkbsvf03';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE87AEB8 2542647299 6ms7yvkbsvf03 1 0 1
00000000BE87AEB8 2542647299 6ms7yvkbsvf03 1 1 0
00000000BE87AEB8 2542647299 6ms7yvkbsvf03 1 2 0
00000000BE87AEB8 2542647299 6ms7yvkbsvf03 0 0 19
00000000BE87AEB8 2542647299 6ms7yvkbsvf03 0 1 20
00000000BE87AEB8 2542647299 6ms7yvkbsvf03 0 2 1
alter system flush SHARED_POOL;
host acs4.sh j10000 20
host acs4.sh j\>million 2
host acs4.sh j100 19
SCOTT@test> select * from v$sql_cs_histogram where sql_id='6ms7yvkbsvf03';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE5435C8 2542647299 6ms7yvkbsvf03 1 0 1
00000000BE5435C8 2542647299 6ms7yvkbsvf03 1 1 0
00000000BE5435C8 2542647299 6ms7yvkbsvf03 1 2 0
00000000BE5435C8 2542647299 6ms7yvkbsvf03 0 0 18
00000000BE5435C8 2542647299 6ms7yvkbsvf03 0 1 20
00000000BE5435C8 2542647299 6ms7yvkbsvf03 0 2 2
6 rows selected.
alter system flush SHARED_POOL;
host acs4.sh j10000 20
host acs4.sh j\>million 2
host acs4.sh j100 18
host acs4.sh j\>million 1
SCOTT@test> select * from v$sql_cs_histogram where sql_id='6ms7yvkbsvf03';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AFC2FE38 2542647299 6ms7yvkbsvf03 1 0 0
00000000AFC2FE38 2542647299 6ms7yvkbsvf03 1 1 0
00000000AFC2FE38 2542647299 6ms7yvkbsvf03 1 2 1
00000000AFC2FE38 2542647299 6ms7yvkbsvf03 0 0 18
00000000AFC2FE38 2542647299 6ms7yvkbsvf03 0 1 20
00000000AFC2FE38 2542647299 6ms7yvkbsvf03 0 2 2
6 rows selected.
--可以发现在BUCKET_ID=1的count最大的情况下,如果BUCKET_ID=0,2的count相加等于BUCKET_ID=1的count，下次执行建立新的子光标。
--换一种方式讲：下次执行时，如果BUCKET_ID=0,2的count相加小于BUCKET_ID=1的count，才会生成新的执行计划(子光标)。
--可以看出上面的规律2的第1条仅仅这个情况下的特例。
4.建立测试例子2：
alter system flush SHARED_POOL;
host acs4.sh j100 20
host acs4.sh j10000 1
host acs4.sh j\>million 7
host acs4.sh j10000 1
SCOTT@test> select * from v$sql_cs_histogram where sql_id='6ms7yvkbsvf03';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE467778 2542647299 6ms7yvkbsvf03 1 0 0
00000000BE467778 2542647299 6ms7yvkbsvf03 1 1 1
00000000BE467778 2542647299 6ms7yvkbsvf03 1 2 0
00000000BE467778 2542647299 6ms7yvkbsvf03 0 0 20
00000000BE467778 2542647299 6ms7yvkbsvf03 0 1 1
00000000BE467778 2542647299 6ms7yvkbsvf03 0 2 7
6 rows selected.
alter system flush SHARED_POOL;
host acs4.sh j100 20
host acs4.sh j10000 2
host acs4.sh j\>million 6
host acs4.sh j10000 1
SCOTT@test> select * from v$sql_cs_histogram where sql_id='6ms7yvkbsvf03';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AFFE8898 2542647299 6ms7yvkbsvf03 1 0 0
00000000AFFE8898 2542647299 6ms7yvkbsvf03 1 1 1
00000000AFFE8898 2542647299 6ms7yvkbsvf03 1 2 0
00000000AFFE8898 2542647299 6ms7yvkbsvf03 0 0 20
00000000AFFE8898 2542647299 6ms7yvkbsvf03 0 1 2
00000000AFFE8898 2542647299 6ms7yvkbsvf03 0 2 6
6 rows selected.
--可以发现如果最大的count在BUCKET_ID=0或者2，如果下次执行时：
--ceil((最大的count[bucket_id]-中间count[bucket_id])/3)--才会生成新的执行计划(子光标)。
--可以看出上面的规律2的第2条仅仅这个情况下的特例。
--大家可以那上面的组合测试，结论应该是一致的。
alter system flush SHARED_POOL;
host acs4.sh j100 20
host acs4.sh j10000 3
host acs4.sh j\>million 5
host acs4.sh j10000 3
SCOTT@test> select * from v$sql_cs_histogram where sql_id='6ms7yvkbsvf03';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE58F628 2542647299 6ms7yvkbsvf03 1 0 0
00000000BE58F628 2542647299 6ms7yvkbsvf03 1 1 1
00000000BE58F628 2542647299 6ms7yvkbsvf03 1 2 0
00000000BE58F628 2542647299 6ms7yvkbsvf03 0 0 20
00000000BE58F628 2542647299 6ms7yvkbsvf03 0 1 5
00000000BE58F628 2542647299 6ms7yvkbsvf03 0 2 5
6 rows selected.
alter system flush SHARED_POOL;
host acs4.sh j100 20
host acs4.sh j10000 3
host acs4.sh j\>million 6
host acs4.sh j\>million 1
SCOTT@test> SCOTT@test> select * from v$sql_cs_histogram where sql_id='6ms7yvkbsvf03';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE5A6F18 2542647299 6ms7yvkbsvf03 1 0 0
00000000BE5A6F18 2542647299 6ms7yvkbsvf03 1 1 0
00000000BE5A6F18 2542647299 6ms7yvkbsvf03 1 2 1
00000000BE5A6F18 2542647299 6ms7yvkbsvf03 0 0 20
00000000BE5A6F18 2542647299 6ms7yvkbsvf03 0 1 3
00000000BE5A6F18 2542647299 6ms7yvkbsvf03 0 2 6
6 rows selected.
--总结一下：
1.要表述出来，才感觉自己的语文太差了。
2.count[bucket_id=1]最大的情况下,下次执行时,count[bucket_id=1]3.count[bucket_id=1]不是最大的情况下，下次执行时,
--ceil((max(count[bucket_id=0],count[bucket_id=2])-count[bucket_id=1])/3)--才会生成新的子光标。
4.不知道是否正确，希望大家指出错误。