Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Operations that perform multiblock I/O and cluster factor

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

From: Alberto Dell'Era <>
Date: Tue, 11 Dec 2007 01:21:24 +0100
Message-ID: <>

On Dec 10, 2007 9:11 PM, Riku Räsänen <> 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 on Linux.

Interesting, "db file parallel read" ... Maybe you saw a variation of what Jonathan describes here: "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"
Received on Mon Dec 10 2007 - 18:21:24 CST

Original text of this message