AWR报告信息如下:
从db time/Elapsed显示数据库的压力并不是很大。
每秒钟产生的redo log 6M,每小时21G,数据库的IO写压力很大。
top5等待事件:enq:CF-contention 该等待事件不是空闲等待事件;
Oracle AWR报告生成与查看
在CentOS 6.4下安装Oracle 11gR2(x64)
二、Metalink对该等待事件的分析
这问题一直没有遇到过,只能求助于metalink,详细的说明如下:
1、出现问题的版本
ORACLE DATABASE - ENTERPRISE EDITION - VERSION 9.2.0.1 TO 11.2.0.3 [RELEASE 9.2 TO 11.2](当前数据库的版本为11.2.0.3)
2、症状
在awr等待报告中的top5等待事件或出现v$session_wait的等待事件;
3、原因
任何需要读取控制文件的动作期间都会产生CF队列,CF锁用于controlfile序列操作和共享部分controlfile读和写。通常CF锁是分配给一个非常简短的时间和时使用:
•发生检查点
•日志文件的切换
•归档online redolog
•运行崩溃后的恢复
•热备的开始和结束
•DML通过nologging选项执行对象时
4、解决问题
找出当前持有CF锁的对象
select l.sid, p.program, p.pid, p.spid, s.username, s.terminal, s.module, s.action, s.event, s.wait_time, s.seconds_in_wait, s.statefrom v$lock l, v$session s, v$process pwhere l.sid = s.sidand s.paddr = p.addrand l.type='CF'and l.lmode >= 5;
查找等待CF锁的对象
select l.sid, p.program, p.pid, p.spid, s.username, s.terminal, s.module, s.action, s.event, s.wait_time, s.seconds_in_wait, s.statefrom v$lock l, v$session s, v$process pwhere l.sid = s.sidand s.paddr = p.addrand l.type='CF'and l.request >= 5
METALINK如下:
It is advisable to run the above queries a few times in a row...
1. If you see the holder is:
background process, typically LGWR, CKPT or ARCn
the holder is holding the enqueue for a longer period of time
Check if the redologs are sized adequately. Typically you want to drive at a log switch every 30 minutes. Also verify checkpointing parameters such as fast_start_mttr_target
2. If you see the holder is:
a user session (so no background process)
the holder is constantly changing
the wait event of the holder is 'control file parallel write'
Then it is most likely that the contention for the CF enqueue is caused by DML on a NOLOGGING object.
When performing DML operations using either NOLOGGING or UNRECOVERABLE option, then oracle records the unrecoverable SCN in the controlfiles. Typically you will see an increase in waits appearing for 'control file parallel write' as well however the session is not blocked for this wait event but rather the session performing the controlfile write will be holding the CF enqueue and the other sessions performing the unrecoverable (nologging) operation will be waiting to get a CF enqueue to update the controlfile with the unrecoverable SCN.
So if you have an object with the NOLOGGING option, it is normal to see CF enqueue contention...
The following operations can make use of no-logging mode:
direct load (SQL*Loader)
direct-load INSERT
CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... SPLIT PARTITION
ALTER INDEX ... REBUILD
ALTER INDEX ... REBUILD PARTITION
INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line
3. Check if the archive destination (log_archive_dest_n) are accessible, you may need to involve System/Storage admins.
If you are using NFS filesystem for the archive destinations then make sure there is no issue with nfs as this can lead to log switch hanging and that leads to CF enqueue as the lock holder will be either LGWR or ARCn processes
理解如下:
•当holder的对象是后台进程:LGWR、CKPT、ARCn