This discussion is archived
3 Replies Latest reply: Nov 27, 2009 2:59 AM by 707490 RSS

About Db_file_scattered_read and Db_file_sequential_read

ice_cold_aswin Newbie
Currently Being Moderated
Hi,

After going through this, I am wondering why Oracle would scatter data to different buffers for a full-table scan and distribute it across contiguos buffers in memory for index scan.

Can some one please help me understand this in more detail?

Thanks,
Aswin.
  • 1. Re: About Db_file_scattered_read and Db_file_sequential_read
    680087 Pro
    Currently Being Moderated
    Hi,

    you are probably confusing the meaning of "contiguous" for sequential read. That doesn't mean that 2 blocks being read (for ex, during IRS) with sequential read would reside in contiguous blocks in the buffer cache. Can you just imagine that? I can't. Contiguous in this case means "we are reading N consecutive bytes from the disk to the memory region", and that memory buffer is contiguous piece of memory - from address A1 to A2.
    As an opposite, while reading multiple blocks from disk to the memory, they'd go to the several memory buffers - which means "reading N*MBRC consecutive bytes from disk to N different memory regions, which are not adjacent - {noformat}[A1, A2], [A3, A4]{noformat}, etc".
  • 2. Re: About Db_file_scattered_read and Db_file_sequential_read
    ice_cold_aswin Newbie
    Currently Being Moderated
    Thanks, Timur.
    my question is now answered.
  • 3. Re: About Db_file_scattered_read and Db_file_sequential_read
    707490 Newbie
    Currently Being Moderated
    db file sequential read:

    A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

    db file scattered read:

    Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points