Top 5 Timed Foreground Events - enq: JS - queue lock

Top 5 Timed Foreground Events - enq: JS - queue lock
The enq: JS – queue lock is Lock on internal scheduler queue where one has to look the jobs scheduled thru DBMS Scheduler.

An enqueue is a lock that protects a shared resource. Locks protect shared resources, such as data in a record, to prevent two people from updating the same data at the same time. An enqueue includes a queuing mechanism, which is FIFO (first in, first out). Note that Oracle's latching mechanism is not FIFO. Enqueue waits usually point to the ST enqueue, the HW enqueue, the TX4 enqueue, and the TM enqueue.

The ST enqueue is used for space management and allocation for dictionary-managed tablespaces. Use LMTs, or try to preallocate extents or at least make the next extent larger for problematic dictionary-managed tablespaces. HW enqueue’s are used with the high-water mark of a segment; manually allocating the extents can circumvent this wait. TX4s are the most common enqueue waits. TX4 enqueue waits are usually the result of one of three issues.

Just found a useful list of Oracle 11g enqueue waits in the web which I want to share with you, this gives us the first hand information of the enqueue locks.

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

Top 5 Timed Foreground Events - Library Cache Lock

Top 5 Timed Foreground Events - Library Cache Lock
This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:

• One client can prevent other clients from accessing the same object.
• The client can maintain a dependency for a long time (for example, so that no other client can change the object).

This lock is also obtained to locate an object in the library cache.
Library cache lock will be obtained on database objects referenced during parsing or compiling of SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, synonym). The lock will be released at the end of the parse or compilation.

Cursors (SQL and PL/SQL areas), pipes and any other transient objects do not use this lock.
Library cache lock is not deadlock sensitive and the operation is synchronous.

How can Library cache lock be reduced?

In general , high contention on library cache lock is usually a result of an under-sized shared pool or non-sharing of sql. Some ways of reducing the contention are:

• Reduce the reloads by increasing the shared pool size as the locks may take a long time if the pool is undersized.
• Increase sharing by setting the cursor_sharing to similar or force.
  Be aware this may change the execution plan; so setting the parameter should be thoroughly tested.
• Reduce the invalidations by running the batch jobs to collect statistics or any other maintenance jobs separately from OLTP.

Why are these two type of locks needed( library cache lock & library cache pin)?

Both locks and pins are provided to access objects in the library cache. Locks manage concurrency between processes, whereas pins manage cache coherence. In order to access an object, a process must first lock the object handle, and then pin the object heap itself.

Requests for both locks and pins will wait until granted. This is a possible source of contention, because there is no NOWAIT request mode.

By acquiring a lock on the object handle, a process can prevent other processes from accessing the object, or even finding out what type it is. It can maintain a dependency on an object without preventing other processes from accessing the object. Acquiring a lock is also the only way to locate an object in cache. A process locates and locks an object in a single operation.

If the process wants to actually examine or modify the object, then it must acquire a pin on the object itself (after acquiring a lock on the handle). Pinning the object causes information about the object and its heaps to be loaded into memory if they were not already there. This information is guaranteed to remain in memory at least until the pin is released.

Oracle needs Library Cache Lock and Library Cache Pin for the compilation/parsing of Package/Procedure/Function/View. This is to ensure that no one is using the object during compilation/parsing while changes are being made to the definition of the object and the object dropped and recreate the object with the new definition.

When a SQL statement is hard parsed by a session, the session has to acquire a library cache lock to prevent other sessions from accessing or modifying the same object. If this event accumulates a lot of time, then it may indicate that the shared pool is too small or is being flushed regularly. Otherwise it indicates that database object definitions are being changed regularly.

Apart from hard parsing, if the session wants to change the definition of the object specified in the SQL or do any modifications, then it has to acquire a library cache lock along with the library cache pin. The pin is required because it needs the dictionary information to be loaded into memory to access the same code.

Top 5 Timed Foreground Events - Library Cache Pin

Top 5 Timed Foreground Events - Library Cache Pin

This event manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock. Pin can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered as a special form of lock. A wait for a "library cache pin" implies some other session holds that PIN in an incompatible mode.

Library cache pin will be obtained on a database objects that is currently cached in the library cache (table, view, procedure, function, package, package body, trigger, index, cluster, synonym); in the library cache, a database object is cached in 2 parts: "handle" and "object"; only when the "object" part is cached is this lock held;

It is not deadlock sensitive and the operation is synchronous.

How can Library cache pin be reduced?

If "library cache pin" waits form a significant amount of the wait time then it is important to determine if this is one or two sessions waiting long periods of time or a more serious general contention issue among lots of processes:

If there is a blocking scenario where one or two processes are being blocked by one process, the reason for the process not releasing the pin needs to be determined. We could able to trace down the blocking sessions via ASH report when this event happened or one can find with the blocking locks script.

If there is general widespread waiting then the shared pool may need tuning .