Re: Which one is good in terms of performance

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracledbam@xxxxxxxxxxx
  • Date: Thu, 23 Dec 2004 13:26:34 -0700

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
> 
> 
> --
> //www.freelists.org/webpage/oracle-l
> 

-- 
Regards

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

Other related posts: