Re: good value for optimizer_index_cost_adj

The naming of these two wait-events is unfortunate in that they are counter-intuitive. You would normally think that the phrase "sequential" implied one thing and the phrase "scattered" implied the opposite. They do, but not the way one would guess...

"Db file scattered read" is the wait-event for sequential, multi-block read I/O. This type of I/O is usually associated with FULL table scans and fast full index scans. "Db file sequential read" is the wait-event for random, single-block read I/O, usually associated with reads by data-block address (DBA) and/or ROWID. This includes reads on "branch" and "leaf" blocks in an index, as well as ROWID access to data blocks in tables and clusters. This wait-event is also associated with reads where ROWID is specified in the SQL statement, as well as reads of segment headers and some stuff like that. I'm sure that there are more scenarios that I've omitted...

"Tuning 101" isn't incorrect and neither is my reply. "Fast full index scans" are sequential, multi-block reads against the entire index segment, just one of four index scan method alternatives (i.e. unique, range, full, and fast-full). And, of course, random single-block I/O is performed on tables/clusters all the time...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 24, 2002 4:23 PM

> > "db file scattered read" (associated with FULL
> > table scans) and "db file sequential
> > read" (associated with indexed scans)
>
> But see, Tuning 101, p. 35, where a "db file sequential read" is
> investigated and the waits are found to be reads from a *table*.
>
> And other examples elsewhere, where "db file scattered read" is found to
be
> associated with reads from an index.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Greg Moore
> INET: sqlgreg_at_pacbell.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).