Re: Few questions about statistics
Date: Tue, 15 Apr 2008 15:42:08 -0300
IMHO, 'up-to-date statistics' and 'missing relevant statistics' are different concepts with different solutions, but both can lead to performance problems.
If the statistics information available in data dictionary for CBO
(how to check them:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm#31058) reflect the reality of application data or differ from it less than (let me say*) 10%, as a rule o thumb we can say that you have up-to-date statistics. Until now, you don't know if you are missing relevant statistics or not.
If your application data statistics are up-to-date and you are
experiencing performance problems, then probably you are missing
important statistics for CBO to estimate the lower-cost access plan
AND/OR your database optimizer-related parameters are needing to be
fine-tuned. To understand how both these situations can impact your
environment I suggest you to read the 1st chapter of Performance Guide
and Reference book available at
- The 10% number was taken from http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm#25754: <quote oracle doc> Objects are considered stale when 10% of the total rows have been changed. </quote>
Hope it helps you a little.
2008/4/15, Uldis.Pavuls_at_tietoenator.com <Uldis.Pavuls_at_tietoenator.com>:
> Hi All
> In general I know the difference between ANALYZE units in DBMS_UTILITY
> and ones in DBMS_STATS. What I lack, it's a couple of details
> (particularly in 9.2 if it makes a difference):
> - Which exactly are the optimizer statistics (and which isn't)? Because
> it's said that DBMS_UTILITY don't gather all what's necessary for CBO,
> while, on the other hand, DBMS_STATS don't bother with stats not needed
> for CBO.
> - How can I know when the last time ANALYZE from DBMS_UTILITY had been
> run on the object and when a unit from DBMS_STATS (e.g.
> DBMS_STATS.GATHER_TABLE_STATS) had? (They both update dictionary, e.g.
> DBA_TABLES.LAST_ANALYZED). In fact this is a question whether the CBO
> sats are up to date and valid.
> Uldis Pavuls