Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Does table monitoring handle all statistics for you?
Sorry - there are too many words with
context-specific meanings in my reply.
Assume you have a table with 1,000,000
rows in it, which has been analyzed to
generate statistics.
Over the next 48 hours, you delete 10,000
rows, and insert 65,000 rows. These are
counted into mon_mod$ (or whatever it
is called).
Another table has 100,000 rows, but
in the same time period you insert 100,
and update 10.
Eventually you call a procedure in the
dbms_stats package to analyze
'stale' tables. The code checks each table
listed in mon_mod$ - finds one with 75,000
changes compared to a base of 1,000,000
rows - this is a relatively large change,
so the table is re-analyzed; the next table
is found to have 110 changes against a
base of 100,000, a relatively small change,
not enough to affect the stats appreciably
so it is not reanalyzed.
The figures for the larger table are deleted from mon_mod$, the figures for the smaller table are not.
Possibly 'stale' is not the term used in the package, but I hope this clarifies what I meant by using the word.
-- Jonathan Lewis Host to The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases See http://www.jlcomp.demon.co.uk/book_rev.html Seminars on getting the best out of Oracle See http://www.jlcomp.demon.co.uk/seminar.html Brian Tkatch wrote in message <3b379ca3.255933921_at_news.alt.net>...Received on Mon Jun 25 2001 - 16:58:18 CDT
>On Mon, 25 Jun 2001 20:16:50 +0100, "Jonathan Lewis"
><jonathan_at_jlcomp.demon.co.uk> wrote:
>
>>
>>Monitoring keeps an approximate count
>>of inserts, updates and deletes. Every
>>three hours, SMON write the in-memory
>>counts to a table (with a name like mon_mod$)
>>
>>You still need to sort out statistics from time
>>to time,
>
>Why is that? What will fall out of order?
>
>> but the dbms_stats analyze procedures
>>now have the option to 'analyze only stale objects';
>
>Which makes it run a lot faster. That is nice.
>
>
>>Oracle then uses the values in mon_mod$ to
>>decide if the existing stats qualify as stale.
>>
>
>If is stale, then it hasn't been written. If it hasn't been written,
>then there was nothing to update. If there was nothing to update who
>cares if it is stale? Am I missing something?
>
>Brian