Enq: CF-contention wait event

Enq: CF-contention wait event

Background description: The user reports that the database runs slowly, but the database returns to normal again when it is viewed. The AWR Report of the past period of time is decisively viewed;

The AWR report information is as follows:

From db time/Elapsed, the pressure on the database is not very high.

The redo log generated every second is 6 MB, and every hour is 21 GB. The input/output pressure on the database is high.

Top 5 waiting events: enq: CF-contention the waiting event is not an idle waiting event;

Generate and view Oracle AWR reports

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Steps for generating an Oracle AWR report

2. Metalink's analysis of the waiting event

This problem has never been encountered. You can only turn to metalink. The details are as follows:

1. Problematic version

Oracle database-enterprise edition-VERSION 9.2.0.1 TO 11.2.0.3 [RELEASE 9.2 TO 11.2] (the current database version is 11.2.0.3)

2. Symptoms

In the awr wait report, the top 5 wait events or the wait events of v $ session_wait appear;

3. Cause

A CF queue is generated during any action to read the control file. The CF lock is used for the controlfile sequential operation and sharing part of the controlfile read and write. Usually the CF lock is assigned to a very short time and use:
• Checkpoints
• Log File Switching
• Archive online redolog
• Recovery after running crash
• Start and end of hot standby
• When DML executes an object using the nologging Option

4. Solve the Problem

Find the object currently holding the CF lock

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;

Search for objects waiting for the CF lock

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 is as follows:

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 descriming 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 should Ming the controlfile write will be holding CF enqueue and the other sessions specify 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

Understanding:
• When the holder object is a background process: LGWR, CKPT, and ARCn

Solution: The size and switching frequency of redolog. It is recommended that each log switch take about 30 minutes.
• When the holder object is a user session, it often changes and waits for the event "control file parallel write"

Solution: the database waits normally;
• Others: Check the archiving path and the waiting event due to system or storage problems;

 

V. Summary

The aw report in this case shows that the archiving logs generated by the database per hour reach 22 GB, and the online redolog size of the database is 1 GB/time, after calculation, 20 log switches are performed every hour, with an average of 3 minutes. There is a lot different from the recommended 30-minute time.
 
After communication with the business, it is found that the current database is extracting data, resulting in the occurrence of the wait event.

 

Final Solution: We recommend that you avoid data extraction during working hours to ensure that the system can run normally during working hours;

You can increase the online redolog size to 5 GB to reduce the log switching frequency;

DBA sometimes has this benefit. When everyone does not know the problem, you can describe the problem as much as possible (on the premise that it is based on facts ), if you establish enough prestige at ordinary times, it is easy for other people to cooperate with your work. At this time, your sense of accomplishment is very strong.
 

Appendix: log information and generation