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 eyes must be going. I see it now. I mised it the last couple times I looked.

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

Are there guidelines to know what needs more complete statistics? Or is it just that each table would need to be tested? I'm looking for basic guidelines to start with.

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

Maybe I'll try this tomorrow. I ought to report back on this later.

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

I ran DBMS_STATS and got between three and five time speed performance on some queries. I let some stats go stale and the queries took between ten and twenty times as long. One query jumped from twenty minutes (with stale statistics) to eight seconds.

I know statistics improve performance. I'm trying to figure out how and how often to update those statistics.

There is one table with over 532,000 records right now, and it gets anywhere between a few hundred to a few thousand additional records per day. If I let it go a week without updating statistics, a specific query takes noticeably longer. Does it make sense to update statistics on that one table daily?

Brian Received on Tue Jun 26 2001 - 17:29:53 CDT