Oracle Enqueue Lock Type Reference including 11g new locks

内部视图x$ksqst记录了以enqueue type分类的队列管理统计信息(Enqueue management statistics by type),ksqstwat列反映了等待次数,而ksqstwtim列反映了累积等待时间,实际上v$enqueue_stat动态性能视图的CUM_WAIT_TIME列数据也来源于ksqstwtim:

SQL> select * from v$fixed_view_definition where view_name=upper('gv$enqueue_stat');

VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------
GV$ENQUEUE_STAT
select inst_id, ksqsttyp, sum(ksqstreq), sum(ksqstwat), sum(ksqstsgt),
 sum(ksqstfgt), sum(ksqstwtm)    from X$KSQST group by inst_id, ksqsttyp having
sum(ksqstreq) > 0

SQL> desc X$KSQST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 KSQSTTYP                                           VARCHAR2(2)
 KSQSTREQ                                           NUMBER
 KSQSTWAT                                           NUMBER
 KSQSTSGT                                           NUMBER
 KSQSTFGT                                           NUMBER
 KSQSTWTM                                           NUMBER
 KSQSTRSN                                           VARCHAR2(64)
 KSQSTEXPL                                          VARCHAR2(4000)
 KSQSTEVIDX                                         NUMBER

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

/* 11g中引入了大量新的enqueue类型,在这里列出 */

SQL> select  KSQSTTYP,KSQSTEXPL from x$ksqst group by  KSQSTTYP,KSQSTEXPL order by KSQSTTYP;
(null)
AB Lock held to ensure that ABMR process is initialized
AB Lock held to ensure that only one ABMR is started in the cluster
AD Synchronizes accesses to a specific ASM disk AU
AE Prevent Dropping an edition in use
AF This enqueue is used to serialize access to an advisor task
AG Synchronizes generation use of a particular workspace
AM ASM group block
AM Allow one ASM password file update per cluster at a time
AM Allow only one AMDU dump when block read failure
AM Block ASM cache freeze
AM Check id1 of call for specific purpose
AM Client group use
AM Prevent DB instance registration during ASM instance shutdown
AM Prevent same file deletion race
AM Prevents a user from being dropped if it owns any open files
AM Registers DB instance to ASM client state object hash
AM Reserve a background COD entry
AM Reserve a rollback COD entry
AM Serializes access to ASM file descriptors
AM Serializes block repairs
AM Start ASM cache freeze
AM Synchronizes disk based allocations/deallocations
AM Synchronizes disk offlines
AO Synchornizes access to objects and scalar variables
AS Synchronizes new service activation
AT Serializes ‘alter tablespace’ operations
AV Serialize inst reg and first DG use
AV Serialize relocating volume extents
AV Serialize taking the AVD DG enqueue
AV prevent DG number collisions
AW Global access synchronization to the AW$ table
AW In-use generation state for a particular workspace
AW Row lock synchronization for the AW$ table
AW Synchronizes user accesses to a particular workspace
AY Affinity Dictionary test affinity synchronization
BB 2PC distributed transaction branch across RAC instances
BF Allocate a bloom filter in a parallel statement
BF PMON bloom filter recovery
BR Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
BR Lock held to perform a new controlfile autobackup
BR Lock held to prevent file from decreasing in physical size during RMAN backup
BR Lock held to prevent multiple process to update the headers at the same time
BR Lock held to request controlfile autobackups
BR Lock held to serialize file header access during multi-section restore
BR Lock held to serialize section access during multi-section restore
CA Synchronizes various IO calibration runs
CF Synchronizes accesses to the controlfile
CI Coordinates cross-instance function invocations
CL Synchronizes accesses to label cache for label comparison
CL Synchronizes accesses to label cache when dropping a label
CM indicate ASM diskgroup is mounted
CM serialize access to instance enqueue
CM serialize asm diskgroup dismount
CN during descriptor initialization
CN during registration
CN during transaction commit to see concurrent registrations
CO enqueue held be Master in Cleanout Optim
CQ Serializes access to cleanup client query cache registrations
CR Coordinates fast block range reuse ckpt
CT Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
CT Lock held during change tracking space management operations that affect just the data for one thread
CT Lock held during change tracking space management operations that affect the entire change tracking file
CT Lock held to ensure that change tracking data remains in existence until a reader is done with it
CT Lock held to ensure that only one CTWR process is started in a single instance
CT Lock held while enabling or disabling change tracking in RAC
CT Lock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time
CU Recovers cursors in case of death while compiling
CX Index Specific Lock on CTX index
DB Synchronizes modification of database wide supplementallogging attributes
DD Synchronizes local accesses to ASM disk groups
DF Enqueue held by foreground or DBWR when a datafile is brought online in RAC
DG Synchronizes accesses to ASM disk groups
DL Lock to prevent index DDL during direct load
DM Enqueue held by foreground or DBWR to syncrhonize database mount/open with other operations
DN Serializes group number generations
DO Represents an active disk online operation
DO Synchronizes Staleness Registry creation
DO Synchronizes disk onlines and their recovery
DO Synchronizes startup of MARK process
DP Synchronizes access to LDAP parameters
DR Serializes the active distributed recovery operation
DS Prevents a database suspend during LMON reconfiguration
DT Serializes changing the default temporary table spaceand user creation
DV Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
DW Serialize in memory dispenser operations
DX Serializes tightly coupled distributed transaction branches
FA Synchronizes accesses to open ASM files
FB Ensures that only one process can format data blcoks in auto segment space managed tablespaces
FC LGWR opens an ACD thread
FC SMON recovers an ACD thread
FD Synchronization
FE Serializes flashback archive recovery
FG only 1 process in the cluster may do ACD relocation in a disk group
FG resolve race condition to acquire Disk Group Redo Generation Enqueue
FL Enqueue used to synchronize Flashback Database and and deletion of flashback logs.
FL Synchronization
FM Synchronizes access to global file mapping state
FP Synchronizes various File Object(FOB) operations
FR begin recovery of disk group
FR indicate this ACD thread is alive
FR wait for lock domain detach
FS Enqueue used to synchronize recovery and file operations or synchronize dictionary check
FT allow LGWR to generate redo in this thread
FT prevent LGWR from generating redo in this thread
FU This enqueue is used to serialize the capture of the DB Feature           Usage and High Water Mark Statistics
FX ARB relocates ACD extent
HD Serializes accesses to ASM SGA data structures
HP Synchronizes accesses to queue pages
HQ Synchronizes the creation of new queue IDs
HV Lock used to broker the high water mark during parallel inserts
HW Lock used to broker the high water mark during parallel inserts
IA
ID Lock held to prevent other processes from performing controlfile transaction while NID is running
IL Synchronizes accesses to internal label data structures
IM Serializes block recovery for IMU txn
IR Synchronizes instance recovery
IR Synchronizes parallel instance recovery and shutdown immediate
IS Enqueue used to synchronize instance state changes
IT Synchronizes accesses to a temp object’s metadata
JD Synchronizes dates between job queue coordinator and slave processes
JI Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
JQ Lock to prevent multiple instances from running a single job
JS Lock got during event notification
JS Lock got when adding subscriber to event q
JS Lock got when doing window open/close
JS Lock got when dropping subscriber to event q
JS Lock obtained when cleaning up q memory
JS Lock on internal scheduler queue
JS Lock to prevent job from running elsewhere
JS Lock to recover jobs running on crashed RAC inst
JS Scheduler evt code and AQ sync
JS Scheduler non-global enqueues
JS Synchronizes accesses to the job cache
JX statement
JX release SQL statement resources
KD Determine DBRM master
KM Synchronizes various Resource Manager operations
KO Coordinates fast object checkpoint
KP Synchronizes kupp process startup
KQ Synchronization of ASM cached attributes
KT Synchronizes accesses to the current Resource Manager plan
MD Lock held during materialized view log DDL statements
MH Lock used for recovery when setting Mail Host for AQ e-mail notifications
MK changing values in enc$
ML Lock used for recovery when setting Mail Port for AQ e-mail notifications
MN Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MO Serialize MMON operations for restricted sessions
MR Lock used to coordinate media recovery with other uses of datafiles
MR Lock used to disallow concurrent standby role transition attempt
MS Lock held during materialized view refresh to setup MV log
MV Held during online datafile move operation or cleanup
MW This enqueue is used to serialize the calibration of the           manageability schedules with the Maintenance Window
MX Lock held to generate a response to the storage server information request when an instance is starting up
OC Synchronizes write accesses to the outline cache
OD Lock to prevent concurrent online DDLs
OL Synchronizes accesses to a particular outline name
OQ Synchronizes access to olapi history allocation
OQ Synchronizes access to olapi history closing
OQ Synchronizes access to olapi history flushing
OQ Synchronizes access to olapi history globals
OQ Synchronizes access to olapi history parameter CB
OT CTX Generic Locks
OW initializing the wallet context
OW terminate the wallet context
PD Prevents others from updating the same property
PE Synchronizes system parameter updates
PF Synchronizes accesses to the password file
PG Synchronizes global system parameter updates
PH Lock used for recovery when setting Proxy for AQ HTTP notifications
PI Communicates remote Parallel Execution Server Process creation status
PL Coordinates plug-in operation of transportable tablespaces
PR Synchronizes process startup
PS Parallel Execution Server Process reservation and synchronization
PT Synchronizes access to ASM PST metadata
PV Synchronizes instance shutdown_slvstart
PV Synchronizes slave start_shutdown
PW DBWR 0 holds enqueue indicating prewarmed buffers present in cache
PW Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
RB Serializes ASM rollback recovery operations
RC Coordinates access to a result-set
RD update RAC load info
RE Synchronize block repair/resilvering operations
RF Captures recent Fast-Start Failover Observer heartbeat information
RF Ensures atomicity of log transport setup
RF Ensures r/w atomicity of DG configuration metadata
RF Identifies which configuration metadata file is current
RF Means for detecting when database is being automatically disabled
RF Records when FSFO Primary Shutdown is suspended
RF Synchronizes apply instance failure detection and failover operation
RF Synchronizes critical apply instance among primary instances
RF Synchronizes selection of the new apply instance
RK wallet master key rekey
RL RAC wallet lock
RN Coordinates nab computations of online logs during recovery
RO Coordinates fast object reuse
RO Coordinates flushing of multiple objects
RP Enqueue held when resilvering is needed or when datablock is repaired from mirror
RR Concurrent invocation of DBMS_WORKLOAD_* package API
RS Lock held to make alert level persistent
RS Lock held to prevent aging list update
RS Lock held to prevent deleting file to reclaim space
RS Lock held to prevent file from accessing during space reclaimation
RS Lock held to prevent file from accessing while reusing circular record
RS Lock held to read alert level
RS Lock held to write alert level
RT Thread locks held by CKPT to synchronize thread enable and disable
RT Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
RU Results of rolling migration CIC
RU Serializes rolling migration operations
RW Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables
RX Synchronizes relocating ASM extents
SB Synchronizes Logical Standby metadata operations
SB Synchronizes table instantiation and EDS operations
SE Synchronizes transparent session migration operations
SF Lock used for recovery when setting Sender for AQ e-mail notifications
SH Should seldom see this contention as this Enqueue is always  acquired in no-wait mode
SI Prevents multiple streams tabel instantiations
SJ Serializes cancelling task executed by slave process
SK Serialize shrink of a segment
SL sending lock escalate to LCK0
SL sending lock req for undo to LCK0
SL sending lock req to LCK0
SO Synchronizes access to Shared Object (PL/SQL Shared Object Manager)
SP (1) due to one-off patch
SP (2) due to one-off patch
SP (3) due to one-off patch
SP (4) due to one-off patch
SQ Lock to ensure that only one process can replenish the sequence cache
SR Coordinates replication / streams operations
SS Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
ST Synchronizes space management activities in dictionary-managed tablespaces
SU Serializes access to SaveUndo Segment
SW Coordinates the ‘alter system suspend’ operation
TA Serializes operations on undo segments and undo tablespaces
TB Synchronizes writes to the SQL Tuning Base Existence Cache
TC Lock held to guarantee uniqueness of a tablespace checkpoint
TC Lock of setup of a unqiue tablespace checkpoint in null mode
TD KTF dumping time/scn mappings in SMON_SCN_TIME table
TE KTF broadcasting
TF Serializes dropping of a temporary file
TH Serializes threshold in-memory chain access
TK Lock held by MMON to prevent other MMON spawning of Autotask Slave
TK Serializes spawned Autotask Slaves
TL Serializes threshold log table read and update
TM Synchronizes accesses to an object
TO Synchronizes DDL and DML operations on a temp object
TP Lock held during purge and dynamic reconfiguration of fixed tables.
TQ Streams DDL on queue table
TQ TM access to the queue table
TS Serializes accesses to temp segments
TT Serializes DDL operations on tablespaces
TW Lock held by one instance to wait for transactions on all instances to finish
TX Allocating an ITL entry in order to begin a transaction
TX Lock held by a transaction to allow other transactions to wait for it
TX Lock held on a particular row by a transaction to prevent other transactions from modifying it
TX Lock held on an index during a split to prevent other operations on it
UL Lock used by user applications
US Lock held to perform DDL on the undo segment
WA Lock used for recovery when setting Watermark for memory usage in AQ notifications
WF This enqueue is used to serialize the flushing of snapshots
WG acquire lobid local enqueue when deleting fso
WG acquire lobid local enqueue when locking fso
WL Coordinates access to redo log files and archive logs
WL Serialize access to RAC-wide SGA
WL Serialize access to RFS global state
WL Testing redo transport access/locking
WM Synchronizes new WLM Plan activation
WP This enqueue handles concurrency between purging and baselines
WR Coordinates access to logs by Async LNS and ARCH/FG
XC Lock obtained when incrementing XDB configuration version number
XD Serialize Auto Drop/Add Exadata disk operations
XD Serialize OFFLINE Exadata disk operations
XD Serialize ONLINE Exadata disk operations
XH Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
XL Keep multiple processes from faulting in the same extent chunk
XQ prevent relocation during _recovery_asserts checking
XQ wait for recovery before doing relocation
XQ wait for relocation before doing block purification
XR Lock held during database force logging mode
XR Lock held during database quiesce
XY Lock used for internal testing
ZA lock held to add partition to std audit table
ZF lock held to add partition to fga audit table
ZG Coordinates file group operations
ZH Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
ZZ lock held for updating global context hash tables

早期metalink上提供的一个lock type list(Values for the TYPE Column: System Types)

System Type Description System Type Description
BL Buffer hash table instance NA..NZ Library cache pin instance (A..Z = namespace)
CF Control file schema global enqueue PF Password File
CI Cross-instance function invocation instance PI, PS Parallel operation
CU Cursor bind PR Process startup
DF Data file instance QA..QZ Row cache instance (A..Z = cache)
DL Direct loader parallel index create RT Redo thread global enqueue
DM Mount/startup db primary/secondary instance SC System commit number instance
DR Distributed recovery process SM SMON
DX Distributed transaction entry SN Sequence number instance
FS File set SQ Sequence number enqueue
HW Space management operations on a specific segment SS Sort segment
IN Instance number ST Space transaction enqueue
IR Instance recovery serialization global enqueue SV Sequence number value
IS Instance state TA Generic enqueue
IV Library cache invalidation instance TS Temporary segment enqueue (ID2=0)
JQ Job queue TS New block allocation enqueue (ID2=1)
KK Thread kick TT Temporary table enqueue
LA .. LP Library cache lock instance lock (A..P = namespace) UN User name
MM Mount definition global enqueue US Undo segment DDL
MR Media recovery WL Being-written redo log instance
Enqueue Type Description
enq: AD – allocate AU Synchronizes accesses to a specific OSM disk AU
enq: AD – deallocate AU Synchronizes accesses to a specific OSM 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: AO – contention Synchronizes access to objects and scalar variables
enq: AS – contention Synchronizes new service activation
enq: AT – contention Serializes ‘alter tablespace’ operations
enq: AW – AW$ table lock Global access synchronization to the AW$ table
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: BR – file shrink Lock held to prevent file from decreasing in physical size during RMAN backup
enq: BR – proxy-copy Lock held to allow cleanup from backup mode during an RMAN proxy-copy 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 OSM disk group is mounted
enq: CT – global space management Lock held during change tracking space management operations that affect the entire change tracking file
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 – 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 – CTWR process start/stop Lock held to ensure that only one CTWR process is started in a single instance
enq: CT – state change gate 1 Lock held while enabling or disabling change tracking in RAC
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: CU – contention Recovers cursors in case of death while compiling
enq: DB – contention Synchronizes modification of database wide supplemental logging 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 synchronize 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 space and 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 blocks in auto segment space managed tablespaces
enq: FC – open an ACD thread LGWR opens an ACD thread
enq: FC – recover an ACD thread SMON recovers an ACD thread
enq: FD – Marker generation Synchronization
enq: FD – Flashback coordinator Synchronization
enq: FD – Tablespace flashback on/off Synchronization
enq: FD – Flashback on/off 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 database log Synchronization
enq: FL – Flashback db command Enqueue used to synchronize Flashback Database and deletion of flashback logs.
enq: FM – contention Synchronizes access to global file mapping state
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 – contention Synchronizes instance recovery
enq: IR – contention2 Synchronizes parallel instance recovery and shutdown immediate
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 – contention Synchronizes accesses to the job cache
enq: JS – coord post lock Lock for coordinator posting
enq: JS – global wdw lock Lock acquired when doing wdw ddl
enq: JS – job chain evaluate lock Lock when job chain evaluated for steps to create
enq: JS – q mem clnup lck Lock obtained when cleaning up q memory
enq: JS – slave enq get lock2 Get run info locks before slv objget
enq: JS – slave enq get lock1 Slave locks exec pre to sess strt
enq: JS – running job cnt lock3 Lock to set running job count epost
enq: JS – running job cnt lock2 Lock to set running job count epre
enq: JS – running job cnt lock Lock to get running job count
enq: JS – coord rcv lock Lock when coord receives msg
enq: JS – queue lock Lock on internal scheduler queue
enq: JS – job run lock – synchronize Lock to prevent job from running elsewhere
enq: JS – job recov lock Lock to recover jobs running on crashed RAC inst
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: 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: 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: 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: 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 – perwarm status in dbw0 DBWR 0 holds enqueue indicating prewarmed buffers present in cache
enq: PW – flush prewarm buffers Direct Load needs to flush pre-warmed buffers if DBWR 0 holds enqueue
enq: RB – contention Serializes OSM rollback recovery operations
enq: RF – synch: per-SGA Broker metadata Ensures r/w atomicity of DG configuration metadata per unique SGA
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: chief Anoints 1 instance’s DMON as chief to other instances’ DMONs
enq: RF – synchronization: HC master Anoints 1 instance’s DMON as health check master
enq: RF – synchronization: aifo master Synchronizes apply instance failure detection and fail over operation
enq: RF – atomicity Ensures atomicity of log transport setup
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 data block is repaired from mirror
enq: RS – file delete Lock held to prevent file from accessing during space reclamation
enq: RS – persist alert level Lock held to make alert level persistent
enq: RS – write alert level Lock held to write alert level
enq: RS – read alert level Lock held to read alert level
enq: RS – prevent aging list update Lock held to prevent aging list update
enq: RS – record reuse Lock held to prevent file from accessing while reusing circular record
enq: RS – prevent file delete Lock held to prevent deleting file to reclaim space
enq: RT – contention Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
enq: SB – contention Synchronizes Logical Standby metadata 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 table 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 Update Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TB – SQL Tuning Base Cache Load 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 unique 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 – DDL contention TM access to the queue table
enq: TQ – INI 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 – 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: 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: 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: XH – contention Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
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

Posted

in

by

Tags:

Comments

One response to “Oracle Enqueue Lock Type Reference including 11g new locks”

  1. Sunitha Avatar

    the latch waited for the most. You still need to start your doiangsis from wait interface, using Snapper or WaitProf for example. Only if these tools show significant latch waits, you should drill down

Leave a Reply

Your email address will not be published. Required fields are marked *