Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

0    79    1

Tags:

👉 本文共约4547个字,系统预计阅读时间或需18分钟。

前言部分

导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~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的序列有关,如下所示:

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

其中,PAREMETER1的值为“name|mode”或“type|mode”的事件为队列等待。在这类等待事件中,name代表队列的名称,type代表队列的类型,mode代表队列的模式。使用如下的SQL可以查询到锁的名称和请求的mode值:

其中,MODE值如下表所示:

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

使用如下的SQL可以查询SQ和SV这2种锁的解释:

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

事实上,Oracle为了管理序列使用了如下表所示的三种锁:

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

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可以查询:

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

RAC中的序列的ORDER和NOORDER测试

测试1:NOORDER属性的序列

测试2:ORDER属性的序列

序列的CACHE值性能测试

Oracle 11gR2测试,单实例数据库单会话循环不间断取600000个值。

类别用时
NOCACHE00:01:16.16
CACHE 2000:00:17.79
CACHE 10000:00:15.22
CACHE 100000: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';

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

SELECT * FROM V$ROWCACHE A WHERE A.CACHE#=13;

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

SELECT * FROM v$lock a WHERE a.sid IN (6,8,114) AND a.TYPE \<>'AE';

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

SELECT * FROM V$lock_Type a WHERE A.TYPE='TO';

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

enq: SQ - contention模拟

版本:11.2.0.4

DROP SEQUENCE LHR_SEQ01;

CREATE SEQUENCE LHR_SEQ01 START WITH 1 CACHE 2 NOORDER;

DECLARE

V_N NUMBER;

BEGIN

FOR CUR IN 1 .. 100000000 LOOP

SELECT LHR_SEQ01.NEXTVAL INTO V_N FROM DUAL;

END LOOP;

END;

/

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,

CHR(BITAND(P1, -16777216) / 16777215) ||

CHR(BITAND(P1, 16711680) / 65535) "Lock",

BITAND(P1, 65535) "Mode",

(SELECT A.OBJECT_NAME || ':' || A.OBJECT_TYPE

FROM DBA_OBJECTS A

WHERE A.OBJECT_ID = A.p2) OBJECT_NAME

FROM V$SESSION A

WHERE A.STATUS = 'ACTIVE'

AND A.EVENT = 'enq: SQ - contention';

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

enq: SV - contention和DFS lock handle模拟

DROP SEQUENCE LHR_SEQ01;

CREATE SEQUENCE LHR_SEQ01 START WITH 1 CACHE 2 ORDER;

DECLARE

V_N NUMBER;

BEGIN

FOR CUR IN 1 .. 100000000 LOOP

SELECT LHR_SEQ01.NEXTVAL INTO V_N FROM DUAL;

END LOOP;

END;

/

版本:11.2.0.4

SELECT A.INST_ID,

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,

CHR(BITAND(P1, -16777216) / 16777215) ||

CHR(BITAND(P1, 16711680) / 65535) "Lock",

BITAND(P1, 65535) "Mode",

(SELECT A.OBJECT_NAME || ':' || A.OBJECT_TYPE

FROM DBA_OBJECTS A

WHERE A.OBJECT_ID = A.P2) OBJECT_NAME,

A.EVENT

FROM GV$SESSION A

WHERE A.STATUS = 'ACTIVE'

-- AND A.EVENT = 'enq: SQ - contention'

AND A.SID IN (225, 99, 193, 194)

AND A.SQL_ID IS NOT NULL;

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

Oracle 10.2.0.5中:

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

Oracle序列等待事件总结(enq: SQ - contention、row cache lock、DFS lock handle和enq: SV - contention)

注意,Oracle 10g表现为:DFS lock handle,而Oracle 11g中表现为enq: SV - contention。个人测试版本,10g为10.2.0.5,11g版本为11.2.0.4。

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

4 × 5 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部