Re: Operations that perform multiblock I/O and cluster factor

On Dec 10, 2007 9:11 PM, Riku Räsänen <riku.rasanen_at_kantamestarit.fi> wrote:
> Actually, I have seen "db file scattered read" on INDEX RANGE SCAN, and I
> had a 10046 trace file for that, but was unable to find it (I must have
> deleted it).
>
> This was a very degenerate case, where the entire table was accessed
> through single column index (hinted). The trace file showed around 70% "db
> file scattered read", 20% "db file sequential read" and around 5% "db file
> parallel read". This was Oracle 9.2.0.6 on Linux.

Interesting, "db file parallel read" ... Maybe you saw a variation of what Jonathan describes here:

http://jonathanlewis.wordpress.com/2006/12/15/index-operations/ "The index full scan typically uses db file sequential reads to get data from disk, although newer versions of Oracle can do db file parallel reads which are read requests for multiple Oracle blocks that are not adjacent blocks in the Oracle data files."

It might (I'm guessing) make sense that when Oracle scans the index, instead of immediately accessing the table blocks, collects, say, the data block addresses of N blocks to be read from the table, than performs a "db file parallel reads" of N blocks, but if they happen to be adjacent, performs an N-block "db file scattered read" instead.
For an index with a very low clustering factor, i.e. on a table where the rows are ordered on disk by index key, the "db file scattered read" would be the norm and not the exception, as you observed.

Caveat emptor, that's a shot in the dark - never seen it myself.

If you could reproduce the test case, I would very much appreciate it :)

-- 
Alberto Dell'Era
"the more you know, the faster you go"
--
http://www.freelists.org/webpage/oracle-l