SEO

I'm bottlenecked on db file sequential read. What wait should I expect
to see if the plan shows Index Fast Full Scan? Is it db file
sequential read or scattered read since the Fast Full Scan is treated
like a tablescan and uses multiblock reads?

EscVector wrote:
> I'm bottlenecked on db file sequential read. What wait should I expect
> to see if the plan shows Index Fast Full Scan? Is it db file
> sequential read or scattered read since the Fast Full Scan is treated
> like a tablescan and uses multiblock reads?

Paraphrased from various sources:
Oracle issues a scattered read to read data into multiple discontinuous
memory locations. A scattered read is usually a multiblock read. It
can occur for a fast full scan of an index in addition to the more
common full table scan. Db file scattered read wait events indicate
that a full scan is occurring. When performing a full scan into the
buffer cache, the blocks read are read into memory locations that are
not physically adjacent to each other.

A sequential read is a single-block read, where a user process is
reading a buffer into the SGA buffer cache and waiting for a physical
I/O. Single block I/Os are usually the result of index scans. Rarely,
full table scans could get truncated to a single block call due to
extent boundaries, or buffers already present in the buffer cache.
------------------------------

An index fast full scan should appear as db file scattered read. The
second paragraph above seems to indicate that full table scans can wait
on a db file sequential read event, if most of the required blocks are
already in the buffer cache, and no contiguous blocks are required. I
suspect that the same is true for index fast full scans.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

"EscVector" <Junk@webthere.com> wrote in message
news:1168531840.130757.22890@77g2000hsv.googlegrou ps.com...
> I'm bottlenecked on db file sequential read. What wait should I expect
> to see if the plan shows Index Fast Full Scan? Is it db file
> sequential read or scattered read since the Fast Full Scan is treated
> like a tablescan and uses multiblock reads?
>

Typically "db file scattered read"
see
http://jonathanlewis.wordpress.com/2...ex-operations/

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Jonathan Lewis wrote:
> "EscVector" <Junk@webthere.com> wrote in message
> news:1168531840.130757.22890@77g2000hsv.googlegrou ps.com...
> > I'm bottlenecked on db file sequential read. What wait should I expect
> > to see if the plan shows Index Fast Full Scan? Is it db file
> > sequential read or scattered read since the Fast Full Scan is treated
> > like a tablescan and uses multiblock reads?
> >
>
> Typically "db file scattered read"
> see
> http://jonathanlewis.wordpress.com/2...ex-operations/
>
> --
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Thank you for the replies and sanity check. I had expected db file
scattered reads, but not what I'm seeing. Need to look further.

EscVector wrote:
> Jonathan Lewis wrote:
> > "EscVector" <Junk@webthere.com> wrote in message
> > news:1168531840.130757.22890@77g2000hsv.googlegrou ps.com...
> > > I'm bottlenecked on db file sequential read. What wait should I expect
> > > to see if the plan shows Index Fast Full Scan? Is it db file
> > > sequential read or scattered read since the Fast Full Scan is treated
> > > like a tablescan and uses multiblock reads?
> > >
> >
> > Typically "db file scattered read"
> > see
> > http://jonathanlewis.wordpress.com/2...ex-operations/
> >
> > --
> > Regards
> >
> > Jonathan Lewis
> > http://jonathanlewis.wordpress.com
> >
> > Author: Cost Based Oracle: Fundamentals
> > http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> >
> > The Co-operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Thank you for the replies and sanity check. I had expected db file
> scattered reads, but not what I'm seeing. Need to look further.

It seems there may be 2 causes - either plan is wrong and actually you
don't do IFFS or most of index is in the buffer cache.

BTW it is interesting how far Table full scan and IFFS can go away from
scattered reads to sequential reads? Is there possible situation when
Full scan or IFFS is performed only using sequential reads?

Gints Plivna
http://www.gplivna.eu