Re: Does table monitoring handle all statistics for you?

On Tue, 26 Jun 2001 21:32:37 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>>
>>I do not see Num_Rows in User_Tables. Nor do I see a view
>>USER_MODIFICATIONS.
>>
>
>user_table.num_rows - column NUM_ROWS of view USER_TABLES -
>

I am definitely missing something. Which view contains "num_rows". Is that the "high-water mark" I have heard of?

>My error, the view is USER_TAB_MODIFICATIONS.
Thanx. I see that one.

>>In other words, does MONITORING just raise a "red flag" when stats are
>>possibly out of date? And this is what is then called "stale"?
>
>
>Correct - but the only thing that looks at the red flag is
>a bit of code in the dbms_stats package.

So, even with MONITORING the stats will go out of date and ruin everything?

In CREATE TABLE the docs say:

Specify MONITORING if you want modification statistics to be collected on this table. These statistics are estimates of the number of rows affected by DML statements over a particular period of time. They are available for use by the optimizer or for analysis by the user.

That last statement "available for use by the optimizer" would indicate that the optimizer does use these values to some extent, and not just the DBMS_STATS package.

>
>>
>>So which is it better to run, DBMS_STATS or ANALYZE.
>>
>
>I happen to use ANALYZE. There is a note on Metalink about
>which values are not re-calculate by dbms_stats (this is version
>dependent of course), but I think it said that it didn't matter
>since they were values not used by the optimizer anyway,

Do you analyze all tables? The thing I like about DBMS_STATS is that it runs on many tables easily. With ANALYZE I'd have to write a procedure to do that. I like the package as well, because I can then use DBMS_JOBS to run it for me on schedule.

>Yes - for exotic and large cases especially. dbms_stats is
>supposed to be the new improved method. However,
>you might like to check in the shorter term (a) if it
>makes any difference and (b) if it costs more to run
>(try using both with sql_trace switched on).

Do you mean run DBMS_STATS on a single table run a query, drop stats, then run again with ANALYZE? I can try that. I'm afraid I not well-versed enough to know what query (or queries) give enough range to fully test the statistics. When using a simple query that uses an index there is a clear method for testing, but that is a simple test. Is that good enough for this test?

Brian Received on Tue Jun 26 2001 - 16:21:21 CDT