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

--11G下Adaptive Cursor Sharing简称ACS能很好的解决绑定变量窥视的带来的问题，前一段时间看了2篇blog

<a href="https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/">https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/</a>

<a href="https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/">https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/</a>

--我以前也写过一篇blog,链接如下：

<a href="http://blog.itpub.net/267265/viewspace-721817/">http://blog.itpub.net/267265/viewspace-721817/</a>

--如果绑定变量值出现倾斜，如果执行次数很大，很难改变执行计划，当时提到最好的方法是加入提示/*+ 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的数据分布情况。建立直方图：

dbms_stats.gather_table_stats

(user

,'t_acs'

,method_opt => 'for all columns size skewonly'

,estimate_percent => null

,cascade => true

,no_invalidate => false

);

--注：我做全统计，这样统计比较准确。

--带入不同的值，主要查询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;

@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

-----------------------------------------------------------------------

| 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

--从这里看count没有变化。

2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 2

2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 3

SCOTT@test> @acs1 j>million 18

2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 21

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

2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 22

SCOTT@test> @acs1 j>million 20

2488vrgvjz63p 3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 42

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

SCOTT@test> @acs1 j>million 21

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;

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

2488vrgvjz63p 535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 1 1

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

2488vrgvjz63p 535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 2 1

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 ;

@acs1 j10000

@acs1 j>million

00000000AFC2D388 4146042997 2488vrgvjz63p 0 0 0

00000000AFC2D388 4146042997 2488vrgvjz63p 0 1 20

00000000AFC2D388 4146042997 2488vrgvjz63p 0 2 20

SCOTT@test> var B1 varchar2(10);

SCOTT@test> exec :B1 := 'j>million' ;

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

COUNT(N1)

----------

1099049

--分号前有空格。

00000000AFC2D388 4146042997 2488vrgvjz63p 1 0 0

00000000AFC2D388 4146042997 2488vrgvjz63p 1 1 0

00000000AFC2D388 4146042997 2488vrgvjz63p 1 2 1

6 rows selected.

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

--的新执行计划也发生了变化。

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

继续阅读

linux-svn卸载与安装

vsftp虚拟多用户多权限一键部署脚本

Ubuntu14.04 LTS下安装mongodb

Nginx服务优化（1）——隐藏版本号、修改用户与组、网页缓存时间、日志切割、连接超时一、隐藏版本号二、修改用户与组三、配置Nginx网页缓存时间四、实现Nginx日志分割五、配置Nginx实现连接超时六、补充关于时间日期的命令

httpd服务的部署、启动、配置和简单优化一、部署二、启动三、配置文件

配置网页内容访问

手动安装Intel network I217-LM网卡的Linux驱动

禁止ubuntu系统弹出报错界面

Ubuntu Linux下Apache的配置文件

neo4j之cypher使用文档

samba服务器的功能

sqlServer根据经纬查距离

【Linux】UDP广播报文接收速率问题

Linux设备模型（中）之上层容器

Oracle 批量查询传入List 返回List

PowerPC平台 Linux移植三