Re: Which one is good in terms of performance

first off, whatever you do, DO NOT use 2) dbms_utility

What I do is:
I
a) DO NOT rely on Oracle's "staleness" algorithm but decide myself which tables need to be analyzed and at what frequency b) DO NOT use "for all indexed columns" but decide myself which columns require a histogram and with how many buckets. Those columns might very well include non-indexed ones and will certainly not include all indexed columns. I have an example where the creation of histograms on indexed columns led to a batch job taking an estimated 18+ hours (if we had had the patience to let it finish instead of killing it after 6 hours) instead of the ~90 seconds without the histograms c) use the dbms_stats procedures
d) use estimate_percent=>dbms_stats.auto_sample-size, cascade=>false, method_opt=>'for all columns size 1' (i.e. the defaults for the latter two parameters) for tables
e) follow with gather_index_stats with estimate_percent=>null (i.e. full compute) for indexes
f) follow with gather_table_stats with estimate_percent=> (i.e. full compute) for individual column histograms.

Seema Singh wrote:
> Hi,
>
> 1)
> exec dbms_stats.gather_schema_stats(ownname =>'''||username||
> ''',degree=>4,cascade =>TRUE,options=>''GATHER STALE'');
> 2)DBMS_UTILITY.ANALYZE_SCHEMA('USERNAME','COMPUTE');
> 3)ANALYZE TABLE <TABLENAME> COMPUTE STATTISTICS FOR ALL INDEXES COLUMNS;
> Please suggest which is best in case of cost based optimizer in Oracle9i.
> thanks
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l