Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand the oracle waiting event enq". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand the oracle waiting event enq".

Enqueue is a locking mechanism to protect shared resources to avoid data damage due to concurrent operations. Enqueue uses a queuing mechanism, namely FIFO (first-in, first-out), to control the use of resources. The wait event enq: CF-contention,CF locks is used to serialize the controlfile transaction when any action needs to read the control file, using the lock when reading and writing to the control file. Usually the allocation time of the lock is very short, for example, if the lock is assigned in the following event, the enq: CF-contention wait event may also occur:

L Checkpoint

Switching of l Redo Logfile

Archiving of l Redo lofileg

L perform instance recovery

L operate redo logfile

L Hot standby start and end

L DML operation of Nologging things

If something has the nologging property set, the wait event is more likely to be generated by the following actions:

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 the 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.typewriter CF'

And l.lmode > = 5

Query the waiter of the 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.typewriter CF'

And l.request > = 5

Cause analysis and solutions:

If holder is a background process, such as lgwr,ckpt,arcn, check the size of redo log, switch the frequency, check the setting of fast_start_mttr_target, and check whether the archive path is available.

If holder is a foreground process, then it is mostly due to the fact that DML or DDL is happening on nologging. At this time, because of the nologging attribute, oracle needs to record unrecoverable SCN to the control file, typically accompanied by enq: CF-contention is usually accompanied by control file parallel write, this session holds CF locks during the writing process, and other sessions have to wait if they also want to update the control file.

Thank you for your reading, the above is the content of "how to understand oracle waiting event enq". After the study of this article, I believe you have a deeper understanding of how to understand oracle waiting event enq, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.