Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Does table monitoring handle all statistics for you?

Re: Does table monitoring handle all statistics for you?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 25 Jun 2001 22:58:18 +0100
Message-ID: <993506454.24077.0.nnrp-14.9e984b29@news.demon.co.uk>

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>...

>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
Received on Mon Jun 25 2001 - 16:58:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US