合 Oracle序列等待事件总结（enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention）
Tags： Oracle等待事件row cache lockDFS lock handleenq: SV - contentionenq: SQ - contention
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 序列等待事件总结
② enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention模拟
③ 序列的CACHE值性能测试
④ RAC中序列的ORDER和NOORDER测试
⑤ 序列等待的相关案例处理
本文简介
在2016年8月24日发布过一篇文章，BLOG名称为：“【故障处理】序列cache值过小导致CPU利用率过高”，连接地址为：http://blog.itpub.net/26736162/viewspace-2123996/，文章中根据案例详细分析了一次有关enq: SQ - contention等待事件的问题，基本上把所有的有关序列问题导致的等待事件全部讲解了一遍，但是那篇文章是针对Oracle 10g数据库而言的，而在Oracle 11g中，对SV锁进行了分离，出现了enq: SV - contention等待事件，而在Oracle 10g不存在“enq: SV - contention”该等待事件，而是表现为DFS lock handle，所以这篇文章将再次把序列等待事件整理一下。
序列等待事件
基础知识介绍
序列的等待事件基本上都与队列的等待事件相关，有关队列的更多介绍可以参考：http://blog.itpub.net/26736162/viewspace-2126079/
其实，“enq: SQ - contention”、“row cache lock”、“DFS lock handle”和“enq: SV - contention”这4个等待事件都与Oracle的序列有关，如下所示：
1 2 3 4 5 6 | SELECT * FROM V$EVENT_NAME WHERE NAME IN ('row cache lock', 'enq: SQ - contention', 'DFS lock handle', 'enq: SV - contention'); |
其中，PAREMETER1的值为“name|mode”或“type|mode”的事件为队列等待。在这类等待事件中，name代表队列的名称，type代表队列的类型，mode代表队列的模式。使用如下的SQL可以查询到锁的名称和请求的mode值：
1 2 3 4 5 6 7 | SELECT CHR(BITAND(P1,-16777216)/16777215)|| CHR(BITAND(P1, 16711680)/65535) "LOCK", BITAND(P1, 65535) "MODE" FROM V$SESSION_WAIT WHERE EVENT IN ('enq: SQ - contention', 'DFS lock handle', 'enq: SV - contention'); |
其中，MODE值如下表所示：
使用如下的SQL可以查询SQ和SV这2种锁的解释：
1 | SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('SV','SQ'); |
事实上，Oracle为了管理序列使用了如下表所示的三种锁：
RAC上创建序列时，在赋予了CACHE属性的状态下，若没有赋予ORDER属性，则各节点将会把不同范围的序列值CACHE到内存上。比如，拥有两个节点的RAC环境下，创建CACHE值为100的序列时，1号节点使用1～100，2号节点使用101～200。若两个节点之间都通过递增方式使用序列，必须赋予ORDER属性。Oracle序列默认是NOORDER，如果设置为ORDER，那么在单实例环境没有影响，在RAC环境中，多实例实际缓存相同的序列，此时在多个实例并发取该序列的时候，会有短暂的资源竞争来在多实例之间进行同步。因次性能相比NOORDER要差，所以RAC环境非必须的情况下不要使用ORDER，尤其要避免NOCACHE ORDER组合。
有一点必须要注意，没有赋予CACHE属性时，不管ORDER属性使用与否或RAC环境与否，一直等待row cache lock 事件。row cache lock是可以在全局范围内使用的锁，单实例环境或多实例环境同样可以发生。如果使用了CACHE，如果此时DB崩溃了，那么序列会从CACHE值之后重新开始，在CACHE中没有使用的序列会被跳过，这样就会导致序列不连续。在创建序列时，CACHE的缺省值设定为较小的20。因此创建并发量多的序列时，CACHE值应该取1000以上的较大值。
另外，若一次性同时创建许多会话时，有时会发生enq: SQ - contention等待事件。其原因是V$SESSION.AUDSID列值是利用序列创建的。Oracle在创建新的会话后，利用名为SYS.AUDSES$的序列的NEXTVAL来创建AUDSID值。在Oracle 10g下SYS.AUDSES$的CACHE值默认20，但在Oracle 11g下SYS.AUDSES$的CACHE值默认为10000，通过如下的SQL可以查询：
1 | SELECT * FROM DBA_SEQUENCES D WHERE D.SEQUENCE_NAME ='AUDSES$'; |
RAC中的序列的ORDER和NOORDER测试
测试1：NOORDER属性的序列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | NODE1： SQL> CREATE SEQUENCE SEQ_NOORDER_LHR START WITH 1 INCREMENT BY 1 CACHE 20 NOORDER; SEQUENCE CREATED. SQL> SELECT SEQ_NOORDER_LHR.NEXTVAL FROM DUAL; NEXTVAL ---------- 1 SQL> / NEXTVAL ---------- 2 SQL> / NEXTVAL ---------- 3 NODE2： SQL> SELECT SEQ_NOORDER_LHR.NEXTVAL FROM DUAL; NEXTVAL ---------- 21 SQL> / NEXTVAL ---------- 22 SQL> / NEXTVAL ---------- 23 NODE2上不是从4开始的，是从21开始的，因为NODE1已经CACHE了20个。 |
测试2：ORDER属性的序列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | NODE1： SQL> CREATE SEQUENCE SEQ_ORDER_LHR START WITH 1 INCREMENT BY 1 CACHE 20 ORDER; SEQUENCE CREATED. SQL> SELECT SEQ_ORDER_LHR.NEXTVAL FROM DUAL; NEXTVAL ---------- 1 SQL> / NEXTVAL ---------- 2 SQL> / NEXTVAL ---------- 3 NODE2： SQL> SELECT SEQ_ORDER_LHR.NEXTVAL FROM DUAL; NEXTVAL ---------- 4 SQL> / NEXTVAL ---------- 5 SQL> / NEXTVAL ---------- 6 指定ORDER之后，取的序列就是顺序的。 |
序列的CACHE值性能测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | SYS@lhrdb> CREATE SEQUENCE SEQ_NOCACHE_LHR NOCACHE; Sequence created. SYS@lhrdb> CREATE SEQUENCE SEQ_CACHE20_LHR CACHE 20; Sequence created. SYS@lhrdb> CREATE SEQUENCE SEQ_CACHE100_LHR CACHE 100; Sequence created. SYS@lhrdb> CREATE SEQUENCE SEQ_CACHE1000_LHR CACHE 1000; Sequence created. SYS@lhrdb> SET TIMING ON; SYS@lhrdb> DECLARE 2 X NUMBER; 3 BEGIN 4 FOR I IN 1 .. 600000 LOOP 5 SELECT SEQ_NOCACHE_LHR.NEXTVAL INTO X FROM DUAL; 6 END LOOP; 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:01:16.16 SYS@lhrdb> DECLARE 2 X NUMBER; 3 BEGIN 4 FOR I IN 1 .. 600000 LOOP 5 SELECT SEQ_CACHE20_LHR.NEXTVAL INTO X FROM DUAL; 6 END LOOP; 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:17.79 SYS@lhrdb> DECLARE 2 X NUMBER; 3 BEGIN 4 FOR I IN 1 .. 600000 LOOP 5 SELECT SEQ_CACHE100_LHR.NEXTVAL INTO X FROM DUAL; 6 END LOOP; 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:15.22 SYS@lhrdb> DECLARE 2 X NUMBER; 3 BEGIN 4 FOR I IN 1 .. 600000 LOOP 5 SELECT SEQ_CACHE1000_LHR.NEXTVAL INTO X FROM DUAL; 6 END LOOP; 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:13.74 SYS@lhrdb> |
Oracle 11gR2测试，单实例数据库单会话循环不间断取600000个值。
| 类别 | 用时 |
|---|---|
| NOCACHE | 00:01:16.16 |
| CACHE 20 | 00:00:17.79 |
| CACHE 100 | 00:00:15.22 |
| CACHE 1000 | 00:00:13.74 |
基本上cache 大于20的时候性能基本可以接受，nocache的时候性能确实很差。
等待事件模拟
row cache lock序列等待模拟
版本：11.2.0.4
CREATE SEQUENCE LHR_SEQ01 START WITH 1 NOCACHE ;
开2-3个窗口分别执行如下的SQL语句：
DECLARE
V_N NUMBER;
BEGIN
FOR CUR IN 1 .. 100000000 LOOP
SELECT LHR_SEQ01.NEXTVAL INTO V_N FROM DUAL;
END LOOP;
END;
/
可以使用vmstat 1查看CPU的使用率。
SELECT A.SID,
A.BLOCKING_SESSION,
A.SQL_ID,
(SELECT B.SQL_TEXT FROM V$SQLAREA B WHERE B.SQL_ID = A.SQL_ID) SQL_TEXT,
A.P1
FROM V$SESSION A
WHERE A.STATUS = 'ACTIVE'
AND A.EVENT = 'row cache lock';
SELECT * FROM V$ROWCACHE A WHERE A.CACHE#=13;
SELECT * FROM v$lock a WHERE a.sid IN (6,8,114) AND a.TYPE \<>'AE';
SELECT * FROM V$lock_Type a WHERE A.TYPE='TO';
