db file scattered read" Reference Note

db file scattered read" Reference Note

Definition:

• Versions:7.0 - 12.1 Documentation: 12.1 11.2 11.1 10.2 10.1
  
• This wait happens when a session is waiting for a multiblock IO to complete. This typically occurs during FULL TABLE SCANs or INDEX FAST FULL SCANs. Oracle reads up to DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and scatters them into buffers in the buffer cache. How this is done depends on the platform and the release of Oracle you are running. It may also vary depending on the device type being read from and the number of blocks requested.

Individual Waits:

 Parameters: 
    P1 = file#
    P2 = block#
    P3 = blocks

 file#   This is the file# of the file that Oracle is trying to read 
                from. In 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. 

                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#. 
                The upper limit is DB_FILE_MULTIBLOCK_READ_COUNT, which is self
                tuned from Oracle 10.2 onwards.

 Wait Time: 
    The wait blocks until all blocks in the IO request have been read.
    Note than an Oracle read request to the OS may be satisfied from an
    OS file system cache and so may not incur a disk IO.

Systemwide Waits:

IO is a normal activity so you are really interested in unnecessary or slow IO activity.If the TIME spent waiting for multiblock reads is significant then determine which segments/objects Oracle is performing the reads against. See the "Tablespace IO", and "File IO" sections of the AWR (or STATSPACK) reports, along with ADDM and ASH output. These should show which tablespaces / files are servicing the most IO requests, and give an indication of the speed of the IO subsystem. Tablespaces / files involved in "db file scattered read" waits will have "Av Blks/Rd" > 1.
The files where the reads are occuring can also be found by looking at V$FILESTAT where BLKS_READ / READS > 1 . (A ratio greater than 1 indicates there are some multiblock reads occuring).
See the "Top SQL by Disk Reads" sections of AWR reports for clues about any SQL causing high I/O. If statistics gathering is enabled then V$SQL_PLAN can also give clues about SQL statements using FULL scans.
It can sometimes be useful to see which sessions are performing scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions are incurring waits:

  SELECT sid, total_waits, time_waited
    FROM v$session_event
   WHERE event='db file scattered 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 "table scans blocks gotten" in V$SESSTAT

Reducing Waits / Wait times:

Ideally you do not want to repeatedly perform full scans in online portions of an application when there is a faster more selective way to get at the data - in this case query tuning should be used to optimize the online SQL.
In non online portions of an application table scanning is much more likely to be required. The main steps for tuning IO waits are described in Note:223117.1. Some specific points for "db file scattered read" waits include:

• Tuning of SQL usually gives the largest gains
• Consider partitioning to reduce the amount of data you need to scan
• Are affected objects sparsely populated? If so consider shrinking them
• Consider Advanced Compression to reduce the number of blocks that need to be visited
• Careful use of multiple buffer pools and the CACHE option might help.

Troubleshooting

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

Document:1475785.1 Resolving Issues Where Application Queries are Waiting Too Often for 'db file scattered read' Operations
Document:1476092.1 Resolving Issues Where 'db file scattered read' Waits are Seen Due to IO Performance Problems
Document:223117.1 Troubleshooting I/O Related Waits
Document:1275596.1 How to Tell if the I/O of the Database is Slow

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

Note---> This informationmation taken from oracle metalink. all copy rights oracle only.