- DB FILE type waits – physical IO
- BUFFER type waits – Logical IO
- LOG type waits – Redo related
- PX – Parallel Query
- GC – Global Cache (RAC related)
- Undo or rollback segment related
- DB FILE type waits – physical IO
1.DB File Sequential Reads – memory starvation, non-selective indexes
The db file sequential read Oracle metric event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return.A sequential read is usually a single-block read.
2.DB File Scattered Reads – full table scans, insufficient indexing
Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA
3.Direct Path Writes – Appends, data loads
Direct path write wait event can occur when a process is writing buffers directly from PGA. The process will wait on this event for the write call to complete
4. Direct Path Reads – Parallel slaves used to retrieve data
A direct path read I/O operation reads data into the session's PGA instead of the SGA.
5 .DB File Parallel Writes – Backup and partition use
The db file parallel write wait event belongs to the Oracle Database Writer (DBWR) process since it is the only process that writes blocks from the SGA to datafiles. When it is time to write, the DBWR process compiles a set of dirty blocks, hands the batch over to the OS, and waits on the db file parallel write event for the I/O to complete.
6.DB File Parallel Reads – Partition use
This happens during recovery. It can also happen during buffer prefetching, as an optimization (rather than performing multiple single-block reads). Database blocks that need to be changed as part of recovery are read in parallel from the database.
7.DB File Single Write – File header writes, excessive data files
This event is used to wait for the writing of the file headers. Normally single writes are the writes happening on the header blocks and parallel writes are the writes in the non header blocks
8.Direct path read temp – Temp file activity (sorts, hashes, temp tables, bitmaps)
9.Direct path write temp – Temp file activity (sorts, hashes, temp tables, bitmaps)
- BUFFER type waits – Logical IO
1. free buffer waits
This wait event will happen when all buffer gets have been suspended.
This could happen when a file was read-only and is now read-write. All the existing buffers need to be invalidated since they are not linked to lock elements (needed when mounted parallel (shared)). So cache buffers are not assigned to dba's until the invalidation is finished.
This could also happen when you need a buffer from the SGA for a block in CR, READING or any of the recovery modes. Basically you will post the DBWR to make some free buffers.
This also happens after inspecting 'free buffers inspected' buffers. If no free buffer was found, Oracle waits for one second and will try to get the buffer again .
2.buffer busy wait
Buffer busy waits occur when a session cannot access a needed block because it is in use by another session.
insufficient free lists for a table, or
too few rollback segments.
3.Latch free waits
Latches are Oracle internal locking mechanisms. They prevent multiple sessions from simultaneously updating the same item within Oracle shared memory (SGA). If a session needs to acquire a latch that is held by another session, a latch free wait may occur.
1.log file parallel write
log file parallel write wait event belongs to the Log Writer (LGWR) process. When it is time to write, the LGWR process writes the redo buffer to the online redo files in parallel and waits on the log file parallel write event until the last I/O is on disk
2.log buffer space
This wait occurs when the session has to wait for space to become available in the log buffer to write new information. The LGWR periodically writes to redo log files from the log buffer and makes those log buffers available for reuse. This wait indicates that the application is generating redo information faster than LGWR process can write it to the redo files. Either the log buffer is too small, or redo log files are on disks with I/O contention
3.log file switch (archiving needed)
Waiting for a log switch because the log that the LGWR will be switching into has not been archived yet. Check the alert file to make sure that archiving has not stopped due to a failed archive write. To speed archiving, consider adding more archive processes or putting the archive files on striped disks
4.log file switch (checkpoint incomplete)
Waiting for a log switch because the session cannot wrap into the next log. Wrapping cannot be performed because the checkpoint for that log has not completed. You may see this event when the redo log files are sized too small
5.log file switch completion
This event happens when the sessions are waiting for a log switch to complete
6. log file sync
When a user session commits, the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session.
The wait time includes the writing of the log buffer and the post.
Master Note Parallel Execution Wait Events (Doc ID 1097154.1)
- GC – Global Cache (RAC related)
gc current block request
gc cr block request
gc current block 3-way
gc current block busy
gc cr block grant 2-way
- Undo or rollback segment related
Undo Related Wait Events & Known Issues (Doc ID 1575701.1)
No comments:
Post a Comment