What is Wait Event

Oracle Wait Events are conditions where a session is waiting for something to happen. A wait event can be caused by many things, from slow read/write speeds on the disk, to locking situations caused by the architecture, to various kinds of Oracle contentions. Waits are either system-level or session-level.

It indicates that the user process is trying to read blocks in buffer cache but block is not available in buffer cache and it is waiting till the end of the physical IO call to return block from physical disk to buffer cache.

db file sequential read wait time is most likely an application issue.

Possible Causes :

It may be happen due to Use of an unselective index

It may be happen due to Fragmented Indexes

It may be happen due to High I/O on a particular disk or mount point

It may be happen due to Bad application design

It may be happen due to Index reads performances by slow I/O subsystem and/or poor database files layout, which result in a higher average wait time

Actions :

We should Check indexes on the table to ensure that the right index is being used

We should Check the column order of the index with the WHERE clause of the Top SQL statements

We should Rebuild indexes with a high clustering factor

We should Use partitioning to reduce the amount of blocks being visited

We should Make sure optimizer statistics are up to date

Relocate ‘hot’ datafiles

We should Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool

It occurs when a query needs to read data from multiple non-contiguous blocks in a database file.

This can happen when data blocks are not stored sequentially on disk or when a query requires data from multiple blocks that are not physically adjacent to each other.

A wait event represents a wait for a physical read of multiple Oracle blocks from the disk.

Possible Causes

Full table scan/Index scan: When a query performs a full table scan, it reads all the blocks of a table, including non-contiguous blocks. This can result in scattered reads. If the table is not stored sequentially on disk, or if there are fragmented free blocks in the table that cause the table blocks to be scattered.

Disk fragmentation: Disk fragmentation can occur over time as files are created, modified, and deleted on the file system. If the database files are fragmented on disk, it can result in scattered reads when reading data from the database files.

High concurrent access: If multiple queries or operations are accessing the same database files concurrently, it can result in scattered reads. This can happen when multiple sessions are performing I/O operations on different parts of the same database file simultaneously, causing the disk head to jump around and read non-contiguous blocks.

Small I/O requests: If the size of the I/O requests issued by the database is small, it can result in scattered reads. For example, if the database is configured to perform small I/O requests, such as 4KB or 8KB, and the data blocks are larger than the I/O request size, it can result in scattered reads as multiple I/O requests are needed to read a single data block.

Disk contention: If the disk subsystem is experiencing high contention due to other I/O-intensive processes or hardware issues, it can result in scattered reads. Disk contention can cause delays in reading data blocks, leading to scattered reads.

Actions :

Optimise multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT

Partition pruning to reduce number of blocks visited

Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool

Optimise the SQL statement that initiated most of the waits. The goal is to minimise the number of physical and logical reads.

"Buffer busy waits" occurs when multiple processes attempt to access the same buffer block in the buffer cache, but only one process can access the buffer at a time.

We can say that this wait event occurs when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked and the user process is waiting for blocks.

Possible Causes

Buffer busy waits can occur due to various reasons, such as high concurrency of transactions, poor SQL statement design, small buffer cache size, or slow I/O performance.

How can we reduce it? To reduce the occurrence of buffer busy waits, it is recommended to properly size the buffer cache and tune the SQL statements to minimise the number of buffer requests. Additionally, using faster I/O subsystems and spreading data across multiple disks can also help alleviate buffer busy wait issues.

"Free buffer waits" occurs when a user process requests a free buffer to read or write data, but no free buffer is immediately available in the buffer cache.

Possible Cause

This can happen when the buffer cache is too small or when there are too many concurrent requests for buffer space.

To reduce the occurrence of free buffer waits, it is recommended to properly size the buffer cache based on the workload and system resources, and to optimise SQL queries to minimise the number of buffer requests. Additionally, increasing the size of the buffer cache or adding more memory to the system can also help alleviate free buffer wait issues.

This wait event typically occurs when the database is writing redo log entries in parallel to multiple redo log files.

This wait event can happen during high levels of transactional activity or when the database is experiencing a high rate of redo log generation.

This wait event indicates the LGWR process is waiting for blocks to be written to all online redo log members in one group.

Possible Cause

Slow I/O subsystem

Small redo log files

High transactional activity

Slow I/O subsystem for redo log files

Inefficient log writer (LGWR) process

Actions :

Reduce the amount of redo being generated

Do not leave tablespaces in hot backup mode for longer than necessary

Do not use RAID 5 for redo log files

Use faster disks for redo log files

Ensure that the disks holding the archived redo log files and the online redo log files are separate so as to avoid contention Consider using NOLOGGING or UNRECOVERABLE options in SQL statements

The "log file sync" occurs when a session is waiting for a redo log write to complete before proceeding.

When a user session issues a commit or rollback , the session will signal LGWR to write the log buffer to the redo log file.

LGWR starts writing and when finished the writing , it will confirm to the user session.

If this wait event is high that means LGWR is taking time to write the data in the redo log file and due to this user session is waiting for LGWR confirmation.

This is also called "commit latency".

Possible Cause

High redo generation rate

Slow I/O subsystem

Small redo log file size

High commit rate

Misconfigured log buffer or redo log settings

Redo log file multiplexing

Actions :

Tune LGWR to get good throughput to disk eg: Do not put redo logs on RAID5

Reduce overall number of commits by batching transactions so that there are fewer distinct COMMIT operations

A direct path read is a type of I/O operation that is used when reading data from a datafile into the buffer cache.

Direct path reads bypass the buffer cache and read data directly into the PGA (Program Global Area) of the process.

This is useful for large full-table scans, as it reduces the overhead of writing data to and reading data from the buffer cache.

When a session performs a direct path read operation, it may have to wait for the I/O operation to complete before it can continue processing. If the wait time for the I/O operation is long, the session may appear to be hanging or not responding. In this case, the session will be in the Direct Path Reads wait event.

Possible Cause

These waits are associated with direct read operations which read data directly into the sessions PGA bypassing the SGA

The “direct path read” and “direct path write” wait events are related to operations that are performed in PGA like sorting, group by operation, hash join

In DSS type systems, or during heavy batch periods, waits on “direct path read” are quite normal However, for an OLTP system these waits are significant

These wait events can occur during sorting operations which is not surprising as direct path reads and writes usually occur in connection with temporary segments

SQL statements with functions that require sorts, such as ORDER BY, GROUP BY, UNION, DISTINCT, and ROLLUP, write sort runs to the temporary tablespace when the input size is larger than the work area in the PGA

Actions :

Ensure the OS asynchronous IO is configured correctly.

Check for IO heavy sessions / SQL and see if the amount of IO can be reduced.

Ensure no disks are IO bound.

Set your PGA_AGGREGATE_TARGET to appropriate value (if the parameter WORKAREA_SIZE_POLICY = AUTO) Or set *_area_size manually (like sort_area_size and then you have to set WORKAREA_SIZE_POLICY = MANUAL

Whenever possible use UNION ALL instead of UNION, and where applicable use HASH JOIN instead of SORT MERGE and NESTED LOOPS instead of HASH JOIN.

Direct path write is a technique used by Oracle to write large amounts of data to disk quickly, without using the buffer cache. Instead, the data is written directly to disk in multiblock I/O operations. This technique is commonly used in operations like sorting, creating indexes, and loading data into a table.

When a session performs a direct path write, it must wait for the write operation to complete before continuing. If the write operation is slow or there are other sessions competing for I/O resources, the session may spend a significant amount of time waiting for the direct path write to complete, leading to the "direct path write" wait event.