This event is used to wait for a reply from one of the background processes. Wait Time: The wait time is specified by the user and is indicated by the parameter timeout. Parameter Description from_process The background process for which the session is waiting. The wait is for a reply to an IPC message sent by…
This event indicates that all message blocks are in use and that the session had to wait for a message block to become available.
The background processes (LGWR, DBWR, LMS0) use this event to indicate that they are idle and are waiting for the foreground processes to send them an IPC message to do some work. Wait Time: Up to 3 seconds. The parameter timeout shows the true sleep time. Parameter Description timeout The amount of time that the session waits…
Writing redo records to the redo log files from the log buffer. Wait Time: Time it takes for the I/Os to complete. Even though redo records are written in parallel, the parallel write is not complete until the last I/O is on disk. Parameter Description files Number of files to be written blocks Number of…
Waiting for space in the log buffer because the session is writing data into the log buffer faster than LGWR can write it out. Consider making the log buffer bigger if it is small, or moving the log files to faster disks such as striped disks.
Used local archiving of online redo logs (for a production database) or standby redo logs (for a standby database). When the archiving process exhausts its I/O buffers because all of them are being used for on-going I/O’s, the wait for an available I/O buffer is captured in this system wait event.
Library cache mutexes protect hash buckets within the shared pool. Each hash bucket contains a number of cursors. The mutex must be held in exclusive mode before any of the structures that it protects can be changed. This wait event is often associated with high parse activity.
While trying to find an index key in an index block, Oracle noticed that the index block was being split. Oracle will wait for the split to finish and try to find the key again. Wait Time: The session will yield the CPU, so there is no actual waiting time Parameter Description rootdba The root…
This wait event can occur for several reasons. All buffer gets have been suspended. This could happen when a file was read-only and is now read/write. All the existing buffers must be invalidated since they are not linked to lock elements (needed when mounted parallel (shared)). So cache buffers are not assigned to data block…
This wait event can occur for several reasons. If one user is wanting to update or delete a row or rows that another session is modifying. The session holding the lock will release it when it performs a COMMIT or ROLLBACK. If a session is waiting due to potential duplicates in a UNIQUE index. If…
Oracle Database Internal Mechanism 