on 12/23/04 1:26 PM, Wolfgang Breitling at breitliw@(protected) wrote:
> first off, whatever you do, DO NOT use 2) dbms_utility
>
> What I do is:
> 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.
