Re: Does table monitoring handle all statistics for you?

On Tue, 26 Jun 2001 20:35:03 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>MONITOR simply collects in real time
>the number of changes you make to
>the table - it does NOT write any stats
>back to the USER_TABLES columns.
>
>If your user_tables.num_rows says
>1,000,000, and you do 50,000 inserts
>on the table, then user_tables.num_rows
>will still say 1,000,000; but there will
>be a row in view USER_MODIFICATIONS
>for that table with
> user_modifications.inserts = 50,000

I do not see Num_Rows in User_Tables. Nor do I see a view USER_MODIFICATIONS.
>
>The CBO and the ANALYZE command
>will not make any use of that value -
>it simply acts as a flag to the dbms_stats
>procedure to suggest that the table has
>changed a lot since the last analyze.

So that tells it not to use the statistics because they are possibly outadated? Or does it tell it to modify is use of the statistics?

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"?

>dbms_stats collects (if I recall correctly)
>the current number of rows physically in
>the table, the number of blocks, the
>number of free blocks, and the average
>free space per block; (it misses a couple
>that the ANALYZE command gathers),
>and a few figures about each column
>such as number of nulls, high and low
>values, number of distinct values.

So which is it better to run, DBMS_STATS or ANALYZE.

In Oracle8i Concepts, Chapter 21 The Optimizer it says

The ANALYZE statement can also generate statistics for cost-based optimization. Using ANALYZE for this purpose is not recommended because of various restrictions, for example:

ANALYZE always runs serially.

ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values.

For partitioned tables and indexes, ANALYZE gathers statistics for the individual partitions and then calculates the global statistics from the partition statistics.

For composite partitioning, ANALYZE gathers statistics for the subpartitions and then calculates the partition statistics and global statistics from the subpartition statistics.

ANALYZE cannot overwrite or delete some of the values of statistics that were gathered by DBMS_STATS.

ANALYZE can gather additional information that is not used by the optimizer, such as information about chained rows and the structural integrity of indexes, tables, and clusters. DBMS_STATS does not gather this information.

It would seem that it says to use DBMS_STATS.

Brian Received on Tue Jun 26 2001 - 15:17:28 CDT