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:
- Each of the table rows is in a different physical
block (100 blocks need to be read for each index block)
- 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'
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:
For more information on outliers in 12C see:
Oracle Database Online
Documentation 12c Release 1 (12.1)Database Administration
Database Reference
V$IO_OUTLIER
V$LGWRIO_OUTLIER
V$KERNEL_IO_OUTLIER (only populated on Solaris)
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
|
