Several common wait events from Oracle

1. CPU Time

• Normal, ranked first in waiting events
• Num_cpu_sockets Number of physical CPUs
• Num_cpu_cores CPU Number of cores
• Num_cpus Number of logical CPUs

2. buffer busy waits (buffer busy wait/ read by other session)

• Generally these 2 wait events can be processed together and recommended for monitoring. This may be caused by the following actions
• Select/select---read by other session: Due to the need to read chunks of data from a data file into the buffer cache, there may be a large number of logical/physical reads; Or too little buffer cache to cause
• Select/update---Buffer busy Waits/read by other session after updating a block of data, it is possible to reconstruct the data block of the past time in undo, possibly Associated Enq:cr-contention due to a large number of Physical read/Logical reading caused
• Update/update---Buffer busy waits because the same data block is updated (not the same row, the same row is enq:tx-contention) This type of problem is caused by a hotspot block
• Insert/insert---Buffer busy waits is caused by freelist contention, you can change the tablespace to ASSM management, or increase freelist

3. sql*net Message from client, etc.

This wait event is basically one of the most common waiting events. When a session is established successfully, the client sends a request to the server side, after the server finishes processing the client request, returns the result to the client and continues to wait for the client's request, which results in a sql*net message from client wait event.
Obviously, this is an idle wait, and if the client is no longer sending requests to the server, the server side will remain in this wait event state.

4. db file sequential read/ db file scattered read

• Typically, the wait event is ranked at 4, 5 digits, and AVG Wait time should be less than 20ms on average for one time.
• DB file sequential read if the database performs an index lookup (index block access or table block access by a rowid), which is typically read by a block of data, and the data blocks read in memory are stored in a contiguous way Put The P3 parameter of the DB file sequential read wait event is generally 1.
• DB file scattered read if the database performs a full-table scan or is a full-index scan, it is common to read multiple blocks into memory (Multi block I/O), and Oracle will typically scatter these blocks in memory for performance and more efficient memory space utilization. The P3 parameter of the db file scattered Read wait event indicates the number of blocks read per I/O. The number of blocks per I/o read, with parameter Db_file_multiblock_read_count control. Generally, the application (SQL) aspect is adjusted.

5. Enq:tx-row Lock Contention

The Enq in the wait event Enq:tx-row lock contention is shorthand for enquence. Enquence is an internal lock that coordinates access to database resources. All wait events that begin with "ENQ:" indicate that the session is waiting for an internal lock to be released by another session.

Its name format is enq:enqueue_type-related_details. The enqueue_type here is Tx,related_details is row lock contention. The database Dynamic performance View V￥event_name provides a list of all wait events that begin with "Enq:".
Although there are a lot of enq:tx-row lock contention waiting in Awrrpt, these are the information you see afterwards. According to AWRRPT, we cannot just know what the request mode of the wait event is, whether it is 6 or 4.
If a database appears Enq:tx-row lock contention Wait, you can see performance views such as V￥session and v￥session_wait.

There are several cases where the Enq:tx-row lock contention is produced.
<1>in Mode 6:a session holds a row level lock,b session waiting for this lock to be released.
A different session updates or deletes the same record. (This occurs when one application was updating or deleting a row that another session was also trying to update or delete.) ）
WORKAROUND: Hold the lock session commit or rollback.

<2>in Mode 4: Unique index
A unique index exists on the table, a session inserts a value (uncommitted), and the B session then inserts the same value; After a session is committed, Enq:tx-row lock contention disappears.
WORKAROUND: Hold the lock session commit or rollback.

<3>in Mode 4:bitmap
Features derived from bitmap: A key value of a bitmap index that points to multiple rows of records, so updating a row locks all rows that the key value points to.
WORKAROUND: Commit or rollback.

<4> other reasons
It could be a primary key problem; A trigger firing attempting to insert, delete, or update a row; A problem with Initrans; Waiting for a index split to complete; Problems with bitmap indexes;updating a row already updated by another session; or something else.

6. Log file Sync

This is a wait event caused by a user session behavior, after the commit instruction of the session, it is necessary to wait for LGWR to write the redo of the transaction successfully to disk before continuing with the subsequent operation, this wait event is called log file sync.

If such an event occurs frequently, it can be judged as:

• If the commit is too many times
• I/O system issues
• Redo logs do not have to be created
• Redo log buffer is too large

7. Log file switch (archiving needed/ checkpoint incomplete)

In archive mode, this wait event occurs when an online log switch (log file switch) is in progress and the online logs that need to be switched are not archived by the archive process (ARCH). When the online log file is switched to the next log, it is necessary to ensure that the next log file has been archived by the archive process, otherwise it is not allowed to overwrite that online log information (otherwise it will result in incomplete archive log information). Such a wait event usually causes the arch process to die for some reason, such as the arch process attempting to write an archive to the destination, but without success (media failure or other reason), the arch process will die. If this occurs, a related error message can be found in the alert log file of the database.

When an online log is switched to the next online log, it is important to ensure that the information on the online log to be switched to (for example, redo log generated by some dirty chunks) is written to disk (checkpoint), because if the information of an online log file is overwritten, it relies on these redo The data block for the recovery of the information has not been written to disk (checkpoint), and when the system is down, Oracle will not be able to recover the instance.

If a large number of log file switch (checkpoint incomplete) Wait events occur in the system, either because the log files are too small or the log groups are too few, the solution is to increase the size of the log files or increase the number of log groups.

8. Library cache lock/ library cache pin

Such wait events occur when different users cause resource contention due to concurrent operation of the same database object, such as when a user is doing DDL operations on a table, the library cache lock Wait event occurs when other users want to access the table. It waits until the DDL operation is complete before proceeding.

The library cache pin, like the library cache lock, is the event that occurs with concurrent operations in the shared pool. In general, if Oracle is to recompile a number of objects such as PL/SQL or views, these objects need to be pin to the shared pool. If this object is unique to other users at this point, a wait for the library cache pin will be generated.

9. Direct Path read/ direct path Write

This type of wait event occurs when a session reads data blocks directly into the PGA rather than the SGA, and the data that is read is usually the private data of the session, so there is no need to put it into the SGA as a shared data, because it makes no sense. This data is usually from the temporary segment of the data, such as a session of the SQL sorting data, parallel execution of the data generated in the middle of the process, and the hash Join,merge Join produced by the sorting data, because this data only for the current session of SQL operations have meaning, So there's no need to put it in the SGA.
When the direct path read wait event occurs, it means that there is a large amount of temporary data generated on the disk, such as sorting, parallel execution, and so on. Or it means that there is not enough free space in the PGA.

In contrast to direct path read, direct path write is a session that writes some data directly from the PGA to the disk file without the SGA. This situation usually occurs when:
Sort using temporal table space (out of memory)
Direct loading of data (loading data using append)
Parallel DML operations.

Several common wait events from Oracle