Re: Does table monitoring handle all statistics for you?

select table_name , num_rows from user_tables;

For nay table that has been analyzed then num_rows should be non-null, and be roughly the number of rows that was in the table when the table was last analyzed.

My theory about CBO statistics is that most tables can have very approximate statistics, a few will need a reasonable percentage estimate, and a few will have some columns that need histogram generation.

In general, therefore, I prefer some detailed control over what I analyze, how frequently, and how much.

In many cases, dbms_stats could easily be the most convenient way of analyzing everything with a small estimate, followed by a detailed list of special cases. Requirements vary with the system, there is no generic sensible solution.

For testing purposes, yes, run dbms_stats for a single table (with sql_trace on), then drop the stats and try analyze.

select * from user_tables/indexes where table_name = 'THE TABLE YOU PICKED'; should show you the differences in behaviour / results. Check the trace file to see the SQL that gets kicked off by the dbms_stats package.

The problem with CBO in general is that you have to know a great deal about the application to decide whether a change in the numbers is significant or not - that's why many sites are very reluctant still to move from RBO to CBO.

--
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 <3b38fa06.345376734_at_news.alt.net>...


>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