db file scattered read, db file sequential read & direct read wait events explanation

As Oracle DBA's we come across these wait events in most environments. We will discuss their definitions and see what we can do to reduce them.

db file scattered read

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 of buffer cache.A scattered read is usually a multi block 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'

db file sequential read

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.

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'.

direct path read

The session is waiting for a direct read to complete. A direct read is a physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory.

If asynchronous I/O is supported (and in use), then Oracle can submit I/O requests and continue processing. Oracle can then pick up the results of the I/O request later and wait on "direct path read" until the required I/O completes.

If asynchronous I/O is not being used, then the I/O requests block until completed but these do not show as waits at the time the I/O is issued. The session returns later to pick up the completed I/O data but can then show a wait on "direct path read" even though this wait will return immediately. Hence this wait event is very misleading because: • The total number of waits does not reflect the number of I/O requests • The total time spent in "direct path read" does not always reflect the true wait time.

 All three wait events are graphically represented below.                                                                                                    
 

For all the three wait events 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)

Solution:

Reducing Waits / Wait times:

Block reads are fairly inevitable so the aim should be to minimize un-necessary IO. This is best achieved by good application design and efficient execution plans. Changes to execution plans can yield orders of magnitude changes in performance. Tweaking at system level usually only achieves percentage gains. The following points may help:

• Check for SQL using unselective index scans

• A larger buffer cache can help - test this by actually increasing << Parameter: DB_CACHE_SIZE>> (or <<Parameter:DB_BLOCK_BUFFERS>> if still using that). Never increase the SGA size if it may induce additional paging or swapping on the system.
•  A less obvious issue which can affect the IO rates is how well data is clustered physically. Eg: Assume that you frequently fetch rows from a table where a column is between two values via an index scan. If there are 100 rows in each index block then the two extremes are:     
1. Each of the table rows is in a different physical block (100 blocks need to be read for each index block)
2. The table rows are all located in the few adjacent blocks (a handful of blocks need to be read for each index block)

Pre-sorting or re-organizing data can help to tackle this in severe situations.

• See if partitioning can be used to reduce the amount of data you need to look at.  

• It can help to place files which incur frequent index scans on disks which have are buffered by a cache of some form. eg: flash cache or hardware disk cache. For non-ASM based databases put such datafiles on a filesystem with an O/S file system cache. This can allow some of Oracles read requests to be satisfied from the cache rather than from a real disk IO.

Response Times will vary from system to system. As an example, the following could be considered an acceptable average:

10 ms for MultiBlock Synchronous Reads
  5 ms for SingleBlock Synchronous Reads
  3 ms for 'log file parallel write'

This is based on the premise that multi block IO may require more IO subsystem work than a single block IO and that, if recommendations are followed, redo logs are likely to be on the fastest disks with no other concurrent activity

IO Wait Outliers (Intermittent Short IO Delays)

Even though the average IO wait time may be well in the accepted range, it is possible that "hiccups" in performance may be due to a few IO wait outliers. In 12c the following views contain entries corresponding to I/Os that have taken a long time (more than 500 ms) In 11g, the information in the Wait Event Histogram sections of the AWR report may be useful in determining whether there are some IOs that are taking longer than average Log write waits over 500ms are also written to the LGWR trace file

For more information on outliers in 12C see:

Oracle Database Online Documentation 12c Release 1 (12.1)Database Administration

Database Reference

V$IO_OUTLIER

https://docs.oracle.com/database/121/REFRN/refrn30673.htm#REFRN30673

V$LGWRIO_OUTLIER

https://docs.oracle.com/database/121/REFRN/refrn30674.htm#REFRN30674

V$KERNEL_IO_OUTLIER  (only populated on Solaris)

https://docs.oracle.com/database/121/REFRN/refrn30673.htm#REFRN30673 

Identifying  IO Response Time

Oracle records the response time of IO operations as the "Elapsed Time" indicated in  specific wait events and statistics."Response time" and "elapsed time" are synonymous and interchangeable terms in this context.

Below is a list of some of the more popular wait events and their typical acceptable wait times (not an exhaustive list)

Wait Event	R/W	Synchronous /Asynchronous	Singleblock/ Multiblock	Elapsed Time (with 1000+ waits per hour)
control file parallel write	Write	Asynchronous	Multi	< 15ms
control file sequential read	Read	Synchronous	Single	< 20 ms
db file parallel read	Read	Asynchronous	Multi	< 20 ms
db file scattered read	Read	Synchronous	Multi	< 20 ms
db file sequential read	Read	Synchronous	Single	< 20 ms
direct path read	Read	Asynchronous	Multi	< 20 ms
direct path read temp	Read	Asynchronous	Multi	< 20 ms
direct path write	Write	Asynchronous	Multi	< 15 ms
direct path write temp	Write	Asynchronous	Multi	< 15 ms
log file parallel write	Write	Asynchronous	Multi	< 15 ms
Exadata Related
cell smart table scan	Read	Asynchronous	Multi	< 1 ms
cell single block physical read	Read	Synchronous	Single	< 1 ms
cell multiblock physical read	Read	Synchronous	Multi	< 6 ms

 references: asktom, oracle documentation.