Re: db file sequential/scattered read

This thread is making me nuts. Okay, a scattered read is indeed a mult-block read that causes the read result to be "scattered" in the buffer cache. There is not fragmentation problem with the buffer cache. It is only that buffers are accessed and maintained one at a time and they are used as they are available. As an instance gets "warm" the next available buffer, from the LRU list, will be unrelated to the previous buffer on that list. It is a matter of happenstance and usage that determines which actual buffer gets used. Since we access them by pointer values and use a hash table to find them, physical location within the buffer cache becomes unimportant.

Those multi block reads are possible when full table scans are done, because the physical blocks themselves are continguous on disk, right? You can run truss or some equivalent on your server, attached to a test shadow process running sql*plus or some such, and force a full table scan and observe the system level calls that do multi block reads. The number of blocks requested depends on the setting of db_file_multiblock_read_count and the limits of the operating system and file system. You won't always read the maximum value because we have to cross extent boundaries and so forth.

Multi block reads also happen when we do index fast-full-scans, which are events that read the entire index in physical block order - that is, we read the physical index segment directly and not in index value / logical order. Recall that indexes are ordered lists of block values, but are stored in non-continguous order. We use pointers to get to the next logically ordered set of values as we navigate from the root block through the branch blocks down to the leaf blocks. This applies to b*tree indexes of course. So, if we read an index as a range scan of key values, we are forced to read the blocks in an order that is not physically contiguous on disk. The term "sequential read" describes the sequentially logical aspect of this type of index access.

Sancho

Ivan Bajon wrote:

> Could someone please clarify whether full table scans cause waits for db
> file sequential read or scattered reads. I've seen claims on both.
>
> Steve Adams says:
> "Sequential reads occur mainly in connection with full table scans and fast
> full index scans." (Taken from Tips->Disk Configuration for Sequential Scans
> on Ixora)
>
> In "Description of Oracle7 Wait Events and Enqueues" (2.36 db file scattered
> read) Anjo Kolk says:
> "This event is normally an indicator that a Full Table Scan is happening on
> the object identified before."
>
> This leaves me confused. Which is it? Both?
>
> Regards,
> Ivan Bajon, ocp
  Received on Fri Jan 19 2001 - 09:33:42 CST