Performance Degradation as a Result of ‘enq: CF - contention‘ (Doc ID 1072417.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 20-Jan-2012***
*** Checked for relevance on 12-Apr-2016 ***
 

SYMPTOMS

You are seeing sessions waiting for 'enq: CF - contention' or this wait event is appearing in the top 5 wait events in an AWR or statspack report.
 

CAUSE

The CF enqueue can be seen during any action that requires reading the control file, the CF locks are used to serialize controlfile transactions and read and writes on shared portions of the controlfile. Typically CF locks are allocated for a very brief time and used when:

• checkpointing
• switching logfiles
• archiving redologs
• performing crash recovery
• logfile manipulation
• begin/end hot backup
• DML access for NOLOGGING objects
   

SOLUTION

To find the holder of the CF enqueue, the following query can be used :
 

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.state
from v$lock l, v$session s, v$process p
where l.sid = s.sid
and s.paddr = p.addr
and l.type='CF'
and l.lmode >= 5;

To find the session waiting to get the CF enqueue, the following query can be used :

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.state
from v$lock l, v$session s, v$process p
where l.sid = s.sid
and s.paddr = p.addr
and l.type='CF'
and l.request >= 5;

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