内部视图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 |
Leave a Reply to Sunitha Cancel reply