Re: db file sequential/scattered read

Large index range scans done often enough effectively clear out the buffer cache, as these blocks are not treated like table-scan blocks ( they actually displace otherwise usefull data in the cache ).

Aside from that, given the two types of "scan" activity ( ie. sequentially reading a block at a time with no read-ahead, and sequentially reading with read-ahead ), the former is slower. If you have lots of the db file sequential read wait events, you may be using indexes where you shouldn't  or you could have disabled read-ahead ).

-Kevin

"Ivan Bajon" <bajon_at_no_spam_at_worldonline.dk> wrote in message news:6R476.13094$zH2.559179_at_news000.worldonline.dk...
> Kevin, I'm with you all the way until
>
> "In any case, the sequential variety is probably the one you'd want to
> minimize."
>
> Sticking to Oracle7 for the sake of simplicity. Why would I want to
 minimize
> sequential read waits when these indicate non-full index scans? I'd much
> rather minimize avoid full table scans, ie. scattered read waits.
>
> Regards,
> Ivan Bajon, ocp
>
>
>
> "Kevin Brand" <kevin.brandx_at_tel.gte.com> wrote in message
> news:93i0d7$i9f$1_at_news.gte.com...
> >
> > A few tests reveal the following:
> >
> > In Oracle7 v7.3.4:
> >
> > doing an index scan with V733_plans_enabled=false I get
> > waits for sequential read
> >
> > doing a table scan with db_file_multiblock_read_count > 1 I get
> > waits for scattered read
> >
> > doing a table scan with db_file_multiblock_read_count = 0 I get
> > waits for sequential read
> >
> > In Oracle8 v8.1.6
> >
> > basically the same except that an index scan produces scattered read
 waits.
> > I believe this is true because index scans in O8 actually use read-ahead
 by
> > default ( FFS ).
> >
> > So, based on the above, sequential and scattered waits both are
 indicitave
> > of table scans. It depends on whether or not the engine chooses ( based
 on
> > parameters ) to perform advanced reads ( read-ahead ). Depending on
 which
> > version of the server you run, these wait events may also indicate index
> > scans.
> >
> > In any case, the sequential variety is probably the one you'd want to
> > minimize.
> >
> > -Kevin
> >
> > "Ivan Bajon" <bajon_at_nospam@worldonline.dk> wrote in message
> > news:93h9gi$eof$1_at_news.inet.tele.dk...
> > > 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 Wed Jan 10 2001 - 16:29:02 CST