Deep analysis of wait events in Oracle

Overview

Wait events can be divided into two main categories, namely, Idle (IDLE) Wait events and non idle (non-idle) wait events.

1). Idle Wait Event

Oracle is waiting for some kind of work, not paying too much attention to this part of the event when diagnosing and optimizing the database.

2). Non Idle Wait Event

Activities specifically directed at ORACLE refer to the waiting events that occur during database tasks or application runs, which need to be paid attention to when adjusting the database.

Wait events in Oracle 10g can view information about wait events through the V$event_name view.

Common wait Events

Buffer Busy Waits

Essentially, the generation of this wait event only shows that a session is waiting for a Buffer (data block), but there are many reasons for this phenomenon.

The two common types are:

When a session view modifies a block of data, but the block is being modified by another session
When a session needs to read a block of data, but the data block is being read into memory by another session.
The smallest unit of Oracle operations is block, and even if you want to modify a record, you need to manipulate the block where the record is located.

When a session modifies a block of data, it is accomplished by following these steps:

(1) Obtain this data block in an exclusive way (Latch)

(2) Modify this data block.

(3) Release of Latch.

Buffer latch

The location of the memory block is recorded in a hash list (cache buffer chains).

When a session needs to access a block of data, it first searches the hash list, obtains the address of the block from the list, and accesses the required data block through the address, which Oracle uses a latch to protect its integrity.

When a session needs to access this list, a Latch is required to ensure that the list does not change in the browsing of the session.

The main reasons for the wait events that generate the buffer latch are:

(1) Buffer chains is too long, causing the session to search this list takes too long, so that other sessions in the waiting state.
(2) The same block of data is frequently accessed, which is what we usually call the hot fast problem.
Control file Parallel Write

There are many reasons to control file writes frequently, such as:

(1) Log switching is too frequent, causing the control file information to be frequently updated accordingly.

(2) system I/O bottlenecks, causing all I/O to appear waiting.

When log switching is too frequent in the system, consider increasing the log file size appropriately to reduce log switching frequency.

When the system has a large number of control file parallel write wait events, you can mitigate I/O contention by storing copies of the control files on different physical disks, such as reducing the number of copies of the controlled files