合 Oracle序列cache值过小导致CPU利用率过高
前言部分
导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① enq: SQ - contention等待事件的解决
② 一般等待事件的解决办法
③ DFS lock handle等待事件
④ 与序列有关的等待事件
故障分析及解决过程
故障环境介绍
项目 | source db |
---|---|
db 类型 | RAC |
db version | 10.2.0.5.0 |
db 存储 | ASM |
OS版本及kernel版本 | AIX 64位 6.1.0.0 |
故障发生现象及报错信息
早上同事过来跟我说昨天有一套数据库做测试的时候,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即配置上的等待问题。
故障分析
根据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时赋予的属性,整理等待事件的结果如下:
- NOCACHE: row cache lock
- CACHE + NOORDER: enq: SQ - contention
- 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是可以在全局范围内使用的锁,单实例环境或多实例环境同样可以发生。