V$SESSION_WAIT can be used to find what wait events sessions are hanging on.
From 10g wait event columns have been included in V$SESSION. This makes it much easier to get an instant view of what events sessions are waiting on.
Enqueues are memory structures that serialise access to database resources and are associated with a session or transaction. In other words an enqueue is a lock that protects a shared resource, such as data, in order to prevent processes updating the same data simultaneously.
DBA_BLOCKERS and DBA_WAITERS are two very useful views for tracking down which session is blocking another when enqueue waits occur. They are not installed by default by can be done so by running the catblock.sql script as follows:
However, these only appear to work well with single instance databases and alternative scripts need to be run to find locking issues across all on instances on a RAC database:
Find blocking sessions across a RAC database.
Find waiting sessions across a RAC database:
There are many types of enqueues; however the most common ones are:
TX Transaction Locks – are acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete. TX locks can occur when multiple sessions want to update the same row of data; as part of enforcing integrity from unique or primary key constraints; multiple sessions trying to update the same bitmap index fragment; or insufficient ITL slots to perform the change when multiple sessions try to update data in the same block. Insufficient ITL slots are probably the most common cause of this type of locking issue. When a transaction needs to lock a row of data it places details regarding which row(s) are locked in the Interested Transaction Lists (ITL) section of the block header. When another session wants to lock a row it checks what rows are already locked in the ITL. If the relevant row is not locked it creates another ITL slot detailing the new lock details otherwise if the row is locked it waits for it to become free. However if there is not sufficient number of ITL slots available (controlled by INITRANS and MAXTRANS settings on the Table / Index) or there is insufficient space in the block to create a new ITL slot then the transaction must wait. This situation can be eased by increasing the value of INITRANS/MAXTRANS (recommended to leave MAXTRANS at default value) and/or increasing the PCTFREE value on the relevant Tables / Indexes. Though setting these values too high will mean space could be wasted when there is not sufficient room to put more data into the block. Look for a high value of ITL Waits in the V$SEGSTAT table for candidates where ITL contention is occurring.
DML Enqueue – occurs during DML to prevent DDL to the affected object. Most common cause of a TM enqueue is un-indexed foreign keys. Therefore it is important to ensure that all foreign keys are indexed to avoid this type of locking issue.
Space Transaction Enqueue – is used for space management and allocation for dictionary-managed tables. Use Locally Managed Tablespaces, or try to preallocate extents to avoid this type of locking issue.
High-Water Enqueue – is used with the high-water mark of a segment, manually allocating extents to the relevant object can help avoid this type of locking issue.
Identify the P3 value for the HW enqueue event:
Determine the correct File and Block Number using above P3 value:
Determine the object to which this block belongs to using the above FILE# and BLOCK#:
Additionally, if the lock contention is currrently observed, we can find out the underlying segment using the following query:
From 10g the wait event details are greatly improved and you can easily see which latch is causing an issue; e.g. wait event in V$SESSION_WAIT will show up as something like:
latch: cache buffers chains
In 9i the only event details that were supplied are that the session was waiting on a latch free event. The following SQL can be used to find which latch the session is hanging on (obviously uncomment the SID bit if you want a specific session):
If you have "cache buffer chains" latches then this is to do with hot blocks.
The particular objects with these hot blocks can be identified using the following SQL:
The above SQL should be run multiple times; if the same object appears in the list frequently then these are the ones which are likely to have hot block contention.
Personally I like to track which SQL is being run when the cache buffer chains waits occur; for example in10g you can run:
Identifying these SQL statements and then tuning them to reduce the number of logical IOs required I have found to be the best way to tackle cache buffers chains waits.
If tuning is not possible then it is advisable to rebuild the relevant tables / indexes with a higher pct free setting to reduce the amount of data which will be stored on each block.
Also look in V$SEGSTAT to find objects with a high number of “buffer busy waits” as these are also candidates for the cause of hot block contention.
If you encounter shared pool or library cache type latches these usually point to a fault an application design issue. Perhaps queries are being executed frequently with different literal values rather than using bind variables or you have multiple versions (e.g. same SQL but against tables in different schemas) of the identical SQL.
The following SQL is useful for identifying statements which could be causing shared pool issues:
Finding SQL using literal values:
Finding SQL with a high version count:
Find Statements using a high amount of shared pool memory:
Note: See “The problem with bind variables” before deciding whether to eliminate the use of literal values.
Oracle uses library cache pins to manage library cache concurrency; to identify sessions waiting on a library cache pin use the following SQL:
P1RAW is the handle of the library cache object which the waiting session wants to acquire a pin on. The actual object being waited on can be found using the following SQL:
The below SQL is the main query to find blockers; though you will need the P1RAW value from above:
Mode is the mode in which the pin is wanted. This is a number thus:
2 - Share mode
3 - Exclusive mode
An X request (3) will be blocked by any pins held S mode (2) on the object.
An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.
Typically you will need to kill the session holding the pin to release the blocking event.
However, frequent library cache pin waits particularly in a LIVE system could point to an application issue with objects being altered or recompiled while application users are working on the same objects.