Performance Degradation as a Result of 'enq: CF - contention

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

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

Select 1 of the following 2 options to work around this problem:

1). Enable LOGGING for all objects in both object and database level.

or

2). Set event 10359 to level 1 to skip updating the unrecoverable SCN's in the 
control file.