想关注我吗？请点击图片上方蓝字小麦苗关注即可，关注后您将可以每日获得最实用的数据库技术。请将小麦苗公众号置顶，小麦苗不喜欢被压着，~O(∩_∩)O~
小麦苗的今日寄语
LDD，如果有一天，你走进我的心里，你一定会哭，因为里面装满你的点滴。如果有一天，我走进你的心里，我也一定会哭，因为里面找不到我的身影。
今天带给大家的是序列cache值过小导致CPU利用率过高的一个案例，说到底也就是关于等待事件的处理。看完本文，希望大家可以学到下边4个方面的内容：
① enq: SQ - contention等待事件的解决
② 一般等待事件的解决办法
③ DFS lock handle等待事件
④ 与序列有关的等待事件
当然，大家看文章的同时可以点击下边的音乐感受作者小麦苗最近悲伤的心情。
1 BLOG文档结构图
2 前言部分
2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① enq: SQ - contention等待事件的解决
② 一般等待事件的解决办法
③ DFS lock handle等待事件
④ 与序列有关的等待事件
Tips：
① 本文在ITpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）有同步更新
② 文章中用到的所有代码，相关软件，相关资料请前往小麦苗的云盘下载（http://blog.itpub.net/26736162/viewspace-1624453/）
③ 若文章代码格式有错乱，推荐使用搜狗、360或QQ浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式显示有问题，可以去博客园地址阅读
④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
3 故障分析及解决过程
3.1 故障环境介绍
项目 |
source db |
db 类型 |
RAC |
db version |
10.2.0.5.0 |
db 存储 |
ASM |
OS版本及kernel版本 |
AIX 64位 6.1.0.0 |
3.2 故障发生现象及报错信息
早上同事过来跟我说昨天有一套数据库做测试的时候，CPU利用率很高，他已经抓取了CPU和AWR，让我帮忙分析分析，首先发生问题的时间段是19点到23点，nmon数据截图如下：
可以看到CPU的利用率是非常高的，下边我们来看看AWR中的数据：
其它的项目就不列出了，从等待事件中可以很明显的看出enq: SQ - contention和DFS lock handle这2个等待事件异常。Top 5 Timed Events这个部分也是AWR报告中非常重要的部分，从这里可以看出等待时间在前五位的是什么事件，基本上就可以判断出性能瓶颈在什么地方。通常，在没有问题的数据库中，CPU time总是列在第一个。在这里，enq: SQ - contention等待了172254次，等待时间为69652秒，平均等待时间为69652/172254=404毫秒，等待类别为Configuration即配置上的等待问题。
3.3 故障分析
根据AWR报告的内容，我们知道只要解决了enq: SQ - contention和DFS lock handle这2个等待事件即可解决问题。那么我们首先来了解一些关于这2个等待事件的知识。
===============================================================================
enq: SQ - contention/row cache lock/DFS lock handle这三个等待事件都与Oracle 的Sequence 有关。
SELECT *
FROM V$EVENT_NAME
WHERE NAME IN
('row cache lock', 'enq: SQ - contention', 'DFS lock handle');
使用如下的SQL我们可以查询到锁的名称和请求的MODE，表的mode值参考表格：
select chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1, 16711680)/65535) "Lock",
bitand(p1, 65535) "Mode"
from v$session_wait
where event = 'DFS enqueue lock acquisition';
Table C-1 Lock Mode Values
Mode Value |
Description |
1 |
Null mode |
2 |
Sub-Share |
3 |
Sub-Exclusive |
4 |
Share |
5 |
Share/Sub-Exclusive |
6 |
Exclusive |
SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('SV','SQ');
Oracle 为了管理Sequence 使用了以下三种锁。
① row cache lock:在调用SEQUNECE.NEXTVAL过程中，将数据字典信息进行物理修改时获取。赋予了NOCACHE属性的SEQUENCE上发生，等待事件为row cache lock。
② SQ锁:在内存上缓存（CACHE）的范围内，调用SEQUENCE.NEXTVAL 期间拥有此锁。赋予了CACHE 属性的SEQUENCE 上发生。赋予了CACHE 属性的SEQUENCE 调用NEXTVAL 期间，应该以SSX 模式获得SQ 锁。许多会话同时为了获取SQ 锁而发生争用过程中，若发生争用，则等待enq: SQ - contention事件。enq: SQ - contention 事件的P2 值是Sequence 的OBJECT ID。因此，若利用P2 值与DBA_OBJECTS 的结合，就可以知道对哪个SEQUENCE 发生了等待现象。
③ SV锁:RAC上节点之间顺序得到保障的情况下，调用SEQUENCE.NEXTVAL期间拥有。赋予CACHE + ORDER属性的SEQUENCE 上发生，等待事件为DFS lock handle，解决办法为：尽量设置为NOORDER并增大其CACHE值。
根据创建Sequence时赋予的属性，整理等待事件的结果如下：
v NOCACHE: row cache lock
v CACHE + NOORDER: enq: SQ - contention
v CACHE + ORDER(RAC): DFS lock handle
创建SEQUENCE赋予的CACHE 值较小时，有enq: SQ - contention等待增加的趋势。CACHE值较小时，内存上事先CACHE的值很快被耗尽，这时需要将数据字典信息物理修改后，再次执行CACHE的工作。在此期间，因为一直拥有SQ 锁，相应的enq: SQ - contention 事件的等待时间也会延长。很不幸的是，在创建SEQUENCE 时，将CACHE 值的缺省值设定为较小的20。因此创建使用量多的SEQUENCE 时，CACHE 值应该取1000 以上的较大值。
另外，偶尔一次性同时创建许多会话时，有时会发生enq: SQ - contention 等待事件。其理由是V$SESSION.AUDSID（auditing session id）列值是利用Sequence创建的。Oracle 在创建新的会话后，利用名为SYS.AUDSES$的Sequence 的nextval，创建AUDSID 值。SYS.AUDSES$ Sequence 的CACHE 大小的缺省值设定为20。许多会话同时连接时，可以将SYS.AUDSES$ Sequence 的CACHE大小扩大至1000，以此可以解决enq: SQ - contention 等待问题。 10g下默认20,11g下默认为10000，通过如下的SQL可以查询：
SELECT * FROM dba_sequences d WHERE d.sequence_name ='AUDSES$';
RAC 上创建SEQUENCE 时，在赋予了CACHE属性的状态下，若没有赋予ORDER 属性，则各节点将会把不同范围的SEQUENCE 值CACHE 到内存上。比如，拥有两个节点的RAC 环境下，创建CACHE 值为100 的SEQUENCE 时，1号节点使用1～100，2 号节点使用101～200。若两个节点之间都通过递增方式使用SEQUENCE，必须赋予如下ORDER 属性。
SQL> CREATE SEQUENCE ORDERED_SEQUENCE CACHE 100 ORDER;
如果是已赋予了CACHE+ORDER 属性的SEQUENCE，Oracle 使用SV 锁进行行同步。即，对赋予了ORDER 属性的Sequence 调用nextval 时，应该以SSX模式拥有SV 锁。在获取SV 锁过程中，如果发生争用时，不是等待row cache lock 事件或enq: SQ - contention 事件，而是等待名为DFS lock handle 事件。正因如此，V$EVENT_NAME 视图上不存在类似"enq:SV-contention"的事件。DFS lock handle 事件是在OPS 或RAC 环境下，除了高速缓冲区同步之外，还有行高速缓冲区或库高速缓冲区的为了同步获取锁的过程中等待的事件。若要保障多个节点之间Sequence顺序，应该在全局范围内获得锁，在此过程中会发生DFS lock handle 等待。在获取SV 锁的过程中发生的DFS lock handle等待事件的P1 、P2 值与enq: SQ - contention 等待事件相同（ P1=mode+namespace、P2=object#）。因此从P1 值能确认是否是SV 锁，通过P2值可以确认对哪些Sequence 发生过等待。SV 锁争用问题发生时的解决方法与SQ 锁的情况相同，就是将CACHE 值进行适当调整，这也是唯一的方法。
在RAC 等多节点环境下，Sequence 的CACHE 值给性能带来的影响比单节点环境更严重。因此，尽量赋予CACHE+NOORDER 属性，并要给予足够大的CACHE值。如果需要保障顺序，必须赋予CACHE+ORDER 属性。但这时为了保障顺序，实例之间不断发生数据的交换。因此，与赋予了NOORODER属性的时候相比性能稍差。
有一点必须要注意，没有赋予CACHE属性时，不管ORDER 属性使用与否或RAC 环境与否，一直等待row cache lock 事件。row cache lock是可以在全局范围内使用的锁，单实例环境或多实例环境同样可以发生。
没有赋予CACHE属性时，不管ORDER属性是否或RAC环境是否，一直等待ROW CACHE事件，ROW CACHE LOCK是否可以在全局范围内使用的锁，单实例环境或多实例环境同时可以发生。
Oracle Sequence默认是NOORDER，如果设置为ORDER；在单实例环境没有影响，在RAC环境此时，多实例实际缓存相同的序列，此时在多个实例并发取该序列的时候，会有短暂的资源竞争来在多实例之间进行同步。因次性能相比noorder要差，所以RAC环境非必须的情况下不要使用ORDER，尤其要避免NOCACHE ORDER组合。
但是如果使用了Cache，如果此时DB 崩溃了，那么sequence会从cache之后重新开始，在cache中没有使用的sequence会被跳过。即sequence不连续。所以只有在多节点高峰并发量很大的情况且对连续性要求不高的情况下，才使用：noorder + cache。
The session waits for the lock handle of a global lock request. The lock handle identifies a global lock. With this lock handle, other operations can be performed on this global lock (to identify the global lock in future operations such as conversions or release). The global lock is maintained by the DLM.
Wait Time: The session waits in a loop until it has obtained the lock handle from the DLM. Inside the loop there is a wait of 0.5 seconds.
Parameter |
Description |
name |
See "name and type" |
mode |
See "mode" |
id1 |
See "id1" |
id2 |
See "id2" |
The session needs to get the lock handle.
该等待事件的发生，若不是SV锁的话，多半为bug引起。
id1
The first identifier (id1) of the enqueue or global lock takes its value from P2 or P2RAW. The meaning of the identifier depends on the name (P1).
id2
The second identifier (id2) of the enqueue or global lock takes its value from P3 or P3RAW. The meaning of the identifier depends on the name (P1).
mode
The mode is usually stored in the low order bytes of P1 or P1RAW and indicates the mode of the enqueue or global lock request.This parameter has one of the following values:
Table C-1 Lock Mode Values
Mode Value |
Description |
1 |
Null mode |
2 |
Sub-Share |
3 |
Sub-Exclusive |
4 |
Share |
5 |
Share/Sub-Exclusive |
6 |
Exclusive |
Use the following SQL statement to retrieve the name of the lock and the mode of the lock request:
select chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1, 16711680)/65535) "Lock",
bitand(p1, 65535) "Mode"
from v$session_wait
where event = 'DFS enqueue lock acquisition';
name and type
The name or "type" of the enqueue or globallock can be determined by looking at the two high order bytes of P1 or P1RAW. The name is always two characters. Use the following SQL statement to retrieve the lock name.
select chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1,16711680)/65535) "Lock"
from v$session_wait
where event = 'DFS enqueue lock acquisition';
===============================================================================
有了以上的知识，我们知道，目前只需要找到产生等待的序列名称，然后设置其CACHE为比较大的一个值即可解决问题。
3.4 故障解决过程
3.4.1 enq: SQ - contention等待事件
我们查询出现问题时间段的ASH视图DBA_HIST_ACTIVE_SESS_HISTORY来找到我们需要的序列名称。
可以有多种查询方法：
SELECT D.SQL_ID, COUNT(1)
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'
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值获取到序列的名称，如下：
SELECT D.EVENT,
D.P1TEXT,
D.P1,
D.P2TEXT,
D.P2,
CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535) "Lock",
BITAND(P1, 65535) "Mode",
D.BLOCKING_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，由此也可以知道序列名称如下，另外，lock为SQ代表的是序列的cache锁（Sequence Cache），mode为6代表Exclusive排他锁。
SELECT * FROM DBA_OBJECTS D WHERE D.object_id='47989';
知道了序列名称后，我们就可以查询序列的属性了：
SELECT * FROM DBA_SEQUENCES D WHERE D.sequence_name='ONLNID' ;
可以看到，该序列是NOORDER属性，CACHE值为默认的20，对于并发值很高的系统而言，该默认值太低，所以需要调整到1000，我们执行SQL:ALTER SEQUENCE NFXS.ONLNID CACHE 1000; 调整其cache值即可解决该问题。
3.4.2 DFS lock handle等待事件
我们查询出现问题时间段的ASH视图DBA_HIST_ACTIVE_SESS_HISTORY来找到我们需要的序列名称。
可以有多种查询方法：
SELECT D.SQL_ID, COUNT(1)
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME BETWEEN TO_DATE('20160823170000', 'YYYYMMDDHH24MISS') AND
TO_DATE('20160823230000', 'YYYYMMDDHH24MISS')
AND D.EVENT = 'DFS lock handle'
GROUP BY D.SQL_ID;
可以看到SQL_ID为"67vjwqswg2zvy"的SQL最多，我们查看具体SQL内容：
SELECT * FROM V$SQL A WHERE A.SQL_ID IN ('67vjwqswg2zvy') ;
SQL的内容为：
SELECT formatid, globalid, branchid FROM SYS.DBA_PENDING_TRANSACTIONS ORDER BY formatid, globalid, branchid
很奇怪，这是个系统视图，为啥会有DFS lock handle的等待事件产生呢？
SELECT D.EVENT,
D.P1TEXT,
D.P1,
D.P2TEXT,
D.P2,
CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535) "Lock",
BITAND(P1, 65535) "Mode",
D.BLOCKING_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 = 'DFS lock handle';
由以上的查询结果可知，lock为CI代表的是交叉实例功能调用实例，而并不是我们期望的SV锁，mode为5代表Share/Sub-Exclusive。
SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE ='CI';
查了metalink可知，该问题是由bug引起。该库的版本为10.2.0.5的基础版本，并没有打任何的PSU。
3.5 健康检查
AWR分析完成后，我又收集了一下该库的健康检查情况，看看是否有其它方面的问题。
可以看出这个库并没有任何的PSU的信息，然后我们直接查看检查的结果：
可以看到数据库有上边的几个问题，其中就有cache小于20的问题，我们点击连接可以看到：
另外，在告警日志中，我们也可以看到，如下的信息，说明prcesses参数设置过小。
About Me
.....................................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号（xiaomaimiaolhr）上有同步更新，推荐pdf文件阅读
● QQ群：230161599 微信群：私聊
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2123996/ 博客园地址：http://www.cnblogs.com/lhrbest/articles/5804363.html
● 本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）
● 小麦苗分享的其它资料：http://blog.itpub.net/26736162/viewspace-1624453/
● 联系我请加QQ好友(642808185)，注明添加缘由
● 于 2016-08-24 09:00~2016-08-24 19:00 在中行完成
● 【版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任】
........................................................................................................................................
长按识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号：xiaomaimiaolhr,学习最实用的数据库技术。
本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。