Re: db file sequential/scattered read

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 - 09:52:26 CST