Notes in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
"Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message
news:9lL9c.128998$Wa.127054_at_news-server.bigpond.net.au...
>
> In conclusion, the only thing to have changed between 8i and 10G has been
> the statistics update interval.
>
>
> My best guess is that the statistics are now updated between five (5) and
> ten (10) minutes after the last DML. It is a pity that they are not more
> accurate.
>
Good guess -
Your post prompted me to take a look at what had changed.
My method is simple:
alter system set sql_trace true scope = both
shutdown
startup
Then exercise the database.
The change from 8 to 9 was to reduce the refresh time
from about 3 hours to about 15 minutes. smon kicks in every
5 minutes, and every third cycle the update was one of the tasks.
9i also introduce a specific call in dbms_stats to 'flush stats' on
demand to write the in-memory record to mon_mods$; and this
call is built in when you do an "analyze stale". (From memory,
I think this also flushes the col_usage$ statistics).
9 to 10 is significantly different, as the task has been handed
off to a standard dbms_job, which is invoked every 10 minutes.
So the updates on my test system appear in the j000 trace file,
and the job definition shows a call to dbms_stats to gather
system stats with the flush method.
Your "5 to 10" minutes is probably appearing as a side-effect
of the default job queue interval being 5 minutes.
Received on Tue Mar 30 2004 - 02:13:00 CST
