| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: good value for optimizer_index_cost_adj
Check out Jeff Holt's "Why are Oracle's Read Events 'Named Backwards'?"
(www.hotsos.com/dnloads/1.Holt2000.02.01-Backwards.pdf) for a
description of why these events are named the way they are.
Cary Millsap
Hotsos Enterprises, Ltd.
cary.millsap_at_hotsos.com
http://www.hotsos.com
-----Original Message-----
Sent: Wednesday, April 24, 2002 8:10 PM
To: Multiple recipients of list ORACLE-L
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...
> > "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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: cary.millsap_at_hotsos.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).Received on Wed Apr 24 2002 - 23:08:30 CDT
 |
 |