[20160203]sequence与rac.txt-白红宇

[20160203]sequence与rac.txt

--前几天跟别人聊天提到对方管理的系统使用了大量的sequence，几乎每个表都以一个sequence作为主键。

--我们的系统也是相似的情况，但是我们开发使用一个表来保存这些信息，这样导致另外的问题，会出现阻塞的情况。

--sequence在rac中问题可能会放大，如果cache很小，并且使用order属性，会导致内联流量上升，并且出现row lock。

--而且这些字段一般都要作为主键，或者讲这些字段一般会存在索引，这样导致另外的问题:

1.如果使用order会导致插入的记录的相关索引存在块争用，因为数据是线性增加，索引的键值都插入一个块中，另外就是如果业务出现

偶然的停顿，会导致占用大量占用ITL槽，而这些块再插满出现块分裂时，会继承这些ITL槽的数量，导致索引占用的空间很大。

2.使用noorder也是一样，因为这样每个实例都cache自己的顺序号，这样其中一个实例的键值会导致索引块分裂是50%，加上上面如果业

务出现停顿，也会导致索引占用的空间很大。

--可以参考我以前的链接：

--自己也测试一下sequence存在大量插入的情况：

1.环境：

SCOTT@xxxx1> @ &r/ver1

PORT_STRING VERSION BANNER

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

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE SEQUENCE SCOTT.seq1 START WITH 0 INCREMENT BY 1 MINVALUE 0 CACHE 10 NOCYCLE ORDER;

--我cache设置相对偏小,仅仅10.

2.建立测试脚本:

$ cat aa.sql

declare

v_seq1 number;

begin

for i in 1 .. 2e5 loop

execute immediate 'select seq1.nextval from dual ' into v_seq1;

end loop;

end;

quit;

$ cat bb.sh

#! /bin/bash

sqlplus scott/bookms@192.168.xx.yyy:1521/xxxx @aa.sql &

--执行2次bb.sh

3.获得sql_id:

select seq1.nextval from dual;

sql_id=gh9xqf2pz2fjk;

EVENT COUNT(*)

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

enq: SV - contention 175

gc cr block 2-way 6

row cache lock 4

gc current block 2-way 4

gc cr block busy 2

cursor: pin S 1

7 rows selected.

SCOTT@xxxx1> select eq_name,eq_type,req_reason from v$enqueue_statistics where eq_type='SV';

no rows selected

--不知道SV表示什么?

3.加大cache再测试看看：

alter sequence seq1 cache 1000;

--重复测试：

SELECT event, COUNT (*)

FROM V$ACTIVE_SESSION_HISTORY

WHERE sql_id = 'gh9xqf2pz2fjk'

and sample_time >= '2016/02/03 09:25'

GROUP BY event

ORDER BY COUNT (*) DESC;

EVENT COUNT(*)

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

enq: SV - contention 138

--可以发现适当的加大cache的数值，可以缓解避免其他等待事件的出现。

3.修改为noorder属性再测试看看：

SCOTT@xxxx1> alter sequence seq1 noorder;

Sequence altered.

SELECT event, COUNT (*)

FROM V$ACTIVE_SESSION_HISTORY

WHERE sql_id = 'gh9xqf2pz2fjk'

and sample_time >= '2016/02/03 09:30'

GROUP BY event

ORDER BY COUNT (*) DESC ;

EVENT COUNT(*)

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

library cache: mutex X 3

--不再出现enq: SV - contention等待事件。

总结：

--从以上的测试可以发现在rac中使用seqence，加大cache，使用nooder属性(有一些业务要求保持顺序，可能不行)，可以减少相关的等待事件。

--另外如果使用sequence在rac中主要出现的等待事件是enq: SV - contention,gc cr block 2-way,row cache lock,gc current block 2-way.

SELECT event, COUNT (*)

FROM V$ACTIVE_SESSION_HISTORY

WHERE sql_id = 'gh9xqf2pz2fjk'

GROUP BY event

ORDER BY COUNT (*) DESC ;

EVENT COUNT(*)

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

enq: SV - contention 1074

233

row cache lock 24

gc cr block 2-way 9

library cache: mutex X 8

gc current block 2-way 5

latch: ges resource hash list 2

gc cr block busy 2

enq: SQ - contention 1

cursor: pin S 1

10 rows selected.