I tried to find what enqueues are and what the types of enqueues we have in
Oracle are.
Oracle
enqueue waits indicate that a seesion is waiting for a lock that is held by
another user (or sessions).
This
wait event indicates a wait for a lock that is held by another session (or
sessions) in an incompatible mode to the requested mode.
For
Oracle 10g and later, the Enqueue wait event has been separated into more than
200 unique wait events, which each include more specific information about the
related lock type. In 11g, we have more additional wait events (apprx 300
unique wait events).
The enqueues
wait events are available in table called x$ksqst and the aggregated table is v$enqueue_stat.
select 'enq:'||ksqsttyp||'-'||ksqstrsn ||' ' "Equeue Type",ksqstexpl "Description " from x$ksqst order by ksqsttyp;
Equeue locks
|
Description
|
enq:AD-deallocate AU
|
Synchronizes accesses to a specific ASM disk AU
|
enq:AD-allocate AU
|
Synchronizes accesses to a specific ASM disk AU
|
enq:AF-task serialization
|
This enqueue is used to serialize access to an
advisor task
|
enq:AG-contention
|
Synchronizes generation use of a particular
workspace
|
enq:AM-rollback COD reservation
|
Reserve a Rollback COD entry
|
enq:AM-shutdown
|
Prevent DB instance registration during ASM instance
shutdown
|
enq:AM-client registration
|
Registers DB instance to ASM client state object
hash
|
enq:AO-contention
|
Synchornizes access to objects and scalar variables
|
enq:AS-service activation
|
Synchronizes new service activation
|
enq:AS-modify service
|
modifying service attributes
|
enq:AT-contention
|
Serializes 'alter tablespace' operations
|
enq:AU-audit index file
|
lock held to operate on the audit index file
|
enq:AW-AW generation lock
|
In-use generation state for a particular workspace
|
enq:AW-user access for AW
|
Synchronizes user accesses to a particular workspace
|
enq:AW-AW state lock
|
Row lock synchronization for the AW$ table
|
enq:AW-AW$ table lock
|
Global access synchronization to the AW$ table
|
enq:BF-allocation contention
|
Allocate a bloom filter in a parallel statement
|
enq:BF-PMON Join Filter cleanup
|
PMON bloom filter recovery
|
enq:BR-proxy-copy
|
Lock held to allow cleanup from backup mode during
an RMAN proxy-copy backup
|
enq:BR-file shrink
|
Lock held to prevent file from decreasing in
physical size during RMAN backup
|
enq:CF-contention
|
Synchronizes accesses to the controlfile
|
enq:CI-contention
|
Coordinates cross-instance function invocations
|
enq:CL-drop label
|
Synchronizes accesses to label cache when dropping a
label
|
enq:CL-compare labels
|
Synchronizes accesses to label cache for label
comparison
|
enq:CM-gate
|
serialize access to instance enqueue
|
enq:CM-instance
|
indicate ASM diskgroup is mounted
|
enq:CN-race with init
|
during descriptor initialization
|
enq:CN-race with reg
|
during transaction commit to see concurrent
registrations
|
enq:CN-race with txn
|
during registration
|
enq:CT-state change gate 2
|
Lock held while enabling or disabling change
tracking in RAC
|
enq:CT-reading
|
Lock held to ensure that change tracking data
remains in
existence until a reader is done with it |
enq:CT-state change gate 1
|
Lock held while enabling or disabling change
tracking in RAC
|
enq:CT-state
|
Lock held while enabling or disabling change
tracking,
to ensure that it is only enabled or disabled by one user at a time |
enq:CT-change stream ownership
|
Lock held by one instance while change tracking is
enabled, to guarantee access to thread-specific resources |
enq:CT-local space management
|
Lock held during change tracking space management
operations that affect just the data for one thread |
enq:CT-global space management
|
Lock held during change tracking space management
operations that affect the entire change tracking file |
enq:CT-CTWR process start/stop
|
Lock held to ensure that only one CTWR process is
started in a single instance
|
enq:CU-contention
|
Recovers cursors in case of death while compiling
|
enq:DB-contention
|
Synchronizes modification of database wide
supplementallogging attributes
|
enq:DD-contention
|
Synchronizes local accesses to ASM disk groups
|
enq:DF-contention
|
Enqueue held by foreground or DBWR when
a datafile is brought online in RAC |
enq:DG-contention
|
Synchronizes accesses to ASM disk groups
|
enq:DL-contention
|
Lock to prevent index DDL during direct load
|
enq:DM-contention
|
Enqueue held by foreground or DBWR to syncrhonize
database mount/open with other operations |
enq:DN-contention
|
Serializes group number generations
|
enq:DP-contention
|
Synchronizes access to LDAP parameters
|
enq:DR-contention
|
Serializes the active distributed recovery operation
|
enq:DS-contention
|
Prevents a database suspend during LMON
reconfiguration
|
enq:DT-contention
|
Serializes changing the default temporary table
spaceand user creation
|
enq:DV-contention
|
Synchronizes access to lower-version Diana (PL/SQL
intermediate representation)
|
enq:DX-contention
|
Serializes tightly coupled distributed transaction
branches
|
enq:FA-access file
|
Synchronizes accesses to open ASM files
|
enq:FB-contention
|
Ensures that only one process can format data blcoks
in auto segment space managed tablespaces |
enq:FC-recover an ACD thread
|
SMON recovers an ACD thread
|
enq:FC-open an ACD thread
|
LGWR opens an ACD thread
|
enq:FD-Marker generation
|
Synchronization
|
enq:FD-Tablespace flashback on/off
|
Synchronization
|
enq:FD-Flashback coordinator
|
Synchronization
|
enq:FD-Flashback on/off
|
Synchronization
|
enq:FD-Restore point create/drop
|
Synchronization
|
enq:FG-serialize ACD relocate
|
only 1 process in the cluster may do ACD relocation
in a disk group
|
enq:FG-LGWR redo generation enq race
|
resolve race condition to acquire Disk Group Redo
Generation Enqueue
|
enq:FG-FG redo generation enq race
|
resolve race condition to acquire Disk Group Redo
Generation Enqueue
|
enq:FL-Flashback db command
|
Enqueue used to synchronize Flashback Database and
and deletion of flashback logs.
|
enq:FL-Flashback database log
|
Synchronization
|
enq:FM-contention
|
Synchronizes access to global file mapping state
|
enq:FP-global fob contention
|
Synchronizes various File Object(FOB) operations
|
enq:FR-contention
|
begin recovery of disk group
|
enq:FS-contention
|
Enqueue used to synchronize recovery and file operations
or synchronize dictionary check
|
enq:FT-allow LGWR writes
|
allow LGWR to generate redo in this thread
|
enq:FT-disable LGWR writes
|
prevent LGWR from generating redo in this thread
|
enq:FU-contention
|
This enqueue is used to serialize the capture of the
DB Feature
Usage and High Water Mark Statistics |
enq:HD-contention
|
Serializes accesses to ASM SGA data structures
|
enq:HP-contention
|
Synchronizes accesses to queue pages
|
enq:HQ-contention
|
Synchronizes the creation of new queue IDs
|
enq:HV-contention
|
Lock used to broker the high water mark during
parallel inserts
|
enq:HW-contention
|
Lock used to broker the high water mark during
parallel inserts
|
enq:IA-contention
|
|
enq:ID-contention
|
Lock held to prevent other processes from performing
controlfile
transaction while NID is running |
enq:IL-contention
|
Synchronizes accesses to internal label data
structures
|
enq:IM-contention for blr
|
Serializes block recovery for IMU txn
|
enq:IR-contention2
|
Synchronizes parallel instance recovery and shutdown
immediate
|
enq:IR-contention
|
Synchronizes instance recovery
|
enq:IS-contention
|
Enqueue used to synchronize instance state changes
|
enq:IT-contention
|
Synchronizes accesses to a temp object's metadata
|
enq:JD-contention
|
Synchronizes dates between job queue coordinator and
slave processes
|
enq:JI-contention
|
Lock held during materialized view operations (like
refresh, alter)
to prevent concurrent operations on the same materialized view |
enq:JQ-contention
|
Lock to prevent multiple instances from running a
single job
|
enq:JS-evt notify
|
Lock got during event notification
|
enq:JS-sch locl enqs
|
Scheduler non-global enqueues
|
enq:JS-q mem clnup lck
|
Lock obtained when cleaning up q memory
|
enq:JS-evtsub add
|
Lock got when adding subscriber to event q
|
enq:JS-evtsub drop
|
Lock got when dropping subscriber to event q
|
enq:JS-wdw op
|
Lock got when doing window open/close
|
enq:JS-job recov lock
|
Lock to recover jobs running on crashed RAC inst
|
enq:JS-job run lock - synchronize
|
Lock to prevent job from running elsewhere
|
enq:JS-contention
|
Synchronizes accesses to the job cache
|
enq:JS-queue lock
|
Lock on internal scheduler queue
|
enq:KK-context
|
Lock held by open redo thread, used by other
instances to force a log switch
|
enq:KM-contention
|
Synchronizes various Resource Manager operations
|
enq:KO-fast object checkpoint
|
Coordinates fast object checkpoint
|
enq:KP-contention
|
Synchronizes kupp process startup
|
enq:KT-contention
|
Synchronizes accesses to the current Resource
Manager plan
|
enq:MD-contention
|
Lock held during materialized view log DDL
statements
|
enq:MH-contention
|
Lock used for recovery when setting Mail Host for AQ
e-mail notifications
|
enq:MK-contention
|
changing values in enc$
|
enq:ML-contention
|
Lock used for recovery when setting Mail Port for AQ
e-mail notifications
|
enq:MN-contention
|
Synchronizes updates to the LogMiner dictionary and
prevents
multiple instances from preparing the same LogMiner session |
enq:MO-contention
|
Serialize MMON operations for restricted sessions
|
enq:MR-contention
|
Lock used to coordinate media recovery with other
uses of datafiles
|
enq:MS-contention
|
Lock held during materialized view refresh to setup
MV log
|
enq:MW-contention
|
This enqueue is used to serialize the calibration of
the manageability
schedules with the Maintenance Window |
enq:OC-contention
|
Synchronizes write accesses to the outline cache
|
enq:OL-contention
|
Synchronizes accesses to a particular outline name
|
enq:OQ-xsoqhiAlloc
|
Synchronizes access to olapi history allocation
|
enq:OQ-xsoqhiClose
|
Synchronizes access to olapi history closing
|
enq:OQ-xsoqhistrecb
|
Synchronizes access to olapi history globals
|
enq:OQ-xsoqhiFlush
|
Synchronizes access to olapi history flushing
|
enq:OQ-xsoq*histrecb
|
Synchronizes access to olapi history parameter CB
|
enq:OW-termination
|
terminate the wallet context
|
enq:OW-initialization
|
initializing the wallet context
|
enq:PD-contention
|
Prevents others from updating the same property
|
enq:PE-contention
|
Synchronizes system parameter updates
|
enq:PF-contention
|
Synchronizes accesses to the password file
|
enq:PG-contention
|
Synchronizes global system parameter updates
|
enq:PH-contention
|
Lock used for recovery when setting Proxy for AQ
HTTP notifications
|
enq:PI-contention
|
Communicates remote Parallel Execution Server
Process creation status
|
enq:PL-contention
|
Coordinates plug-in operation of transportable
tablespaces
|
enq:PR-contention
|
Synchronizes process startup
|
enq:PS-contention
|
Parallel Execution Server Process reservation and
synchronization
|
enq:PT-contention
|
Synchronizes access to ASM PST metadata
|
enq:PV-syncstart
|
Synchronizes slave start_shutdown
|
enq:PV-syncshut
|
Synchronizes instance shutdown_slvstart
|
enq:PW-flush prewarm buffers
|
Direct Load needs to flush prewarmed buffers if DBWR
0 holds enqueue
|
enq:PW-perwarm status in dbw0
|
DBWR 0 holds enqueue indicating prewarmed buffers
present in cache
|
enq:RB-contention
|
Serializes ASM rollback recovery operations
|
enq:RF-synchronization: chief
|
Anoints 1 instance's DMON as chief to other
instances' DMONs
|
enq:RF-RF - Database Automatic Disable
|
Means for detecting when database is being
automatically disabled
|
enq:RF-RF - FSFO Observed
|
Means for detecting whether database has been
observed recently
|
enq:RF-atomicity
|
Ensures atomicity of log transport setup
|
enq:RF-synch: DG Broker metadata
|
Ensures r/w atomicity of DG configuration metadata
|
enq:RF-synchronization: HC master
|
Anoints 1 instance's DMON as healthcheck master
|
enq:RF-RF - FSFO synchronization
|
Means for synchronizing Fast-Start Failover lock
operations
|
enq:RF-RF - FSFO connectivity
|
Means for detecting whether standby has connectivity
to primary
|
enq:RF-RF - FSFO wait
|
Means for waiting for lock request to timeout
|
enq:RF-RF - FSFO state
|
Means for sharing Fast-Start Failover state changes
around a RAC
|
enq:RF-synchronization: critical ai
|
Synchronizes critical apply instance among primary
instances
|
enq:RF-new AI
|
Synchronizes selection of the new apply instance
|
enq:RF-synchronization: aifo master
|
Synchronizes apply instance failure detection and
failover operation
|
enq:RN-contention
|
Coordinates nab computations of online logs during
recovery
|
enq:RO-contention
|
Coordinates flushing of multiple objects
|
enq:RO-fast object reuse
|
Coordinates fast object reuse
|
enq:RP-contention
|
Enqueue held when resilvering is needed or when
datablock is repaired from mirror
|
enq:RR-contention
|
Concurrent invocation of DBMS_WORKLOAD_* package API
|
enq:RS-read alert level
|
Lock held to read alert level
|
enq:RS-persist alert level
|
Lock held to make alert level persistent
|
enq:RS-prevent aging list update
|
Lock held to prevent aging list update
|
enq:RS-prevent file delete
|
Lock held to prevent deleting file to reclaim space
|
enq:RS-record reuse
|
Lock held to prevent file from accessing while
reusing circular record
|
enq:RS-file delete
|
Lock held to prevent file from accessing during
space reclaimation
|
enq:RS-write alert level
|
Lock held to write alert level
|
enq:RT-contention
|
Thread locks held by LGWR, DBW0, and RVWR to
indicate mounted or open status
|
enq:RU-contention
|
Serializes rolling upgrade operations
|
enq:RU-waiting
|
Results of rolling upgrade CIC
|
enq:RW-MV metadata contention
|
Lock held by CREATE/ALTER/DROP materialized
viewwhile
updating materialized view flags in detail tables |
enq:SB-contention
|
Synchronizes Logical Standby metadata operations
|
enq:SE-contention
|
Synchronizes transparent session migration
operations
|
enq:SF-contention
|
Lock used for recovery when setting Sender for AQ
e-mail notifications
|
enq:SH-contention
|
Should seldom see this contention as this Enqueue is
always acquired in no-wait mode
|
enq:SI-contention
|
Prevents multiple streams tabel instantiations
|
enq:SK-contention
|
Serialize shrink of a segment
|
enq:SQ-contention
|
Lock to ensure that only one process can replenish
the sequence cache
|
enq:SR-contention
|
Coordinates replication / streams operations
|
enq:SS-contention
|
Ensures that sort segments created during parallel
DML operations aren't prematurely cleaned up
|
enq:ST-contention
|
Synchronizes space management activities in
dictionary-managed tablespaces
|
enq:SU-contention
|
Serializes access to SaveUndo Segment
|
enq:SW-contention
|
Coordinates the 'alter system suspend' operation
|
enq:TA-contention
|
Serializes operations on undo segments and undo
tablespaces
|
enq:TB-SQL Tuning Base Cache Load
|
Synchronizes writes to the SQL Tuning Base Existence
Cache
|
enq:TB-SQL Tuning Base Cache Update
|
Synchronizes writes to the SQL Tuning Base Existence
Cache
|
enq:TC-contention
|
Lock held to guarantee uniqueness of a tablespace
checkpoint
|
enq:TC-contention2
|
Lock of setup of a unqiue tablespace checkpoint in
null mode
|
enq:TD-KTF dump entries
|
KTF dumping time/scn mappings in SMON_SCN_TIME table
|
enq:TE-KTF broadcast
|
KTF broadcasting
|
enq:TF-contention
|
Serializes dropping of a temporary file
|
enq:TL-contention
|
Serializes threshold log table read and update
|
enq:TM-contention
|
Synchronizes accesses to an object
|
enq:TO-contention
|
Synchronizes DDL and DML operations on a temp object
|
enq:TQ-TM contention
|
TM access to the queue table
|
enq:TQ-INI contention
|
TM access to the queue table
|
enq:TQ-DDL contention
|
TM access to the queue table
|
enq:TS-contention
|
Serializes accesses to temp segments
|
enq:TT-contention
|
Serializes DDL operations on tablespaces
|
enq:TW-contention
|
Lock held by one instance to wait for transactions
on all instances to finish
|
enq:TX-allocate ITL entry
|
Allocating an ITL entry in order to begin a
transaction
|
enq:TX-index contention
|
Lock held on an index during a split to prevent
other operations on it
|
enq:TX-contention
|
Lock held by a transaction to allow other
transactions to wait for it
|
enq:TX-row lock contention
|
Lock held on a particular row by a transaction to
prevent other transactions from modifying it
|
enq:UL-contention
|
Lock used by user applications
|
enq:US-contention
|
Lock held to perform DDL on the undo segment
|
enq:WA-contention
|
Lock used for recovery when setting Watermark for
memory usage in AQ notifications
|
enq:WF-contention
|
This enqueue is used to serialize the flushing of
snapshots
|
enq:WL-contention
|
Coordinates access to redo log files and archive
logs
|
enq:WP-contention
|
This enqueue handles concurrency between purging and
baselines
|
enq:WR-contention
|
Coordinates access to logs by Async LNS and ARCH/FG
|
enq:XH-contention
|
Lock used for recovery when setting No Proxy Domains
for AQ HTTP notifications
|
enq:XQ-recovery
|
prevent relocation during _recovery_asserts checking
|
enq:XQ-relocation
|
wait for recovery before doing relocation
|
enq:XR-quiesce database
|
Lock held during database quiesce
|
enq:XR-database force logging
|
Lock held during database force logging mode
|
enq:XY-contention
|
Lock used for internal testing
|
enq:ZG-contention
|
Coordinates file group operations
|
No comments:
Post a Comment