Re: question regarding statistics

One more point worth making - the default behaviour for dbms_stats may do different things from the apparently identical analyze call.

For example,

    analyze table t compute statistics
cascades to analyzing the index by default

dbms_stats.gather_table_stats(user,'t')
does not cascade. (And the defaults
for the other parameters keep changing
with each release of Oracle as well).

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Jan 21st 2005

• Original Message ----- From: "Mladen Gogala" <mgogala_at_allegientsystems.com> To: <bunjibry_at_gmail.com> Cc: "Oracle-L" <oracle-l_at_freelists.org> Sent: Monday, March 07, 2005 6:38 PM Subject: Re: question regarding statistics

Bryan Wells wrote:

>i may have asked this once before, so if it is indeed redundant i
>appologize:
>
>is there a difference in analyze and compute statistics?
>
>

You probably mean whether there is adifference between DBMS_STATS and analyze. There are several,
differences and we can divide them in two groups:

1. Performance differences DBMS_STATS can utilize PQ. Analyze can not.
2. Essential differences DBMS_STATS package can gather global statistics at multiple levels as specified by the granularity parameter. In contrast, the legacy ANALYZE command collects statistics only at the lowest level and derives higher level statistics by aggregation. These aggregated statistics are sometimes less accurate than the global statistics since it is not possible to precisely determine the overlap of values across partitions. Statistics for the number of distinct values and density are particularly susceptible to inaccuracy DBMS_STATS can populate auxiliary statistics, needed for CPU costing. Analyze can not.

More detail in Note:114671.1

-- 
Mladen Gogala
Oracle DBA
Ext. 121


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


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