Resolving common Oracle Wait Events using the Wait Interface

Wait Event :- db file sequential reads

This event signifies that the user process is reading a buffer into the SGA Buffer cache and is waiting for a physical I/O call to return.

A sequential read is a single-block read into one SGA buffer

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as ‘db file sequential read’.

Check the following V$SESSION_WAIT parameter columns:

  • P1 – The absolute file number
  • P2 – The block being read
  • P3 – The number of blocks (should be 1)

Interpreting Wait Event “file#” and “block#” parameters [ID 181306.1]

Possible Causes

  • Use of an unselective index
  • Fragmented Indexes
  • High I/O on a particular disk or mount point
  • Bad application design
  • Index reads performance can be affected by  slow I/O subsystem and/or poor database  files layout, which result in a higher average  wait time

Actions

  1. Check indexes on the table to ensure that the right index is being used
  2. Check the column order of the index with the WHERE clause of the Top SQL statements
  3. Rebuild indexes with a high clustering factor
  4. Use partitioning to reduce the amount of blocks being visited
  5. Make sure optimizer statistics are up to date
  6. Relocate ‘hot’ datafiles
  7. Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool
  8. Inspect the execution plans of the SQL statements that access data through indexes
  9. Is it appropriate for the SQL statements to access data through index lookups?
  10. Is the application an online transaction  processing (OLTP) or decision support system (DSS)?
  11. Would full table scans be more efficient?
  12. Do the statements use the right driving  table?
  13. The optimization goal is to minimize  both the number of logical and physical I/Os.

Remarks

The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from disk.

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

If the DBA_INDEXES.CLUSTERING_FACTOR of the index approaches the number of blocks in the table, then most of the rows in the table are ordered. This is desirable.

However, if the clustering factor approaches the number of rows in the table, it means the rows in the table are randomly ordered and thus it requires more I/Os to complete the operation. You can improve the index’s clustering factor by rebuilding the table so that rows are ordered according to the index key and rebuilding the index thereafter.

The OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING initialization parameters can influence the optimizer to favour the nested loops operation and choose an index access path over a full table scan.

Tuning I/O related waits Note# 223117.1

db file sequential read Reference Note# 34559.1

Wait Event:- db file scattered reads

Multiblock read into many discontinuous SGA buffers

This 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 db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations.

A scattered read is usually a multiblock read.It can occur for a fast full scan (of an index) in addition to a full table scan.

The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called ‘db file scattered read’. multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for ‘db file scattered read’.

Check the following V$SESSION_WAIT parameter columns:

  • P1 – The absolute file number
  • P2 – The block being read
  • P3 – The number of blocks (should be greater than 1)

Possible Causes

  • The Oracle session has requested and is waiting for multiple contiguous database blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT) to be read into the SGA from disk.
  • Full Table scans
  • Fast Full Index Scans

Actions

Optimize 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

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

Should the statement access the data by a full table scan or index FFS?
Would an index range or unique scan  be more efficient?

Does the query use the right driving table?

Are the SQL predicates appropriate for hash or merge join?

If full scans are appropriate, can parallel query improve the response time?

The objective is to reduce the demands for both the logical and physical I/Os, and this is best achieved through SQL and application tuning.

Make sure all statistics are representative of the actual data.

Check the LAST_ANALYZED date

Remarks

If an application that has been running fine for a while suddenly clocks a lot of time on the db file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes has been dropped or become unusable.

db file scattered read Reference Note# 34558.1

Wait Event:-Direct Path Reads

single or multiblock read into the PGA, bypassing the SGA

Possible Causes

  • 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 tsegments
  • 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.

Make sure the optimizer selects the right driving table. Check to see if the composite index’s columns can be rearranged to match the ORDER BY clause to avoid sort entirely.

Also, consider automating the SQL work areas using PGA_AGGREGATE_TARGET in Oracle9i Database.

Query V$SESSTAT> to identify sessions with high “physical reads direct”

Remarks

Default size of HASH_AREA_SIZE  is twice that of SORT_AREA_SIZE

Larger HASH_AREA_SIZE will influence optimizer to go for hash joins instead of nested loops

Hidden parameter DB_FILE_DIRECT_IO_COUNT can impact the direct path read performance.It sets the maximum I/O buffer size of direct read and write operations. Default is 1M in 9i

How to identify resource intensive SQL statements?

How to Identify Resource Intensive SQL to Tune [ID 232443.1]

Wait Event:-log file parallel write

Possible Causes

LGWR waits while writing contents of the redo log buffer cache to the online log files
on disk I/O wait on sub system holding the online  redo log files

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

Remarks

Reference Note# 34583.1

log file sync

Possible Causes

Oracle foreground processes are waiting for a COMMIT or ROLLBACK to complete

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

Remarks

Reference Note# 34592.1

High Waits on log file sync Note# 125269.1

Tuning the Redolog Buffer Cache and Resolving Redo Latch Contention

Note# 147471.1

Wait Event Docs

More Reference

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s