Last updated on July 28th, 2016 at 05:59 pm
This is part I in series of Oracle Wait Events That Everyone Should Know
Here are some of the Common Oracle wait events that everyone should Know.
You can find which event session is waiting for it by following query
select event from V$session_wait where sid=&1
I am trying to explain few common Oracle wait events ,there causes and resolutions
The process is waiting on an enqueue (a lock you can see in v$lock). This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user. The blockers can be find out by using following query
select * from dba_waiters
library cache pin
The process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. This happens when you are compiling or parsing a PL/SQL object or a view. Avoid compiling PL/SQL object or view at high usage time to avoid this wait event
library cache load lock
The process is waiting for the opportunity to load an object or a piece of an object into the library cache. (Only one process can load an object or a piece of an object at a time.)
The process is waiting for a latch held by another process. (This wait event does not apply to processes that are spinning while waiting for a latch; when a process is spinning, it is not waiting.).Latches are lightweight serialization devices used to coordinate multiuser access to shared data structures, objects, and files.
Latches are locks designed to be held for extremely short periods of time for example, the time it takes to modify an in-memory data structure. They are used to protect certain memory structures, such as the database block buffer cache or the library cache in the shared pool. Latches are typically requested internally in a ‘willing to wait’ mode. This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the operation later. Other latches may be requested in an ‘immediate’ mode, which is similar in concept to a SELECT FOR UPDATE NOWAIT, meaning that the process will go do something else, such as try to grab an equivalent sibling latch that may be free, rather than sit and wait for this latch to become available. Since many requestors may be waiting for a latch at the same time, you may see some processes waiting longer than others. Latches are assigned rather randomly, based on the luck of the draw, if you will. Whichever session asks for a latch right after it was released will get it. There is no line of latch waiters¿just a mob of waiters constantly retrying.
buffer busy waits
The process wants to access a data block that is currently not in memory, but another process has already issued an I/O request to read the block into memory. (The process is waiting for the other process to finish bringing the block into memory.). The hot blocks can be found using view v$bh