ITPub博客

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

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

原创 Oracle 作者:lfree 时间:2014-12-15 16:46:15 0 删除 编辑

[20141213]11g ACS的一些问题3.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,才会生成新的执行计划(子光标)。

--我昨天在测试另外一些情况时,遇到一些问题,做一个记录:
--如果我使用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<=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脚本:
-- 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 loop;
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.

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

--从这里看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
PL/SQL procedure successfully completed.

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
--从这里看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                           2

SCOTT@test> @acs1 j>million 1
PL/SQL procedure successfully completed.

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                           3

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> @acs1 j>million 18
PL/SQL procedure successfully completed.

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

--可以发现执行计划依旧没有变化。

SCOTT@test> @acs1 j>million 1
PL/SQL procedure successfully completed.

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                          22

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> @acs1 j>million 20
PL/SQL procedure successfully completed.

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

--可以发现执行计划依旧没有变化。v$sql_cs_histogram的count也不变化。

SCOTT@test> @acs1 j>million 21

PL/SQL procedure successfully completed.

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> 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                          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;
@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
---------------- ---------- ------------- ------------ ---------- ----------
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

SCOTT@test> @acs1 j>million 1
PL/SQL procedure successfully completed.

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
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
2488vrgvjz63p       535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                           1            1

--执行计划发生了变化(看PLAN_HASH_VALUE)。选择的是全表扫描。如果这次带入j1000参数情况如何呢?

SCOTT@test> @acs1 j10000 1
PL/SQL procedure successfully completed.

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
2488vrgvjz63p       535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                           2            1

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
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 ;

SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 ;

alter system flush SHARED_POOL;
@acs1 j10000
@acs1 j>million

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AFC2D388 4146042997 2488vrgvjz63p            0          0          0
00000000AFC2D388 4146042997 2488vrgvjz63p            0          1         20
00000000AFC2D388 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

SCOTT@test> var B1 varchar2(10);
SCOTT@test> exec :B1 := 'j>million' ;

PL/SQL procedure successfully completed.

SCOTT@test> SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 ;
COUNT(N1)
----------
   1099049

--分号前有空格。

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AFC2D388 4146042997 2488vrgvjz63p            1          0          0
00000000AFC2D388 4146042997 2488vrgvjz63p            1          1          0
00000000AFC2D388 4146042997 2488vrgvjz63p            1          2          1
00000000AFC2D388 4146042997 2488vrgvjz63p            0          0          0
00000000AFC2D388 4146042997 2488vrgvjz63p            0          1         20
00000000AFC2D388 4146042997 2488vrgvjz63p            0          2         20

6 rows selected.

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
2488vrgvjz63p       535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                           1            1

--可以发现使用PLSQL测试与SQL语句的执行方式存在不同。v$sql_cs_histogram看到CHILD_NUMBER=1的情况,里面的count有变化,生成
--的新执行计划也发生了变化。

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

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

注册时间:2008-01-03

  • 博文量
    2696
  • 访问量
    6466017