没有唯一答案,读者根据自己的维护经验讲解即可,下面给出一个作者曾经处理过的故障诊断案例。
1.故障环境
项目
|
sourcedb
|
db类型
|
RAC
|
db version
|
10.2.0.5.0
|
db存储
|
ASM
|
OS版本及kernel版本
|
AIX 64位 6.1.0.0
|
2.故障发生现象及报错信息
有一套数据库做测试的时候,CPU利用率很高,当时抓取了CPU和AWR的信息。发生问题的时间段是19~23点,其中,nmon数据截图如下图所示。
可以看到CPU的利用率是非常高的,下边来看看AWR中的数据。
从等待事件中可以很明显地看出,“enq: SQ -contention”和“DFS lock handle”这两个等待事件异常。“Top 5 Timed Events”部分也是AWR报告中非常重要的部分,从这里可以看出等待事件中排在前五位的是哪些事件,从而基本上就可以判断出性能瓶颈在什么地方。在这里,“enq: SQ-contention”等待了172254次,等待时间为69652s,平均等待时间为69652/172254s=404ms,等待类别为Configuration即配置上的等待问题。
3.有关序列等待的基础知识
根据AWR报告的内容基本可以断定,只要解决了“enq: SQ-contention”和“DFS lock handle”这两个等待事件即可解决问题。其实,“enq:SQ-contention”“row cache lock”“DFS lock handle”和“enq: SV- contention”这4个等待事件都与Oracle的序列有关,如下:
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值:
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值见下表。
模式代码
|
解释
|
1
|
Null Mode
|
2
|
Sub-Shoe
|
3
|
Sub-Exclusive
|
4
|
Share
|
5
|
Share/Sub-Exclusive
|
6
|
Exclusive
|
使用如下的SQL可以查询SQ和SV这两种锁的解释:
SELECT*FROM V$LOCK_TYPE D WHERE D.TYPEIN ('SV','SQ');
事实上,Oracle为了管理序列使用了如下表所示的三种锁。
锁
|
row cache lock
|
SQ锁(Sequence Cache)
|
SV锁(Sequence Ordering)
|
产生的条件
|
NOCACHE
|
CACHE+NOORDER或CACHE+ORDER (单实例)
|
CACHE+ORDER(RAC)
|
拥有的锁模式
|
6-X(Exclusive)
|
6-X(Exclusive)
|
5-SSX(Share/Sub-Exclusive)
|
表现出的等 待事件
|
row cache lock
|
enq:SQ-contention
|
Oracle 10g表现为DFS lock handle,而 Oracle 11g中表现为enq:SV-contention
|
简介
|
在赋予了NOCACHE属性 的序列上,在调用 SEQUNECE.NEXTVAL过 程中,将数据字典信息进行 物理修改时拥有该锁,等待 事件表现为row cache lock
|
赋予了CACHE属性的序列调刖 NEXTVAL期间,应该以SSX模式获得SQ 锁。若许多会话同时为了获取SQ锁而发生 争用,则等待enq:SQ-contention事件
|
在RAC上节点之间顺序得到保障的情况 下,调用SEQUENCE.NEXTVAL期间拥有 该锁。在RAC环境中,赋予CACHE+ORDER 属性的序列上发生,在Oracle 10g表现为 DFS lock handle,而在Oracle 11g中表现为 enq:SV-contention。解决办法:尽量设置为 NOORDER并增大其CACHE值
|
参数含义
|
P1代表V$ROWCACHE 中的CACHE#
|
P1可以查询到锁的名称和请求的MODE 值。P2值是序列的OBJECT_ID。因此,若 利用P2值与DBA_OBJECTS的结合,就可 以知道对哪个序列发生了等待现象
|
P1可以查询到锁的名称和请求的MODE值
|
解决办法
|
尽量设置为NOORDER属性并增大其CACHE值,一般情况下可以增大到1000
|
在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可以查询:
SELECT*FROM DBA_SEQUENCES D WHERE D.SEQUENCE_NAME='AUDSES$';
4.故障解决过程
首先查洵出现问题时间段的ASH视图DBA_HIST_ACTIVE_SESS_HISTORY,通过该视图可以找到需要的序列名称。可以有多种查询方法:
SELECT D.SQL_ID,COUNT(1) FROM DBA_HISL_ACTIVE_SESS_HISTORY D
WHERED.SAMPLE_TIME BETWEEN TO_DATE('20160823170000','YYYYMMDDHH24MISS')AND
TO_DATE('20160823230000','YYYYMMDDHH24MISS') AND D.EVENT=enq:SQ-contention' GROUP BY D.SQL_ID;
可以看到SQL_ID为3jhvjgj7kbpmt的SQL最多,查看具体SQL内容:
SELECT*FROM V$SQL A WHERE A.SQL_ID IN('3jhvjgj7kbpmt');
由此可以知道,产生等待的序列名称为ONLNID,另外,也可以从DBA_HIST_ACTIVE_SESS_HISTORY视图的P2值获取到序列的名称,SQL语句如下:
SELECT D.EVENT,D.P1TEXL D.P1, D.P2TEXL D.P2,
CHR(BITAND(P1,-16777216)/16777215)|| CHR(BITAND(P1,16711680)/65535)"Lock",
BITAND(P1, 65535) "Mode",D.BLOCKlNG_SESSION, D.BLOCKING_SESSION_STATUS,D.BLOCKING_SESSION_SERIAL#,
D.SQL_ID,TO_CHAR(D.SAMPLE_TIME,'YYYYMMDDHH24MISS')SAMPLE_TIME,D.*
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME BETWEEN TO_DATE('20160823170000','YYYYMMDDHH24MISS')AND
TO_DATE('20160823230000','YYYYMMDDHH24MISS') AND D.EVENT='enq:SQ-contention';
由以上的查询结果可知,序列的OBJECT_ID为47989,通过DBA_OBJECTS就可以查询到序列的名称了。另外,LOCK为SQ代表的是序列的CACHE锁(SEQUENCE CACHE),MODE为6代表EXCLUSIVE排它锁。
SELECT*FROM DBA_OBJECTS D WHERE D.object_id='47989';
知道了序列名称后,通过DBA_SEQUENCES视图就可以查询到序列的属性了:
SELECT*FROM DBA_SEQUENCES D WHERE D.sequence_name='ONLNID';
可以看到,该序列是NOORDER属性,CACHE值为默认的20,对于并发值很高的系统而言,该默认值太低,所以需要调整到1000。可以执行SQL语句“ALTER SEQUENCE ONLNlD CACHE 1000;”调整其CACHE值即可解决该问题。