db file sequential read" Reference Note

db file sequential read" Reference Note

Definition:

• Versions:7.0 - 12.1
  Documentation: 12.1 11.2 11.1 10.2 10.1
  
• This signifies a wait for an I/O read request to complete. This call differs from "db file scattered read" in that a sequential read reads data into contiguous memory (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA).A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3). This wait may also be seen for reads from datafile headers (P2=1 indicates a file header read) .

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 is
  being read.

                See Note:181306.1 to determine the tablespace, filename
         and object for this file#,block# pair.

        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

Reducing Waits / Wait times:

Block reads are fairly inevitable so the aim should be to minimise 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 <> (or <> 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-organising 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.

Troubleshooting

See the following documents for help troubleshooting issues relating to "db file sequential read" waits:

Document:1475825.1 Resolving Issues Where Application Queries are Waiting Too Frequently for 'db file sequential read' Operations
Document:1477209.1 Resolving Issues Where Application Queries are Waiting Too Long for 'db file sequential read' Operations Due to Underlying I/O Issues
Document:223117.1 Troubleshooting I/O Related Waits
Document:1275596.1 How to Tell if the I/O of the Database is Slow

=====================================================================