Results 1 to 4 of 4

Thread: db file scattered read

  1. #1
    Join Date
    Mar 2003

    db file scattered read

    What is the "db file scattered read" a symptom of?
    1. index read
    2. Dyslexia
    3. Failed disk drive
    4. Full table scan

    If you can answer this question click
    Click Here to Take a Quiz

    And get some free stuff!

  2. #2
    Join Date
    Apr 2007

    db file scattered read generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list.

  3. #3
    Join Date
    Aug 2011
    The db file scattered read wait event means that Oracle is waiting while doing a multiblock IO to complete. This is the case during full table scans or fast full index scans. Oracle can read multiple blocks (up to the initialization parameter db_file_multiblock_read_count) and scatters them into buffers in the buffer cache.

    The parameter db_file_multiblock_read_count depends on the platform and the release of Oracle you are running. For exaple for Unix and Linux Systems a good value is 8.

    Like all wait events the columns P1, P2, P3 give us the information needed to diagnose the waiting.
    P1 = file#
    P2 = block#
    P3 = blocks

    file# This is the file# of the file that Oracle is trying to read
    from. In Oracle8/9 it is the ABSOLUTE file number.

    block# This is the starting block number in the file from where
    Oracle starts reading the blocks.

    To find the object that Oracle doing the I/O use one of
    the two following ways

    SELECT owner, segment_type, segment_name, partition_name,
    FROM dba_extents
    WHERE :P2 BETWEEN block_id AND (block_id + blocks - 1)
    AND file_id = :P1;

    Or even better
    SELECT a.SID, c.obj, c.FILE#, c.dbablk
    FROM v$session_wait a, x$bh c
    WHERE a.p1 = c.FILE#(+)
    AND a.p2 = c.dbablk(+)
    AND a.event = 'db file scattered read'
    AND a.SID = :sid_waiting;

    blocks This parameter specifies the number of blocks that Oracle is
    trying to read from the file# starting at block#.

    Wait Time:
    The wait blocks until all blocks in the IO request have been read.
    Some advise
    If you see this wait event then general you are NOT in a good position. It is very usual some databases to have wait events and doing IO for a full table or index scan, but can be avoided

    START considering to create an index instead of full table scan, if this is not posible, try to imporove the full table or index scan.

    How to improve performance for db file scattered read
    The steps are, starting from that might come with better results:

    1. Move the table to reset table High Water Mark
    2. Use Parallel processing
    3. Use tablespace with bigger block size
    4. If the table has small size change it to IOT
    5. Distribute the table or index in different filesystems to reduce contention for I/O (Disk I/O)

  4. #4
    Join Date
    Nov 2002
    New Jersey, USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts