Enq of oracle waiting event: CF-contentio
Enqueue is a locking mechanism to protect shared resources and avoid data damage due to concurrent operations. Enqueue uses a queuing mechanism, i.e. FIFO (First In First Out), to control the use of resources. A wait event enq: CF -control occurs whenever a control file needs to be read, CF locks are used to serialize controlfile transactions, and the lock is used when reading and writing control files. Usually the lock allocation time is very short, for example, if the lock is allocated in the following event, then enq: CF - contention waiting event may also occur:
l Checkpoint
l Redo Logo file switch
Archive of l Redo lofileg
l Perform instance recovery
Operation redo logfile
l Start and end of hot standby
l Nlogging DML operations on things
If something has the nlogging attribute set, then the wait event is more likely to occur if:
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
Query the holder of this waiting event
Zhengzhou infertility hospital: yyk.39.net/zz3/zonghe/1d427.html
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;
Query the waiter for this waiting event
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;
Cause analysis and solutions:
If the holder is a background process, such as lgwr, ckpt, arcn, etc., check redo log size, switch frequency, check fast_start_mttr_target setting, check archive path availability.
If the holder is a foreground process, then it is mostly because DML or DDL is occurring on the nlogging thing. At this time, due to the nlogging attribute, oracle needs to record the recoverable SCN in the control file, typically accompanied by enq: CF-contention and usually control file parallel write. This session holds CF locks during the writing process. If other sessions also want to update the control file, then they have to wait.
