内部视图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 |
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