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: Tue, 26 Jun 2001 21:32:37 +0100
Message-ID: <993587884.320.0.nnrp-14.9e984b29@news.demon.co.uk>

Comments inline

--
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 <3b38eb8a.341668171_at_news.alt.net>...

>
>
>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 - My error, the view is USER_TAB_MODIFICATIONS.
>
>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?
>
No - the CBO just doesn't care
>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 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,
>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.
>
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). One interesting detail is (under 8.1.5, at least) if you used dbms_stats against an IOT, it would raise an internal exception and switch to executing an ANALYZE anyway. (dbms_stats normally runs with a SAMPLE clause, which is not supported for IOTs).
>Brian
Received on Tue Jun 26 2001 - 15:32:37 CDT

Original text of this message

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