3 Replies Latest reply on Nov 27, 2009 10:59 AM by 707490

    About Db_file_scattered_read and Db_file_sequential_read


      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?

        • 1. Re: About Db_file_scattered_read and Db_file_sequential_read
          Timur Akhmadeev

          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
            Thanks, Timur.
            my question is now answered.
            • 3. Re: About Db_file_scattered_read and Db_file_sequential_read
              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.