Over 1 and a half year ago I posted the enqueue waits for Oracle 10g. Well, a lot has changed in 11g including the enqueue waits. This is the new list of enqueue waits in 11gR2:
| Group | Enqueue Type | Description |
|---|---|---|
| Auto BMR | enq: AB – ABMR process initialized | Lock held to ensure that ABMR process is initialized |
| Auto BMR | enq: AB – ABMR process start/stop | Lock held to ensure that only one ABMR is started in the cluster |
| ASM Disk AU Lock | enq: AD – allocate AU | Synchronizes accesses to a specific ASM disk AU |
| ASM Disk AU Lock | enq: AD – deallocate AU | Synchronizes accesses to a specific ASM disk AU |
| ASM Disk AU Lock | enq: AD – relocate AU | Synchronizes accesses to a specific ASM disk AU |
| Edition Lock | enq: AE – lock | Prevent Dropping an edition in use |
| Advisor Framework | enq: AF – task serialization | This enqueue is used to serialize access to an advisor task |
| Analytic Workspace Generation | enq: AG – contention | Synchronizes generation use of a particular workspace |
| ASM Enqueue | enq: AM – ASM ACD Relocation | Block ASM cache freeze |
| ASM Enqueue | enq: AM – ASM Amdu Dump | Allow only one AMDU dump when block read failure |
| ASM Enqueue | enq: AM – ASM File Destroy | Prevent same file deletion race |
| ASM Enqueue | enq: AM – ASM Password File Update | Allow one ASM password file update per cluster at a time |
| ASM Enqueue | enq: AM – ASM User | Prevents a user from being dropped if it owns any open files |
| ASM Enqueue | enq: AM – ASM cache freeze | Start ASM cache freeze |
| ASM Enqueue | enq: AM – PST split check | Synchronizes check for Storage (PST) split in disk groups |
| ASM Enqueue | enq: AM – background COD reservation | Reserve a background COD entry |
| ASM Enqueue | enq: AM – client registration | Registers DB instance to ASM client state object hash |
| ASM Enqueue | enq: AM – disk offline | Synchronizes disk offlines |
| ASM Enqueue | enq: AM – group block | ASM group block |
| ASM Enqueue | enq: AM – group use | Client group use |
| ASM Enqueue | enq: AM – rollback COD reservation | Reserve a rollback COD entry |
| ASM Enqueue | enq: AM – shutdown | Prevent DB instance registration during ASM instance shutdown |
| MultiWriter Object Access | enq: AO – contention | Synchornizes access to objects and scalar variables |
| Service Operations | enq: AS – service activation | Synchronizes new service activation |
| Alter Tablespace | enq: AT – contention | Serializes ‘alter tablespace’ operations |
| Audit index file | enq: AU – audit index file | lock held to operate on the audit index file |
| ASM volume locks | enq: AV – AVD client registration | Serialize inst reg and first DG use |
| ASM volume locks | enq: AV – add/enable first volume in DG | Serialize taking the AVD DG enqueue |
| ASM volume locks | enq: AV – persistent DG number | prevent DG number collisions |
| ASM volume locks | enq: AV – volume relocate | Serialize relocating volume extents |
| Analytic Workspace | enq: AW – AW generation lock | In-use generation state for a particular workspace |
| Analytic Workspace | enq: AW – AW state lock | Row lock synchronization for the AW$ table |
| Analytic Workspace | enq: AW – AW$ table lock | Global access synchronization to the AW$ table |
| Analytic Workspace | enq: AW – user access for AW | Synchronizes user accesses to a particular workspace |
| KSXA Test Affinity Dictionary | enq: AY – contention | Affinity Dictionary test affinity synchronization |
| Global Transaction Branch | enq: BB – 2PC across RAC instances | 2PC distributed transaction branch across RAC instances |
| BLOOM FILTER | enq: BF – PMON Join Filter cleanup | PMON bloom filter recovery |
| BLOOM FILTER | enq: BF – allocation contention | Allocate a bloom filter in a parallel statement |
| Backup/Restore | enq: BR – file shrink | Lock held to prevent file from decreasing in physical size during RMAN backup |
| Backup/Restore | enq: BR – multi-section restore header | Lock held to serialize file header access during multi-section restore |
| Backup/Restore | enq: BR – multi-section restore section | Lock held to serialize section access during multi-section restore |
| Backup/Restore | enq: BR – perform autobackup | Lock held to perform a new controlfile autobackup |
| Backup/Restore | enq: BR – proxy-copy | Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup |
| Backup/Restore | enq: BR – request autobackup | Lock held to request controlfile autobackups |
| Backup/Restore | enq: BR – space info datafile hdr update | Lock held to prevent multiple process to update the headers at the same time |
| Calibration | enq: CA – contention | Synchronizes various IO calibration runs |
| Controlfile Transaction | enq: CF – contention | Synchronizes accesses to the controlfile |
| Cross-Instance Call Invocation | enq: CI – contention | Coordinates cross-instance function invocations |
| Label Security cache | enq: CL – compare labels | Synchronizes accesses to label cache for label comparison |
| Label Security cache | enq: CL – drop label | Synchronizes accesses to label cache when dropping a label |
| ASM Instance Enqueue | enq: CM – gate | serialize access to instance enqueue |
| ASM Instance Enqueue | enq: CM – instance | indicate ASM diskgroup is mounted |
| KTCN REG enq | enq: CN – race with init | during descriptor initialization |
| KTCN REG enq | enq: CN – race with reg | during transaction commit to see concurrent registrations |
| KTCN REG enq | enq: CN – race with txn | during registration |
| KTUCLO Master Slave enq | enq: CO – master slave det | enqueue held be Master in Cleanout Optim |
| Cleanup querycache registrations | enq: CQ – contention | Serializes access to cleanup client query cache registrations |
| Reuse Block Range | enq: CR – block range reuse ckpt | Coordinates fast block range reuse ckpt |
| Block Change Tracking | enq: CT – CTWR process start/stop | Lock held to ensure that only one CTWR process is started in a single instance |
| Block Change Tracking | enq: CT – change stream ownership | Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources |
| Block Change Tracking | enq: CT – global space management | Lock held during change tracking space management operations that affect the entire change tracking file |
| Block Change Tracking | enq: CT – local space management | Lock held during change tracking space management operations that affect just the data for one thread |
| Block Change Tracking | enq: CT – reading | Lock held to ensure that change tracking data remains in existence until a reader is done with it |
| Block Change Tracking | 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 |
| Block Change Tracking | enq: CT – state change gate 1 | Lock held while enabling or disabling change tracking in RAC |
| Block Change Tracking | enq: CT – state change gate 2 | Lock held while enabling or disabling change tracking in RAC |
| Cursor | enq: CU – contention | Recovers cursors in case of death while compiling |
| DbsDriver | enq: DB – contention | Synchronizes modification of database wide supplementallogging attributes |
| ASM Local Disk Group | enq: DD – contention | Synchronizes local accesses to ASM disk groups |
| Datafile Online in RAC | enq: DF – contention | Enqueue held by foreground or DBWR when a datafile is brought online in RAC |
| ASM Disk Group Modification | enq: DG – contention | Synchronizes accesses to ASM disk groups |
| Direct Loader Index Creation | enq: DL – contention | Lock to prevent index DDL during direct load |
| Database Mount/Open | enq: DM – contention | Enqueue held by foreground or DBWR to syncrhonize database mount/open with other operations |
| Diskgroup number generator | enq: DN – contention | Serializes group number generations |
| ASM Disk Online Lock | enq: DO – Staleness Registry create | Synchronizes Staleness Registry creation |
| ASM Disk Online Lock | enq: DO – disk online | Synchronizes disk onlines and their recovery |
| ASM Disk Online Lock | enq: DO – disk online operation | Represents an active disk online operation |
| ASM Disk Online Lock | enq: DO – disk online recovery | Synchronizes disk onlines and their recovery |
| ASM Disk Online Lock | enq: DO – startup of MARK process | Synchronizes startup of MARK process |
| LDAP Parameter | enq: DP – contention | Synchronizes access to LDAP parameters |
| Distributed Recovery | enq: DR – contention | Serializes the active distributed recovery operation |
| Database Suspend | enq: DS – contention | Prevents a database suspend during LMON reconfiguration |
| Default Temporary Tablespace | enq: DT – contention | Serializes changing the default temporary table spaceand user creation |
| Diana Versioning | enq: DV – contention | Synchronizes access to lower-version Diana (PL/SQL intermediate representation) |
| In memory Dispenser | enq: DW – contention | Serialize in memory dispenser operations |
| Distributed Transaction | enq: DX – contention | Serializes tightly coupled distributed transaction branches |
| ASM File Access Lock | enq: FA – access file | Synchronizes accesses to open ASM files |
| Format Block | enq: FB – contention | Ensures that only one process can format data blcoks in auto segment space managed tablespaces |
| Disk Group Chunk Mount | enq: FC – open an ACD thread | LGWR opens an ACD thread |
| Disk Group Chunk Mount | enq: FC – recover an ACD thread | SMON recovers an ACD thread |
| Flashback Database | enq: FD – Flashback coordinator | Synchronization |
| Flashback Database | enq: FD – Flashback logical operations | Synchronization |
| Flashback Database | enq: FD – Flashback on/off | Synchronization |
| Flashback Database | enq: FD – Marker generation | Synchronization |
| Flashback Database | enq: FD – Restore point create/drop | Synchronization |
| Flashback Database | enq: FD – Tablespace flashback on/off | Synchronization |
| KTFA Recovery | enq: FE – contention | Serializes flashback archive recovery |
| ACD Relocation Gate Enqueue | enq: FG – FG redo generation enq race | resolve race condition to acquire Disk Group Redo Generation Enqueue |
| ACD Relocation Gate Enqueue | enq: FG – LGWR redo generation enq race | resolve race condition to acquire Disk Group Redo Generation Enqueue |
| ACD Relocation Gate Enqueue | enq: FG – serialize ACD relocate | only 1 process in the cluster may do ACD relocation in a disk group |
| Flashback database log | enq: FL – Flashback database log | Synchronization |
| Flashback database log | enq: FL – Flashback db command | Enqueue used to synchronize Flashback Database and and deletion of flashback logs. |
| File Mapping | enq: FM – contention | Synchronizes access to global file mapping state |
| File Object | enq: FP – global fob contention | Synchronizes various File Object(FOB) operations |
| Disk Group Recovery | enq: FR – contention | begin recovery of disk group |
| Disk Group Recovery | enq: FR – recover the thread | wait for lock domain detach |
| Disk Group Recovery | enq: FR – use the thread | indicate this ACD thread is alive |
| File Set / Dictionary Check | enq: FS – contention | Enqueue used to synchronize recovery and file operations or synchronize dictionary check |
| Disk Group Redo Generation | enq: FT – allow LGWR writes | allow LGWR to generate redo in this thread |
| Disk Group Redo Generation | enq: FT – disable LGWR writes | prevent LGWR from generating redo in this thread |
| DBFUS | enq: FU – contention | This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics |
| ACD Xtnt Info CIC | enq: FX – issue ACD Xtnt Relocation CIC | ARB relocates ACD extent |
| ASM Disk Header | enq: HD – contention | Serializes accesses to ASM SGA data structures |
| Queue Page | enq: HP – contention | Synchronizes accesses to queue pages |
| Hash Queue | enq: HQ – contention | Synchronizes the creation of new queue IDs |
| Direct Loader High Water Mark | enq: HV – contention | Lock used to broker the high water mark during parallel inserts |
| Segment High Water Mark | enq: HW – contention | Lock used to broker the high water mark during parallel inserts |
| Internal | enq: IA – contention | |
| NID | enq: ID – contention | Lock held to prevent other processes from performing controlfile transaction while NID is running |
| Label Security | enq: IL – contention | Synchronizes accesses to internal label data structures |
| Kti blr lock | enq: IM – contention for blr | Serializes block recovery for IMU txn |
| Instance Recovery | enq: IR – contention | Synchronizes instance recovery |
| Instance Recovery | enq: IR – contention2 | Synchronizes parallel instance recovery and shutdown immediate |
| Instance State | enq: IS – contention | Enqueue used to synchronize instance state changes |
| In-Mem Temp Table Meta Creation | enq: IT – contention | Synchronizes accesses to a temp object’s metadata |
| Job Queue Date | enq: JD – contention | Synchronizes dates between job queue coordinator and slave processes |
| Materialized View | enq: JI – contention | Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view |
| Job Queue | enq: JQ – contention | Lock to prevent multiple instances from running a single job |
| Job Scheduler | enq: JS – aq sync | Scheduler evt code and AQ sync |
| Job Scheduler | enq: JS – contention | Synchronizes accesses to the job cache |
| Job Scheduler | enq: JS – evt notify | Lock got during event notification |
| Job Scheduler | enq: JS – evtsub add | Lock got when adding subscriber to event q |
| Job Scheduler | enq: JS – evtsub drop | Lock got when dropping subscriber to event q |
| Job Scheduler | enq: JS – job recov lock | Lock to recover jobs running on crashed RAC inst |
| Job Scheduler | enq: JS – job run lock – synchronize | Lock to prevent job from running elsewhere |
| Job Scheduler | enq: JS – q mem clnup lck | Lock obtained when cleaning up q memory |
| Job Scheduler | enq: JS – queue lock | Lock on internal scheduler queue |
| Job Scheduler | enq: JS – sch locl enqs | Scheduler non-global enqueues |
| Job Scheduler | enq: JS – wdw op | Lock got when doing window open/close |
| SQL STATEMENT QUEUE | enq: JX – SQL statement queue | statement |
| SQL STATEMENT QUEUE | enq: JX – cleanup of queue | release SQL statement resources |
| Scheduler Master DBRM | enq: KD – determine DBRM master | Determine DBRM master |
| Scheduler | enq: KM – contention | Synchronizes various Resource Manager operations |
| Multiple Object Checkpoint | enq: KO – fast object checkpoint | Coordinates fast object checkpoint |
| Kupp Process Startup | enq: KP – contention | Synchronizes kupp process startup |
| ASM Attributes Enque | enq: KQ – access ASM attribute | Synchronization of ASM cached attributes |
| Scheduler Plan | enq: KT – contention | Synchronizes accesses to the current Resource Manager plan |
| Materialized View Log DDL | enq: MD – contention | Lock held during materialized view log DDL statements |
| AQ Notification Mail Host | enq: MH – contention | Lock used for recovery when setting Mail Host for AQ e-mail notifications |
| Master Key | enq: MK – contention | changing values in enc$ |
| AQ Notification Mail Port | enq: ML – contention | Lock used for recovery when setting Mail Port for AQ e-mail notifications |
| LogMiner | enq: MN – contention | Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session |
| MMON restricted session | enq: MO – contention | Serialize MMON operations for restricted sessions |
| Media Recovery | enq: MR – contention | Lock used to coordinate media recovery with other uses of datafiles |
| Media Recovery | enq: MR – standby role transition | Lock used to disallow concurrent standby role transition attempt |
| Materialized View Refresh Log | enq: MS – contention | Lock held during materialized view refresh to setup MV log |
| Online Datafile Move | enq: MV – datafile move | Held during online datafile move operation or cleanup |
| MWIN Schedule | enq: MW – contention | This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window |
| ksz synch | enq: MX – sync storage server info | Lock held to generate a response to the storage server information request when an instance is starting up |
| Outline Cache | enq: OC – contention | Synchronizes write accesses to the outline cache |
| Online DDLs | enq: OD – Serializing DDLs | Lock to prevent concurrent online DDLs |
| Outline Name | enq: OL – contention | Synchronizes accesses to a particular outline name |
| OLAPI Histories | enq: OQ – xsoq*histrecb | Synchronizes access to olapi history parameter CB |
| OLAPI Histories | enq: OQ – xsoqhiAlloc | Synchronizes access to olapi history allocation |
| OLAPI Histories | enq: OQ – xsoqhiClose | Synchronizes access to olapi history closing |
| OLAPI Histories | enq: OQ – xsoqhiFlush | Synchronizes access to olapi history flushing |
| OLAPI Histories | enq: OQ – xsoqhistrecb | Synchronizes access to olapi history globals |
| Encryption Wallet | enq: OW – initialization | initializing the wallet context |
| Encryption Wallet | enq: OW – termination | terminate the wallet context |
| Property Lock | enq: PD – contention | Prevents others from updating the same property |
| Parameter | enq: PE – contention | Synchronizes system parameter updates |
| Password File | enq: PF – contention | Synchronizes accesses to the password file |
| Global Parameter | enq: PG – contention | Synchronizes global system parameter updates |
| AQ Notification Proxy | enq: PH – contention | Lock used for recovery when setting Proxy for AQ HTTP notifications |
| Remote PX Process Spawn Status | enq: PI – contention | Communicates remote Parallel Execution Server Process creation status |
| Transportable Tablespace | enq: PL – contention | Coordinates plug-in operation of transportable tablespaces |
| Process Startup | enq: PR – contention | Synchronizes process startup |
| PX Process Reservation | enq: PS – contention | Parallel Execution Server Process reservation and synchronization |
| ASM Partnership and Status Table | enq: PT – contention | Synchronizes access to ASM PST metadata |
| KSV slave startup | enq: PV – syncshut | Synchronizes instance shutdown_slvstart |
| KSV slave startup | enq: PV – syncstart | Synchronizes slave start_shutdown |
| Buffer Cache PreWarm | enq: PW – flush prewarm buffers | Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue |
| Buffer Cache PreWarm | enq: PW – perwarm status in dbw0 | DBWR 0 holds enqueue indicating prewarmed buffers present in cache |
| ASM Rollback Recovery | enq: RB – contention | Serializes ASM rollback recovery operations |
| Result Cache: Enqueue | enq: RC – Result Cache: Contention | Coordinates access to a result-set |
| RAC Load | enq: RD – RAC load | update RAC load info |
| Block Repair/Resilvering | enq: RE – block repair contention | Synchronize block repair/resilvering operations |
| Data Guard Broker | enq: RF – DG Broker Current File ID | Identifies which configuration metadata file is current |
| Data Guard Broker | enq: RF – FSFO Observer Heartbeat | Captures recent Fast-Start Failover Observer heartbeat information |
| Data Guard Broker | enq: RF – RF – Database Automatic Disable | Means for detecting when database is being automatically disabled |
| Data Guard Broker | enq: RF – atomicity | Ensures atomicity of log transport setup |
| Data Guard Broker | enq: RF – new AI | Synchronizes selection of the new apply instance |
| Data Guard Broker | enq: RF – synch: DG Broker metadata | Ensures r/w atomicity of DG configuration metadata |
| Data Guard Broker | enq: RF – synchronization: aifo master | Synchronizes apply instance failure detection and failover operation |
| Data Guard Broker | enq: RF – synchronization: critical ai | Synchronizes critical apply instance among primary instances |
| wallet_set_mkey | enq: RK – set key | wallet master key rekey |
| RAC Encryption Wallet Lock | enq: RL – RAC wallet lock | RAC wallet lock |
| Redo Log Nab Computation | enq: RN – contention | Coordinates nab computations of online logs during recovery |
| Multiple Object Reuse | enq: RO – contention | Coordinates flushing of multiple objects |
| Multiple Object Reuse | enq: RO – fast object reuse | Coordinates fast object reuse |
| Resilver / Repair | enq: RP – contention | Enqueue held when resilvering is needed or when datablock is repaired from mirror |
| Workload Capture and Replay | enq: RR – contention | Concurrent invocation of DBMS_WORKLOAD_* package API |
| Reclaimable Space | enq: RS – file delete | Lock held to prevent file from accessing during space reclaimation |
| Reclaimable Space | enq: RS – persist alert level | Lock held to make alert level persistent |
| Reclaimable Space | enq: RS – prevent aging list update | Lock held to prevent aging list update |
| Reclaimable Space | enq: RS – prevent file delete | Lock held to prevent deleting file to reclaim space |
| Reclaimable Space | enq: RS – read alert level | Lock held to read alert level |
| Reclaimable Space | enq: RS – record reuse | Lock held to prevent file from accessing while reusing circular record |
| Reclaimable Space | enq: RS – write alert level | Lock held to write alert level |
| Redo Thread | enq: RT – contention | Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status |
| Redo Thread | enq: RT – thread internal enable/disable | Thread locks held by CKPT to synchronize thread enable and disable |
| Rolling Migration | enq: RU – contention | Serializes rolling migration operations |
| Rolling Migration | enq: RU – waiting | Results of rolling migration CIC |
| Materialized View Flags | enq: RW – MV metadata contention | Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables |
| ASM Extent Relocation Lock | enq: RX – relocate extent | Synchronizes relocating ASM extents |
| LogicalStandby | enq: SB – contention | Synchronizes Logical Standby metadata operations |
| Session Migration | enq: SE – contention | Synchronizes transparent session migration operations |
| AQ Notification Sender | enq: SF – contention | Lock used for recovery when setting Sender for AQ e-mail notifications |
| Active Session History Flushing | enq: SH – contention | Should seldom see this contention as this Enqueue is always acquired in no-wait mode |
| Streams Table Instantiation | enq: SI – contention | Prevents multiple streams tabel instantiations |
| KTSJ Slave Task Cancel | enq: SJ – Slave Task Cancel | Serializes cancelling task executed by slave process |
| Shrink Segment | enq: SK – contention | Serialize shrink of a segment |
| Serialize Lock request | enq: SL – escalate lock | sending lock escalate to LCK0 |
| Serialize Lock request | enq: SL – get lock | sending lock req to LCK0 |
| Serialize Lock request | enq: SL – get lock for undo | sending lock req for undo to LCK0 |
| Shared Object | enq: SO – contention | Synchronizes access to Shared Object (PL/SQL Shared Object Manager) |
| Spare Enqueue | enq: SP – contention | (1) due to one-off patch |
| Spare Enqueue | enq: SP – contention 2 | (2) due to one-off patch |
| Spare Enqueue | enq: SP – contention 3 | (3) due to one-off patch |
| Spare Enqueue | enq: SP – contention 4 | (4) due to one-off patch |
| Sequence Cache | enq: SQ – contention | Lock to ensure that only one process can replenish the sequence cache |
| Synchronized Replication | enq: SR – contention | Coordinates replication / streams operations |
| Sort Segment | enq: SS – contention | Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up |
| Space Transaction | enq: ST – contention | Synchronizes space management activities in dictionary-managed tablespaces |
| SaveUndo Segment | enq: SU – contention | Serializes access to SaveUndo Segment |
| Suspend Writes | enq: SW – contention | Coordinates the ‘alter system suspend’ operation |
| Instance Undo | enq: TA – contention | Serializes operations on undo segments and undo tablespaces |
| SQL Tuning Base Existence Cache | enq: TB – SQL Tuning Base Cache Load | Synchronizes writes to the SQL Tuning Base Existence Cache |
| SQL Tuning Base Existence Cache | enq: TB – SQL Tuning Base Cache Update | Synchronizes writes to the SQL Tuning Base Existence Cache |
| Tablespace Checkpoint | enq: TC – contention | Lock held to guarantee uniqueness of a tablespace checkpoint |
| Tablespace Checkpoint | enq: TC – contention2 | Lock of setup of a unqiue tablespace checkpoint in null mode |
| KTF map table enqueue | enq: TD – KTF dump entries | KTF dumping time/scn mappings in SMON_SCN_TIME table |
| KTF broadcast | enq: TE – KTF broadcast | KTF broadcasting |
| Temporary File | enq: TF – contention | Serializes dropping of a temporary file |
| Threshold Chain | enq: TH – metric threshold evaluation | Serializes threshold in-memory chain access |
| Auto Task Serialization | enq: TK – Auto Task Serialization | Lock held by MMON to prevent other MMON spawning of Autotask Slave |
| Auto Task Serialization | enq: TK – Auto Task Slave Lockout | Serializes spawned Autotask Slaves |
| Log Lock | enq: TL – contention | Serializes threshold log table read and update |
| DML | enq: TM – contention | Synchronizes accesses to an object |
| Temp Object | enq: TO – contention | Synchronizes DDL and DML operations on a temp object |
| Runtime Fixed Table Purge | enq: TP – contention | Lock held during purge and dynamic reconfiguration of fixed tables. |
| Queue table enqueue | enq: TQ – DDL contention | TM access to the queue table |
| Queue table enqueue | enq: TQ – DDL-INI contention | Streams DDL on queue table |
| Queue table enqueue | enq: TQ – INI contention | TM access to the queue table |
| Queue table enqueue | enq: TQ – TM contention | TM access to the queue table |
| Temporary Segment | enq: TS – contention | Serializes accesses to temp segments |
| Tablespace | enq: TT – contention | Serializes DDL operations on tablespaces |
| Cross-Instance Transaction | enq: TW – contention | Lock held by one instance to wait for transactions on all instances to finish |
| Transaction | enq: TX – allocate ITL entry | Allocating an ITL entry in order to begin a transaction |
| Transaction | enq: TX – contention | Lock held by a transaction to allow other transactions to wait for it |
| Transaction | enq: TX – index contention | Lock held on an index during a split to prevent other operations on it |
| Transaction | enq: TX – row lock contention | Lock held on a particular row by a transaction to prevent other transactions from modifying it |
| User-defined | enq: UL – contention | Lock used by user applications |
| Undo Segment | enq: US – contention | Lock held to perform DDL on the undo segment |
| AQ Notification Watermark | enq: WA – contention | Lock used for recovery when setting Watermark for memory usage in AQ notifications |
| AWR Flush | enq: WF – contention | This enqueue is used to serialize the flushing of snapshots |
| Write gather local enqueue | enq: WG – delete fso | acquire lobid local enqueue when deleting fso |
| Write gather local enqueue | enq: WG – lock fso | acquire lobid local enqueue when locking fso |
| Being Written Redo Log | enq: WL – RAC-wide SGA contention | Serialize access to RAC-wide SGA |
| Being Written Redo Log | enq: WL – RFS global state contention | Serialize access to RFS global state |
| Being Written Redo Log | enq: WL – Test access/locking | Testing redo transport access/locking |
| Being Written Redo Log | enq: WL – contention | Coordinates access to redo log files and archive logs |
| WLM Plan Operations | enq: WM – WLM Plan activation | Synchronizes new WLM Plan activation |
| AWR Purge | enq: WP – contention | This enqueue handles concurrency between purging and baselines |
| LNS archiving log | enq: WR – contention | Coordinates access to logs by Async LNS and ARCH/FG |
| XDB Configuration | enq: XC – XDB Configuration | Lock obtained when incrementing XDB configuration version number |
| Auto Online Exadata disks | enq: XD – ASM disk drop/add | Serialize Auto Drop/Add Exadata disk operations |
| AQ Notification No-Proxy | enq: XH – contention | Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications |
| ASM Extent Fault Lock | enq: XL – fault extent map | Keep multiple processes from faulting in the same extent chunk |
| ASM Extent Relocation Enqueue | enq: XQ – purification | wait for relocation before doing block purification |
| ASM Extent Relocation Enqueue | enq: XQ – recovery | prevent relocation during _recovery_asserts checking |
| ASM Extent Relocation Enqueue | enq: XQ – relocation | wait for recovery before doing relocation |
| Quiesce / Force Logging | enq: XR – database force logging | Lock held during database force logging mode |
| Quiesce / Force Logging | enq: XR – quiesce database | Lock held during database quiesce |
| Internal Test | enq: XY – contention | Lock used for internal testing |
| Audit Partition | enq: ZA – add std audit table partition | lock held to add partition to std audit table |
| FGA Partition | enq: ZF – add fga audit table partition | lock held to add partition to fga audit table |
| File Group | enq: ZG – contention | Coordinates file group operations |
| Compression Analyzer | enq: ZH – compression analysis | Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load |
| Global Context Action | enq: ZZ – update hash tables | lock held for updating global context hash tables |
For those who made it all to the bottom here a little extra:
While I just found the enqueue waits for 10g on the internet I managed to find out how to retrieve that information. And believe it or not – it’s available just over a simple query:
SELECT eq_name "Group", ev.name "Enqueue Type", eq.req_description "Description" FROM v$enqueue_statistics eq, v$event_name ev WHERE eq.event#=ev.event# ORDER BY ev.name;
Very nicely done.
Thanks for the post.
Amazingly enough, we have an instance going crazy on CO enqueue failures. I gather this means the PQO needs a bit of slapping around, but…we shall see….let the digging commence!
Outstanding! I stumbled across this today while searching for the definition of one of the enques (output from snapper). Using your query I wrote a quick PL/SQL block to output the definitions as a JavaScript associative array and I’ve now added to my AWR Formatter:
http://tylermuth.wordpress.com/2011/04/20/awr-formatter/
So, all of these events are linked to their definitions when viewing an AWR now.
Regards,
Tyler
Glad that it helped you Tyler! Btw: You don’t wanna port your AWR formatter to FireFox, do you?! 😉
This is very attention-grabbing, You are an overly skilled blogger. I have joined your feed and look forward to in the hunt for extra of your excellent post. Also, I have shared your website in my social networks
Slightly tidier output for your query:
SET LINES 200
COL group_name FORMAT A32 HEADING “Group”
COL name FORMAT A41 HEADING “Enqueue Type”
COL description FORMAT a120 HEADING “Description”
SELECT eq_name group_name, ev.name name, eq.req_description description
FROM v$enqueue_statistics eq, v$event_name ev
WHERE eq.event#=ev.event#
ORDER BY ev.name
/