DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=BSMZoe0+rmcY79dl5rgAgrlp9PRzNSQxNF+rZbsRBkJV7ak8OTFGwOGwvXf7tKdAQAlWG9DlQZ6InLhQdp+FVQhfc5hQhoGgnSmScK8LG255ovBcvyrtCinhd23jBjqY5PamFefKawUftSQCn6YEZI2vhB7OOM5ReCS5vCpcaKA=
Message-ID: <4ef2fbf50712101621r1c98c50ft1a542af3ec39d105@mail.gmail.com>
Date: Tue, 11 Dec 2007 01:21:24 +0100
From: "Alberto Dell'Era" <alberto.dellera@gmail.com>
To: riku.rasanen@kantamestarit.fi
Subject: Re: Operations that perform multiblock I/O and cluster factor
Cc: Brandon.Allen@oneneck.com, oracle-l@freelists.org
In-Reply-To: <2509.62.142.244.186.1197317491.squirrel@webmail.ainaratkaisu.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
Content-Disposition: inline
References: <578806DDE66A3A45916740EB73C6982AB42BB5D391@M1EXCHANGE01.mmi.local>
	 <04DDF147ED3A0D42B48A48A18D574C450999D439@NT15.oneneck.corp>
	 <2509.62.142.244.186.1197317491.squirrel@webmail.ainaratkaisu.fi>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: alberto.dellera@gmail.com

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