oracle 中经常出现enq：相关的等待事件，脚本贡献出来，结果也贴下，方便查找。
SELECT 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;
11g R2中的事件如下
| Enqueue Type | Description |
|---|---|
| enq: AB - ABMR process initialized | Lock held to ensure that ABMR process is initialized |
| enq: AB - ABMR process start/stop | Lock held to ensure that only one ABMR is started in the cluster |
| enq: AD - allocate AU | Synchronizes accesses to a specific ASM disk AU |
| enq: AD - deallocate AU | Synchronizes accesses to a specific ASM disk AU |
| enq: AD - relocate AU | Synchronizes accesses to a specific ASM disk AU |
| enq: AE - lock | Prevent Dropping an edition in use |
| 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 - ASM ACD Relocation | Block ASM cache freeze |
| enq: AM - ASM Amdu Dump | Allow only one AMDU dump when block read failure |
| enq: AM - ASM File Destroy | Prevent same file deletion race |
| enq: AM - ASM Grow ACD | Serializes growing ASM ACD |
| enq: AM - ASM Password File Update | Allow one ASM password file update per cluster at a time |
| enq: AM - ASM User | Prevents a user from being dropped if it owns any open files |
| enq: AM - ASM cache freeze | Start ASM cache freeze |
| enq: AM - ASM disk based alloc/dealloc | Synchronizes disk based allocations/deallocations |
| enq: AM - ASM file descriptor | Serializes access to ASM file descriptors |
| enq: AM - ASM file relocation | Serializes file relocation shrink and drop |
| enq: AM - ASM reserved | Check id1 of call for specific purpose |
| enq: AM - background COD reservation | Reserve a background COD entry |
| enq: AM - block repair | Serializes block repairs |
| enq: AM - client registration | Registers DB instance to ASM client state object hash |
| enq: AM - disk offline | Synchronizes disk offlines |
| enq: AM - group block | ASM group block |
| enq: AM - group use | Client group use |
| enq: AM - rollback COD reservation | Reserve a rollback COD entry |
| enq: AM - shutdown | Prevent DB instance registration during ASM instance shutdown |
| enq: AO - contention | Synchornizes access to objects and scalar variables |
| enq: AP - contention | This enqueue is used to serialize the purging of SQL plan baselines |
| enq: AS - service activation | Synchronizes new service activation |
| enq: AT - contention | Serializes 'alter tablespace' operations |
| enq: AV - AVD client registration | Serialize inst reg and first DG use |
| enq: AV - add/enable first volume in DG | Serialize taking the AVD DG enqueue |
| enq: AV - persistent DG number | prevent DG number collisions |
| enq: AV - volume relocate | Serialize relocating volume extents |
| enq: AW - AW generation lock | In-use generation state for 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: AW - user access for AW | Synchronizes user accesses to a particular workspace |
| enq: AY - contention | Affinity Dictionary test affinity synchronization |
| enq: BB - 2PC across RAC instances | 2PC distributed transaction branch across RAC instances |
| enq: BF - PMON Join Filter cleanup | PMON bloom filter recovery |
| enq: BF - allocation contention | Allocate a bloom filter in a parallel statement |
| enq: BM - clonedb bitmap file write | synchronizes clonedb bitmap file operations |
| enq: BR - file shrink | Lock held to prevent file from decreasing in physical size during RMAN backup |
| enq: BR - multi-section restore header | Lock held to serialize file header access during multi-section restore |
| enq: BR - multi-section restore section | Lock held to serialize section access during multi-section restore |
| enq: BR - perform autobackup | Lock held to perform a new controlfile autobackup |
| enq: BR - proxy-copy | Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup |
| enq: BR - request autobackup | Lock held to request controlfile autobackups |
| enq: BR - space info datafile hdr update | Lock held to prevent multiple process to update the headers at the same time |
| enq: CA - contention | Synchronizes various IO calibration runs |
| enq: CF - contention | Synchronizes accesses to the controlfile |
| enq: CI - contention | Coordinates cross-instance function invocations |
| enq: CL - compare labels | Synchronizes accesses to label cache for label comparison |
| enq: CL - drop label | Synchronizes accesses to label cache when dropping a label |
| enq: CM - diskgroup dismount | serialize asm diskgroup dismount |
| 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: CO - master slave det | enqueue held be Master in Cleanout Optim |
| enq: CQ - contention | Serializes access to cleanup client query cache registrations |
| enq: CR - block range reuse ckpt | Coordinates fast block range reuse ckpt |
| enq: CT - CTWR process start/stop | Lock held to ensure that only one CTWR process is started in a single instance |
| enq: CT - change stream ownership | Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources |
| enq: CT - global space management | Lock held during change tracking space management operations that affect the entire change tracking file |
| enq: CT - local space management | Lock held during change tracking space management operations that affect just the data for one thread |
| enq: CT - reading | Lock held to ensure that change tracking data remains in existence until a reader is done with it |
| 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 1 | Lock held while enabling or disabling change tracking in RAC |
| enq: CT - state change gate 2 | Lock held while enabling or disabling change tracking in RAC |
| enq: CU - contention | Recovers cursors in case of death while compiling |
| enq: CX - TEXT: Index Specific Lock | Index Specific Lock on CTX index |
| 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: DO - Staleness Registry create | Synchronizes Staleness Registry creation |
| enq: DO - disk online | Synchronizes disk onlines and their recovery |
| enq: DO - disk online operation | Represents an active disk online operation |
| enq: DO - disk online recovery | Synchronizes disk onlines and their recovery |
| enq: DO - startup of MARK process | Synchronizes startup of MARK process |
| 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: DW - contention | Serialize in memory dispenser operations |
| 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 - open an ACD thread | LGWR opens an ACD thread |
| enq: FC - recover an ACD thread | SMON recovers an ACD thread |
| enq: FD - Flashback coordinator | Synchronization |
| enq: FD - Flashback logical operations | Synchronization |
| enq: FD - Flashback on/off | Synchronization |
| enq: FD - Marker generation | Synchronization |
| enq: FD - Restore point create/drop | Synchronization |
| enq: FD - Tablespace flashback on/off | Synchronization |
| enq: FE - contention | Serializes flashback archive recovery |
| enq: FG - FG redo generation enq race | resolve race condition to acquire Disk Group Redo Generation Enqueue |
| enq: FG - LGWR redo generation enq race | resolve race condition to acquire Disk Group Redo Generation Enqueue |
| enq: FG - serialize ACD relocate | only 1 process in the cluster may do ACD relocation in a disk group |
| enq: FL - Flashback database log | Synchronization |
| enq: FL - Flashback db command | Enqueue used to synchronize Flashback Database and and deletion of flashback logs. |
| 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: FR - recover the thread | wait for lock domain detach |
| enq: FR - use the thread | indicate this ACD thread is alive |
| 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: FX - issue ACD Xtnt Relocation CIC | ARB relocates ACD extent |
| 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 - aq sync | Scheduler evt code and AQ sync |
| enq: JS - contention | Synchronizes accesses to the job cache |
| enq: JS - evt notify | Lock got during event notification |
| 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 - 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 - q mem clnup lck | Lock obtained when cleaning up q memory |
| enq: JS - queue lock | Lock on internal scheduler queue |
| enq: JS - sch locl enqs | Scheduler non-global enqueues |
| enq: JS - wdw op | Lock got when doing window open/close |
| enq: JX - SQL statement queue | statement |
| enq: JX - cleanup of queue | release SQL statement resources |
| enq: KD - determine DBRM master | Determine DBRM master |
| 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: KQ - access ASM attribute | Synchronization of ASM cached attributes |
| 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: MR - standby role transition | Lock used to disallow concurrent standby role transition attempt |
| enq: MS - contention | Lock held during materialized view refresh to setup MV log |
| enq: MV - datafile move | Held during online datafile move operation or cleanup |
| enq: MW - contention | This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window |
| enq: MX - sync storage server info | Lock held to generate a response to the storage server information request when an instance is starting up |
| enq: OC - contention | Synchronizes write accesses to the outline cache |
| enq: OD - Serializing DDLs | Lock to prevent concurrent online DDLs |
| enq: OL - contention | Synchronizes accesses to a particular outline name |
| enq: OQ - xsoq*histrecb | Synchronizes access to olapi history parameter CB |
| enq: OQ - xsoqhiAlloc | Synchronizes access to olapi history allocation |
| enq: OQ - xsoqhiClose | Synchronizes access to olapi history closing |
| enq: OQ - xsoqhiFlush | Synchronizes access to olapi history flushing |
| enq: OQ - xsoqhistrecb | Synchronizes access to olapi history globals |
| enq: OT - TEXT: Generic Lock | CTX Generic Locks |
| enq: OW - initialization | initializing the wallet context |
| enq: OW - termination | terminate 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 - syncshut | Synchronizes instance shutdown_slvstart |
| enq: PV - syncstart | Synchronizes slave start_shutdown |
| 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: RC - Result Cache: Contention | Coordinates access to a result-set |
| enq: RD - RAC load | update RAC load info |
| enq: RE - block repair contention | Synchronize block repair/resilvering operations |
| enq: RF - DG Broker Current File ID | Identifies which configuration metadata file is current |
| enq: RF - FSFO Observer Heartbeat | Captures recent Fast-Start Failover Observer heartbeat information |
| enq: RF - FSFO Primary Shutdown suspended | Records when FSFO Primary Shutdown is suspended |
| enq: RF - RF - Database Automatic Disable | Means for detecting when database is being automatically disabled |
| enq: RF - atomicity | Ensures atomicity of log transport setup |
| enq: RF - new AI | Synchronizes selection of the new apply instance |
| enq: RF - synch: DG Broker metadata | Ensures r/w atomicity of DG configuration metadata |
| enq: RF - synchronization: aifo master | Synchronizes apply instance failure detection and failover operation |
| enq: RF - synchronization: critical ai | Synchronizes critical apply instance among primary instances |
| enq: RK - set key | wallet master key rekey |
| enq: RL - RAC wallet lock | RAC wallet lock |
| 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 - file delete | Lock held to prevent file from accessing during space reclaimation |
| 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 - read alert level | Lock held to read alert level |
| enq: RS - record reuse | Lock held to prevent file from accessing while reusing circular record |
| 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: RT - thread internal enable/disable | Thread locks held by CKPT to synchronize thread enable and disable |
| enq: RU - contention | Serializes rolling migration operations |
| enq: RU - waiting | Results of rolling migration CIC |
| enq: RW - MV metadata contention | Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables |
| enq: RX - relocate extent | Synchronizes relocating ASM extents |
| enq: RX - unlock extent | Synchronizes unlocking ASM extents |
| enq: SB - logical standby metadata | Synchronizes Logical Standby metadata operations |
| enq: SB - table instantiation | Synchronizes table instantiation and EDS 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: SJ - Slave Task Cancel | Serializes cancelling task executed by slave process |
| enq: SK - contention | Serialize shrink of a segment |
| enq: SL - escalate lock | sending lock escalate to LCK0 |
| enq: SL - get lock | sending lock req to LCK0 |
| enq: SL - get lock for undo | sending lock req for undo to LCK0 |
| enq: SO - contention | Synchronizes access to Shared Object (PL/SQL Shared Object Manager) |
| enq: SP - contention 1 | (1) due to one-off patch |
| enq: SP - contention 2 | (2) due to one-off patch |
| enq: SP - contention 3 | (3) due to one-off patch |
| enq: SP - contention 4 | (4) due to one-off patch |
| 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: TH - metric threshold evaluation | Serializes threshold in-memory chain access |
| enq: TK - Auto Task Serialization | Lock held by MMON to prevent other MMON spawning of Autotask Slave |
| enq: TK - Auto Task Slave Lockout | Serializes spawned Autotask Slaves |
| 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: TP - contention | Lock held during purge and dynamic reconfiguration of fixed tables. |
| enq: TQ - DDL contention | TM access to the queue table |
| enq: TQ - DDL-INI contention | Streams DDL on queue table |
| enq: TQ - INI contention | TM access to the queue table |
| enq: TQ - TM 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 - contention | Lock held by a transaction to allow other transactions to wait for it |
| enq: TX - index contention | Lock held on an index during a split to prevent other operations on 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: WG - delete fso | acquire lobid local enqueue when deleting fso |
| enq: WG - lock fso | acquire lobid local enqueue when locking fso |
| enq: WL - RAC-wide SGA contention | Serialize access to RAC-wide SGA |
| enq: WL - RFS global state contention | Serialize access to RFS global state |
| enq: WL - Test access/locking | Testing redo transport access/locking |
| enq: WL - contention | Coordinates access to redo log files and archive logs |
| enq: WM - WLM Plan activation | Synchronizes new WLM Plan activation |
| 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: XC - XDB Configuration | Lock obtained when incrementing XDB configuration version number |
| enq: XD - ASM disk OFFLINE | Serialize OFFLINE Exadata disk operations |
| enq: XD - ASM disk ONLINE | Serialize ONLINE Exadata disk operations |
| enq: XD - ASM disk drop/add | Serialize Auto Drop/Add Exadata disk operations |
| enq: XH - contention | Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications |
| enq: XL - fault extent map | Keep multiple processes from faulting in the same extent chunk |
| enq: XQ - purification | wait for relocation before doing block purification |
| enq: XQ - recovery | prevent relocation during _recovery_asserts checking |
| enq: XQ - relocation | wait for recovery before doing relocation |
| enq: XR - database force logging | Lock held during database force logging mode |
| enq: XR - quiesce database | Lock held during database quiesce |
| enq: XY - contention | Lock used for internal testing |
| enq: ZA - add std audit table partition | lock held to add partition to std audit table |
| enq: ZF - add fga audit table partition | lock held to add partition to fga audit table |
| enq: ZG - contention | Coordinates file group operations |
| enq: ZH - compression analysis | Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load |
| enq: ZZ - update hash tables | lock held for updating global context hash tables |