CF锁oracle,enq: CF - contention 等待事件

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