contention 等待事件

AWR报告信息如下:

contention 等待事件

从db time/Elapsed显示数据库的压力并不是很大。

contention 等待事件

每秒钟产生的redo log 6M,每小时21G,数据库的IO写压力很大。

contention 等待事件

top5等待事件:enq:CF-contention 该等待事件不是空闲等待事件;

Oracle AWR报告生成与查看

CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

Oracle AWR报告生成步骤

二、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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/703ec43b9bf942290bc04892df0130db.html