Notes in-line:
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
news:3jhd20dahoombts7sm9p03hahkl0agkieo_at_4ax.com...
> One more remark: According to the statspack reports, time is being
> consumed here by "db file sequencial read". This phenomenal knowledge
> does not buy me anything, though.
>
> What is more interesting: Since system statistics are being collected,
> the corresponding view SYS.AUX_STATS$ reports that a sinble block
> access takes almost 3x as long as a MBRC, which surprises me a lot (8
> msec vs. 3 msec). Note: as I understand, Oracle can't tell anything
> about real physical access; data often comes from the storage cache,
> which explains the low values.
>
This is one of the flaws in using system level statistics to
calibrate the CBO. The 'standard' method for choosing
a representative value for optimizer_index_cost_adj is
to take a couple of snapshots of the db file xxx waits
and compare the average times for sequential and scattered
reads. The trouble is that one rogue process doing lots
of relatively small tablescans can manage to keep data
in the O/S buffer even when it keeps dropping out of the
Oracle buffer. This can lead to very low times for the
'system-wide' scattered read time, when compared to
the sequential read time. Unfortunately, system_statistics
uses exactly this system-wide technique.
To counter this problem, you could use snapshots of
v$session_event, and if there are a couple of extreme
sessions distorting the scattered read times, simply
ignore them in the calculation.
Another option is to go right outside Oracle, and
do some tests on how fast your hardware can
do different sized I/Os, and use those numbers to
set optimizer_index_cost_adj, or the sread_time and
mread_time settings of system statistics.
> The Performance Tunig Pack of the OEM shows also very clear that "db
> file sequencial read" is always a predominant wait event (in terms of
> waited time), no matter what query is running, compared to FTS. I
> still feel challenged to better understand why.
>
> Thanks again
> Rick Denoire
>
Received on Sun Feb 08 2004 - 17:58:13 CST
