Answers in-line
(or see OTN for a recent article (of mine))
http://otn.oracle.com/pub/articles/lewis_cbo.html
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
news:9q7bd0l1e6323lqfqnapkioge6hoaoaf6e_at_4ax.com...
> Hello
>
> After reading about the subject, some points stay unclear to me.
>
> If I say:
> exec dbms_stats.gather_system_stats('Start');
>
> then a particular table, namely sys.aux_stats$ is populated with
> values measured while some DB activity took place.
> Should I flush the shared pool in order to force recalculation of
> execution plans?
>
Yes - but you also need to have done a
exec dbms_stats.gather_system_stats('Stop');
at some point.
> Are these values considered by the CBO readily or is another command
> needed?
>
Unless you've been hacking hidden init.ora (spfile) parameters,
they kick in automatically.
> If I wanted to store these values in a different table, how can I
> create this table? (Since it must be suitable for this purpose).
>
dbms_stats.create_stat_table(. etc..)
dbms_stats.export_system_stats( etc. )
> Which might be the reason why after starting system stats gathering in
> two different servers (both V. 9.2.0.4, one Sun, the other Linux),
> only in one of them new values appeared on the sys.aux_stats$ table,
> while on the other the table remained empty? How long should I wait in
> order to get these values from the table?
Perhaps someone did a 'Stop' on one of them.
exec dbms_stats.gather_system_stats('Start');
>
> And how is it possible that the MBRC value is reported as -1?
>
It means that there were no tablescans in the interval
bounded by the Start and Stop.
> Is there any source for adequate or typical values that I can SET
> directly, trying to improve the way the CBO calculates costs?
You could stop the database and test the hardware I/O
response time using the program at www.iozone.com
But you need a sensible figure for the MBRC - which
Oracle gets by looking at v$filestat over the snapshot
period - so you could copy the idea.
> (You could report the values that you find in your server instead).
>
I would use the CPU figure - as I suspect it's based on an
internal calibration operation, so if it's wrong it's going to
be wrong self-consistently.
> How has been your experience in deliberately changing the values in
> aux_stats$ differently from the ones gathered by Oracle?
>
If you gather stats at an unlucky point in time, when
a rogue processing is causing bizarre tablescan response
then the MBRC and mreadtim values can be silly, and
it makes sense to adjust them
> How can I garantee that these values survive a server restart? Does
> the aux_stats$ table exist only in memory?
>
It's a real table - they survive
> When will these gathered sysstat values will be actually considered:
> After stopping the gathering using the same command as above but with
> the ('Stop') argument? Or only BETWEEN the Start and Stop commands?
>
Only after the STOP command (and a flush of shared_pool to
re-optimize current cursors).
> Why is it necessary to gather system stats according to different
> activity levels of the server if no matter how much activity is taking
> place any single block access will take the same time? I mean that the
> hardware won't get slower, any multiblock read operation will last
> exactly the same time at any point in time... or not?
>
Depends how variable your workload is - for example:
A real, physical, tablescan may be the quickest way to get
the answer for (say) a 1 in 400 row query when no-one else
is using the system. If 25 other people are using the system
and doing the SAME thing, then they may all get the best
response time if they all did an indexed access path.
(Oracle is moving from Adam Smith to John Nash)
In general, though, I suspect that a neutral calibration of
i/o times, combined with a reasonable MBRC will be the
best for most systems.
> Thanks in advance
> Bye
> Rick Denoire
>
>
Received on Sun Jun 20 2004 - 12:00:08 CDT
