My 1/2 cents on this...
Another way of thinking of scattered -vs- sequential
reads is that a scattered read is used when you know
up front that you want to read several blocks and know
which blocks they are. You can then use a vectored
read to do this or farm them all out in parallel using
Hi,
Oracle 817/Solaris 8.
Users are doing select joining using the PKs of 2
partitionned tables. Partitionned key and the primary
key are the same.
The access plan is a nested loop with a full table
scan on the first table which hold 700 000 rows.
The block size is 16K, I assume that's why
Based on documentation, OPTIMIZER_INDEX_COST_ADJ seems to provide additional
comparison information for the CBO in terms of the relative cost of
different types of I/O. To make a long story short, I believe that guidance
can come from examining timing statistics from the wait-events db file
Best place to look is probably Tim Gorman's paper
titled something like 'The search for intelligent life'.
To be found on www.evdbt.com
His argument, which I think is very sound, is that
the most correct value for the parameter is
the relative cost of a single block read compared
to a multi
It's interesting that you should have heard that.
My first interpretation of the optimizer_index_cost_adj
was that it was an estimate of the table logical I/O that
would become physical I/O (and ignore the fact that
this was allowed to go above 100%) - which brings
it into line, somewhat, with
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
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
Well, Tim did say he was making a long story short.
scattered reads = multiblock reads which are typically
associated with tablescans, but can be index fast full
scans.
sequential reads = single block reads which are typically
associated with index block reads, table blocks
I've got it on my list of things I'd like to do (i.e. understand
DBMS_STATS.GATHER_SYSTEM_STATS), but I don't see any time to do it anytime
soon. :-(
It makes sense that the use of this procedure should obsolesce both
OPTIMIZER_INDEX_ parameters...
- Original Message -
To: Multiple
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.
[EMAIL PROTECTED]
http://www.hotsos.com
-Original
10 matches
Mail list logo
