Oracle Wait Events : ” db_file_sequential_reads “
4 min readThis 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. DB file sequential reads wait event comes under the User I/O wait class. In any busy system this is one of the most common wait event and normally appears in the top 5 wait events
" Db file sequential reads wait event occurs when a process has issued an I/O request to read one block (single block I/O) from a datafile into the buffer cache and is waiting for the operation to complete. These single block I/Os are usually a result of using indexes Or table data blocks accessed through an index. "
Individual Waits: Parameters: P1 = file# P2 = block# P3 = blocks file# This is the file# of the file that Oracle is trying to read from. From Oracle8 onwards it is the ABSOLUTE file number (AFN). block# This is the starting block number in the file from where Oracle starts reading the blocks. Typically only one block isbeing read. blocks This parameter specifies the number of blocks that Oracle is trying to read from the file# starting at block#. This is usually "1" but if P3 > 1 then this is a multiblock read. Multiblock "db file sequential read"s may be seen in earlier Oracle versions when reading from a SORT (TEMPORARY) segments.
Wait Time:
The IO is generally issued as a single IO request to the OS - the wait blocks until the IO request completes.
Note than an Oracle read request to the OS may be satisfied from an OS file system cache so the wait time may be very small.
Systemwide Waits:
IO is a normal activity so you are really interested in unnecessary or slow IO activity.
If the TIME spent waiting for IOs is significant then we can determine which segment/s Oracle has to go to disk for. See the "Tablespace IO", and "File IO" sections of the AWR (or STATSPACK) reports, along with ADDM and ASH output, to get information on which tablespaces / files are servicing the most IO requests, and to get an indication of the speed of the IO subsystem. If the TIME spent waiting for reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at V$FILESTAT.
See the "Top SQL by Disk Reads" sections of AWR reports for clues about any SQL causing high I/O.
It can also be useful to see which sessions are performing reads and trace them to see if the IOs are expected or not. This statement can be used to see which sessions may be worth tracing:
SELECT sid, total_waits, time_waited
FROM v$session_event
WHERE event='db file sequential read'
and total_waits>0
ORDER BY 3,2
;
One can also look at:
Statements with high DISK_READS in V$SQL - shown in the "Top SQL by Disk Reads" section of AWR.
Sessions with high "physical reads" in V$SESSTAT
When you issue any SQL Statement that performs a single block read operation against indexes, undo segments, tables (only when accessed by rowid), control files and data file headers, oracle server process waits for the OS to provide that block from the data file, and the wait event on which server process waits till the block is made available is termed as db file sequential read.
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.
The following points may help:
1) Check for SQL using unselective index scans
2) 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 thesystem.
3) 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-organising data can help to tackle this in severe situations.
4) See if partitioning can be used to reduce the amount of data you need to look at.
5) 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.
Hope it worked !! 🙂
