[20160203]sequence与rac.txt
--前几天跟别人聊天提到对方管理的系统使用了大量的sequence，几乎每个表都以一个sequence作为主键。
--我们的系统也是相似的情况，但是我们开发使用一个表来保存这些信息，这样导致另外的问题，会出现阻塞的情况。
--sequence在rac中问题可能会放大，如果cache很小，并且使用order属性，会导致内联流量上升，并且出现row lock。
--而且这些字段一般都要作为主键，或者讲这些字段一般会存在索引，这样导致另外的问题:
1.如果使用order会导致插入的记录的相关索引存在块争用，因为数据是线性增加，索引的键值都插入一个块中，另外就是如果业务出现
偶然的停顿，会导致占用大量占用ITL槽，而这些块再插满出现块分裂时，会继承这些ITL槽的数量，导致索引占用的空间很大。
2.使用noorder也是一样，因为这样每个实例都cache自己的顺序号，这样其中一个实例的键值会导致索引块分裂是50%，加上上面如果业
务出现停顿，也会导致索引占用的空间很大。
--可以参考我以前的链接：
http://blog.itpub.net/267265/viewspace-774519/
--自己也测试一下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 &
sqlplus scott/bookms@192.168.xx.yyy:1521/xxxx @aa.sql &
sqlplus scott/bookms@192.168.xx.yyy:1521/xxxx @aa.sql &
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
51
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
35
--可以发现适当的加大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(*)
---------------------------------------- ----------
14
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.