ITPub博客

首页 > 数据库 > Oracle > [20141213]11g ACS的一些问题4.txt

[20141213]11g ACS的一些问题4.txt

原创 Oracle 作者:lfree 时间:2014-12-16 09:23:04 0 删除 编辑

[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    < 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

规律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,出现count<>0的情况,如果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<=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);

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)<远端的count[bucket_id]
--才会生成新的执行计划(子光标)。
--可以看出上面的规律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.不知道是否正确,希望大家指出错误。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1369051/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2696
  • 访问量
    6467171