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

更新时间：2022-09-12 12:56:47

--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，才会生成新的执行计划(子光标)。

--我昨天在测试另外一些情况时，遇到一些问题，做一个记录：
--如果我使用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的数据分布情况。建立直方图：
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> @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
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> @acs1 j>million 18
PL/SQL procedure successfully completed.

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

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

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

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

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

SCOTT@test> @acs1 j>million 21

PL/SQL procedure successfully completed.

--问题依旧。执行计划使用索引。

--当查询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

@acs1 j>million 1

SCOTT@test> @acs1 j>million 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                           1            1

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

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

--当查询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> 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.

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

上一篇 : ：[20141225]学习使用tmux心得.txt下一篇 : [20141126]expr例子.txt

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

相关阅读

推荐文章