Re: Few questions about statistics

Hi, Pavuls.

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
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#721.

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

Regards,
Cerri

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.
>
>
> TIA
> Uldis Pavuls
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l