ORACLE enqueue 相关等待事件描述

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 TypeDescription
enq: AB - ABMR process initializedLock held to ensure that ABMR process is initialized
enq: AB - ABMR process start/stopLock held to ensure that only one ABMR is started in the cluster
enq: AD - allocate AUSynchronizes accesses to a specific ASM disk AU
enq: AD - deallocate AUSynchronizes accesses to a specific ASM disk AU
enq: AD - relocate AUSynchronizes accesses to a specific ASM disk AU
enq: AE - lockPrevent Dropping an edition in use
enq: AF - task serializationThis enqueue is used to serialize access to an advisor task
enq: AG - contentionSynchronizes generation use of a particular workspace
enq: AM - ASM ACD RelocationBlock ASM cache freeze
enq: AM - ASM Amdu DumpAllow only one AMDU dump when block read failure
enq: AM - ASM File DestroyPrevent same file deletion race
enq: AM - ASM Grow ACDSerializes growing ASM ACD
enq: AM - ASM Password File UpdateAllow one ASM password file update per cluster at a time
enq: AM - ASM UserPrevents a user from being dropped if it owns any open files
enq: AM - ASM cache freezeStart ASM cache freeze
enq: AM - ASM disk based alloc/deallocSynchronizes disk based allocations/deallocations
enq: AM - ASM file descriptorSerializes access to ASM file descriptors
enq: AM - ASM file relocationSerializes file relocation shrink and drop
enq: AM - ASM reservedCheck id1 of call for specific purpose
enq: AM - background COD reservationReserve a background COD entry
enq: AM - block repairSerializes block repairs
enq: AM - client registrationRegisters DB instance to ASM client state object hash
enq: AM - disk offlineSynchronizes disk offlines
enq: AM - group blockASM group block
enq: AM - group useClient group use
enq: AM - rollback COD reservationReserve a rollback COD entry
enq: AM - shutdownPrevent DB instance registration during ASM instance shutdown
enq: AO - contentionSynchornizes access to objects and scalar variables
enq: AP - contentionThis enqueue is used to serialize the purging of SQL plan baselines
enq: AS - service activationSynchronizes new service activation
enq: AT - contentionSerializes 'alter tablespace' operations
enq: AV - AVD client registrationSerialize inst reg and first DG use
enq: AV - add/enable first volume in DGSerialize taking the AVD DG enqueue
enq: AV - persistent DG numberprevent DG number collisions
enq: AV - volume relocateSerialize relocating volume extents
enq: AW - AW generation lockIn-use generation state for a particular workspace
enq: AW - AW state lockRow lock synchronization for the AW$ table
enq: AW - AW$ table lockGlobal access synchronization to the AW$ table
enq: AW - user access for AWSynchronizes user accesses to a particular workspace
enq: AY - contentionAffinity Dictionary test affinity synchronization
enq: BB - 2PC across RAC instances2PC distributed transaction branch across RAC instances
enq: BF - PMON Join Filter cleanupPMON bloom filter recovery
enq: BF - allocation contentionAllocate a bloom filter in a parallel statement
enq: BM - clonedb bitmap file writesynchronizes clonedb bitmap file operations
enq: BR - file shrinkLock held to prevent file from decreasing in physical size during RMAN backup
enq: BR - multi-section restore headerLock held to serialize file header access during multi-section restore
enq: BR - multi-section restore sectionLock held to serialize section access during multi-section restore
enq: BR - perform autobackupLock held to perform a new controlfile autobackup
enq: BR - proxy-copyLock held to allow cleanup from backup mode during an RMAN proxy-copy backup
enq: BR - request autobackupLock held to request controlfile autobackups
enq: BR - space info datafile hdr updateLock held to prevent multiple process to update the headers at the same time
enq: CA - contentionSynchronizes various IO calibration runs
enq: CF - contentionSynchronizes accesses to the controlfile
enq: CI - contentionCoordinates cross-instance function invocations
enq: CL - compare labelsSynchronizes accesses to label cache for label comparison
enq: CL - drop labelSynchronizes accesses to label cache when dropping a label
enq: CM - diskgroup dismountserialize asm diskgroup dismount
enq: CM - gateserialize access to instance enqueue
enq: CM - instanceindicate ASM diskgroup is mounted
enq: CN - race with initduring descriptor initialization
enq: CN - race with regduring transaction commit to see concurrent registrations
enq: CN - race with txnduring registration
enq: CO - master slave detenqueue held be Master in Cleanout Optim
enq: CQ - contentionSerializes access to cleanup client query cache registrations
enq: CR - block range reuse ckptCoordinates fast block range reuse ckpt
enq: CT - CTWR process start/stopLock held to ensure that only one CTWR process is started in a single instance
enq: CT - change stream ownershipLock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
enq: CT - global space managementLock held during change tracking space management operations that affect the entire change tracking file
enq: CT - local space managementLock held during change tracking space management operations that affect just the data for one thread
enq: CT - readingLock held to ensure that change tracking data remains in existence until a reader is done with it
enq: CT - stateLock 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 1Lock held while enabling or disabling change tracking in RAC
enq: CT - state change gate 2Lock held while enabling or disabling change tracking in RAC
enq: CU - contentionRecovers cursors in case of death while compiling
enq: CX - TEXT: Index Specific LockIndex Specific Lock on CTX index
enq: DB - contentionSynchronizes modification of database wide supplementallogging attributes
enq: DD - contentionSynchronizes local accesses to ASM disk groups
enq: DF - contentionEnqueue held by foreground or DBWR when a datafile is brought online in RAC
enq: DG - contentionSynchronizes accesses to ASM disk groups
enq: DL - contentionLock to prevent index DDL during direct load
enq: DM - contentionEnqueue held by foreground or DBWR to syncrhonize database mount/open with other operations
enq: DN - contentionSerializes group number generations
enq: DO - Staleness Registry createSynchronizes Staleness Registry creation
enq: DO - disk onlineSynchronizes disk onlines and their recovery
enq: DO - disk online operationRepresents an active disk online operation
enq: DO - disk online recoverySynchronizes disk onlines and their recovery
enq: DO - startup of MARK processSynchronizes startup of MARK process
enq: DP - contentionSynchronizes access to LDAP parameters
enq: DR - contentionSerializes the active distributed recovery operation
enq: DS - contentionPrevents a database suspend during LMON reconfiguration
enq: DT - contentionSerializes changing the default temporary table spaceand user creation
enq: DV - contentionSynchronizes access to lower-version Diana (PL/SQL intermediate representation)
enq: DW - contentionSerialize in memory dispenser operations
enq: DX - contentionSerializes tightly coupled distributed transaction branches
enq: FA - access fileSynchronizes accesses to open ASM files
enq: FB - contentionEnsures that only one process can format data blcoks in auto segment space managed tablespaces
enq: FC - open an ACD threadLGWR opens an ACD thread
enq: FC - recover an ACD threadSMON recovers an ACD thread
enq: FD - Flashback coordinatorSynchronization
enq: FD - Flashback logical operationsSynchronization
enq: FD - Flashback on/offSynchronization
enq: FD - Marker generationSynchronization
enq: FD - Restore point create/dropSynchronization
enq: FD - Tablespace flashback on/offSynchronization
enq: FE - contentionSerializes flashback archive recovery
enq: FG - FG redo generation enq raceresolve race condition to acquire Disk Group Redo Generation Enqueue
enq: FG - LGWR redo generation enq raceresolve race condition to acquire Disk Group Redo Generation Enqueue
enq: FG - serialize ACD relocateonly 1 process in the cluster may do ACD relocation in a disk group
enq: FL - Flashback database logSynchronization
enq: FL - Flashback db commandEnqueue used to synchronize Flashback Database and and deletion of flashback logs.
enq: FM - contentionSynchronizes access to global file mapping state
enq: FP - global fob contentionSynchronizes various File Object(FOB) operations
enq: FR - contentionbegin recovery of disk group
enq: FR - recover the threadwait for lock domain detach
enq: FR - use the threadindicate this ACD thread is alive
enq: FS - contentionEnqueue used to synchronize recovery and file operations or synchronize dictionary check
enq: FT - allow LGWR writesallow LGWR to generate redo in this thread
enq: FT - disable LGWR writesprevent LGWR from generating redo in this thread
enq: FU - contentionThis enqueue is used to serialize the capture of the DB Feature           Usage and High Water Mark Statistics
enq: FX - issue ACD Xtnt Relocation CICARB relocates ACD extent
enq: HD - contentionSerializes accesses to ASM SGA data structures
enq: HP - contentionSynchronizes accesses to queue pages
enq: HQ - contentionSynchronizes the creation of new queue IDs
enq: HV - contentionLock used to broker the high water mark during parallel inserts
enq: HW - contentionLock used to broker the high water mark during parallel inserts
enq: IA - contention
enq: ID - contentionLock held to prevent other processes from performing controlfile transaction while NID is running
enq: IL - contentionSynchronizes accesses to internal label data structures
enq: IM - contention for blrSerializes block recovery for IMU txn
enq: IR - contentionSynchronizes instance recovery
enq: IR - contention2Synchronizes parallel instance recovery and shutdown immediate
enq: IS - contentionEnqueue used to synchronize instance state changes
enq: IT - contentionSynchronizes accesses to a temp object's metadata
enq: JD - contentionSynchronizes dates between job queue coordinator and slave processes
enq: JI - contentionLock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
enq: JQ - contentionLock to prevent multiple instances from running a single job
enq: JS - aq syncScheduler evt code and AQ sync
enq: JS - contentionSynchronizes accesses to the job cache
enq: JS - evt notifyLock got during event notification
enq: JS - evtsub addLock got when adding subscriber to event q
enq: JS - evtsub dropLock got when dropping subscriber to event q
enq: JS - job recov lockLock to recover jobs running on crashed RAC inst
enq: JS - job run lock - synchronizeLock to prevent job from running elsewhere
enq: JS - q mem clnup lckLock obtained when cleaning up q memory
enq: JS - queue lockLock on internal scheduler queue
enq: JS - sch locl enqsScheduler non-global enqueues
enq: JS - wdw opLock got when doing window open/close
enq: JX - SQL statement queue statement
enq: JX - cleanup of  queuerelease SQL statement resources
enq: KD - determine DBRM masterDetermine DBRM master
enq: KM - contentionSynchronizes various Resource Manager operations
enq: KO - fast object checkpointCoordinates fast object checkpoint
enq: KP - contentionSynchronizes kupp process startup
enq: KQ - access ASM attributeSynchronization of ASM cached attributes
enq: KT - contentionSynchronizes accesses to the current Resource Manager plan
enq: MD - contentionLock held during materialized view log DDL statements
enq: MH - contentionLock used for recovery when setting Mail Host for AQ e-mail notifications
enq: MK - contentionchanging values in enc$
enq: ML - contentionLock used for recovery when setting Mail Port for AQ e-mail notifications
enq: MN - contentionSynchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
enq: MO - contentionSerialize MMON operations for restricted sessions
enq: MR - contentionLock used to coordinate media recovery with other uses of datafiles
enq: MR - standby role transitionLock used to disallow concurrent standby role transition attempt
enq: MS - contentionLock held during materialized view refresh to setup MV log
enq: MV - datafile moveHeld during online datafile move operation or cleanup
enq: MW - contentionThis enqueue is used to serialize the calibration of the           manageability schedules with the Maintenance Window
enq: MX - sync storage server infoLock held to generate a response to the storage server information request when an instance is starting up
enq: OC - contentionSynchronizes write accesses to the outline cache
enq: OD - Serializing DDLsLock to prevent concurrent online DDLs
enq: OL - contentionSynchronizes accesses to a particular outline name
enq: OQ - xsoq*histrecbSynchronizes access to olapi history parameter CB
enq: OQ - xsoqhiAllocSynchronizes access to olapi history allocation
enq: OQ - xsoqhiCloseSynchronizes access to olapi history closing
enq: OQ - xsoqhiFlushSynchronizes access to olapi history flushing
enq: OQ - xsoqhistrecbSynchronizes access to olapi history globals
enq: OT - TEXT: Generic LockCTX Generic Locks
enq: OW - initializationinitializing the wallet context
enq: OW - terminationterminate the wallet context
enq: PD - contentionPrevents others from updating the same property
enq: PE - contentionSynchronizes system parameter updates
enq: PF - contentionSynchronizes accesses to the password file
enq: PG - contentionSynchronizes global system parameter updates
enq: PH - contentionLock used for recovery when setting Proxy for AQ HTTP notifications
enq: PI - contentionCommunicates remote Parallel Execution Server Process creation status
enq: PL - contentionCoordinates plug-in operation of transportable tablespaces
enq: PR - contentionSynchronizes process startup
enq: PS - contentionParallel Execution Server Process reservation and synchronization
enq: PT - contentionSynchronizes access to ASM PST metadata
enq: PV - syncshutSynchronizes instance shutdown_slvstart
enq: PV - syncstartSynchronizes slave start_shutdown
enq: PW - flush prewarm buffersDirect Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
enq: PW - perwarm status in dbw0DBWR 0 holds enqueue indicating prewarmed buffers present in cache
enq: RB - contentionSerializes ASM rollback recovery operations
enq: RC - Result Cache: ContentionCoordinates access to a result-set
enq: RD - RAC load update RAC load info
enq: RE - block repair contentionSynchronize block repair/resilvering operations
enq: RF - DG Broker Current File IDIdentifies which configuration metadata file is current
enq: RF - FSFO Observer HeartbeatCaptures recent Fast-Start Failover Observer heartbeat information
enq: RF - FSFO Primary Shutdown suspendedRecords when FSFO Primary Shutdown is suspended
enq: RF - RF - Database Automatic DisableMeans for detecting when database is being automatically disabled
enq: RF - atomicityEnsures atomicity of log transport setup
enq: RF - new AISynchronizes selection of the new apply instance
enq: RF - synch: DG Broker metadataEnsures r/w atomicity of DG configuration metadata
enq: RF - synchronization: aifo masterSynchronizes apply instance failure detection and failover operation
enq: RF - synchronization: critical aiSynchronizes critical apply instance among primary instances
enq: RK - set keywallet master key rekey
enq: RL - RAC wallet lockRAC wallet lock
enq: RN - contentionCoordinates nab computations of online logs during recovery
enq: RO - contentionCoordinates flushing of multiple objects
enq: RO - fast object reuseCoordinates fast object reuse
enq: RP - contentionEnqueue held when resilvering is needed or when datablock is repaired from mirror
enq: RR - contentionConcurrent invocation of DBMS_WORKLOAD_* package API
enq: RS - file deleteLock held to prevent file from accessing during space reclaimation
enq: RS - persist alert levelLock held to make alert level persistent
enq: RS - prevent aging list updateLock held to prevent aging list update
enq: RS - prevent file deleteLock held to prevent deleting file to reclaim space
enq: RS - read alert levelLock held to read alert level
enq: RS - record reuseLock held to prevent file from accessing while reusing circular record
enq: RS - write alert levelLock held to write alert level
enq: RT - contentionThread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
enq: RT - thread internal enable/disableThread locks held by CKPT to synchronize thread enable and disable
enq: RU - contentionSerializes rolling migration operations
enq: RU - waitingResults of rolling migration CIC
enq: RW - MV metadata contentionLock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables
enq: RX - relocate extentSynchronizes relocating ASM extents
enq: RX - unlock extentSynchronizes unlocking ASM extents
enq: SB - logical standby metadataSynchronizes Logical Standby metadata operations
enq: SB - table instantiationSynchronizes table instantiation and EDS operations
enq: SE - contentionSynchronizes transparent session migration operations
enq: SF - contentionLock used for recovery when setting Sender for AQ e-mail notifications
enq: SH - contentionShould seldom see this contention as this Enqueue is always  acquired in no-wait mode
enq: SI - contentionPrevents multiple streams tabel instantiations
enq: SJ - Slave Task CancelSerializes cancelling task executed by slave process
enq: SK - contentionSerialize shrink of a segment
enq: SL - escalate locksending lock escalate to LCK0
enq: SL - get locksending lock req to LCK0
enq: SL - get lock for undosending lock req for undo to LCK0
enq: SO - contentionSynchronizes 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 - contentionLock to ensure that only one process can replenish the sequence cache
enq: SR - contentionCoordinates replication / streams operations
enq: SS - contentionEnsures that sort segments created during parallel DML operations aren't prematurely cleaned up
enq: ST - contentionSynchronizes space management activities in dictionary-managed tablespaces
enq: SU - contentionSerializes access to SaveUndo Segment
enq: SW - contentionCoordinates the 'alter system suspend' operation
enq: TA - contentionSerializes operations on undo segments and undo tablespaces
enq: TB - SQL Tuning Base Cache LoadSynchronizes writes to the SQL Tuning Base Existence Cache
enq: TB - SQL Tuning Base Cache UpdateSynchronizes writes to the SQL Tuning Base Existence Cache
enq: TC - contentionLock held to guarantee uniqueness of a tablespace checkpoint
enq: TC - contention2Lock of setup of a unqiue tablespace checkpoint in null mode
enq: TD - KTF dump entriesKTF dumping time/scn mappings in SMON_SCN_TIME table
enq: TE - KTF broadcastKTF broadcasting
enq: TF - contentionSerializes dropping of a temporary file
enq: TH - metric threshold evaluationSerializes threshold in-memory chain access
enq: TK - Auto Task SerializationLock held by MMON to prevent other MMON spawning of Autotask Slave
enq: TK - Auto Task Slave LockoutSerializes spawned Autotask Slaves
enq: TL - contentionSerializes threshold log table read and update
enq: TM - contentionSynchronizes accesses to an object
enq: TO - contentionSynchronizes DDL and DML operations on a temp object
enq: TP - contentionLock held during purge and dynamic reconfiguration of fixed tables.
enq: TQ - DDL contentionTM access to the queue table
enq: TQ - DDL-INI contentionStreams DDL on queue table
enq: TQ - INI contentionTM access to the queue table
enq: TQ - TM contentionTM access to the queue table
enq: TS - contentionSerializes accesses to temp segments
enq: TT - contentionSerializes DDL operations on tablespaces
enq: TW - contentionLock held by one instance to wait for transactions on all instances to finish
enq: TX - allocate ITL entryAllocating an ITL entry in order to begin a transaction
enq: TX - contentionLock held by a transaction to allow other transactions to wait for it
enq: TX - index contentionLock held on an index during a split to prevent other operations on it
enq: TX - row lock contentionLock held on a particular row by a transaction to prevent other transactions from modifying it
enq: UL - contentionLock used by user applications
enq: US - contentionLock held to perform DDL on the undo segment
enq: WA - contentionLock used for recovery when setting Watermark for memory usage in AQ notifications
enq: WF - contentionThis enqueue is used to serialize the flushing of snapshots
enq: WG - delete fsoacquire lobid local enqueue when deleting fso
enq: WG - lock fsoacquire lobid local enqueue when locking fso
enq: WL - RAC-wide SGA contentionSerialize access to RAC-wide SGA
enq: WL - RFS global state contentionSerialize access to RFS global state
enq: WL - Test access/lockingTesting redo transport access/locking
enq: WL - contentionCoordinates access to redo log files and archive logs
enq: WM - WLM Plan activationSynchronizes new WLM Plan activation
enq: WP - contentionThis enqueue handles concurrency between purging and baselines
enq: WR - contentionCoordinates access to logs by Async LNS and ARCH/FG
enq: XC - XDB ConfigurationLock obtained when incrementing XDB configuration version number
enq: XD - ASM disk OFFLINESerialize OFFLINE Exadata disk operations
enq: XD - ASM disk ONLINESerialize ONLINE Exadata disk operations
enq: XD - ASM disk drop/addSerialize Auto Drop/Add Exadata disk operations
enq: XH - contentionLock used for recovery when setting No Proxy Domains for AQ HTTP notifications
enq: XL - fault extent mapKeep multiple processes from faulting in the same extent chunk
enq: XQ - purificationwait for relocation before doing block purification
enq: XQ - recoveryprevent relocation during _recovery_asserts checking
enq: XQ - relocationwait for recovery before doing relocation
enq: XR - database force loggingLock held during database force logging mode
enq: XR - quiesce databaseLock held during database quiesce
enq: XY - contentionLock used for internal testing
enq: ZA - add std audit table partitionlock held to add partition to std audit table
enq: ZF - add fga audit table partitionlock held to add partition to fga audit table
enq: ZG - contentionCoordinates file group operations
enq: ZH - compression analysisSynchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
enq: ZZ - update hash tableslock held for updating global context hash tables

Write a Reply or Comment

电子邮件地址不会被公开。 必填项已用*标注