Re: Can Gathering Statistics Ever Hurt Performance?

timburda_at_hotmail.com wrote:
> First, I've talked to a few Oracle DBAs about this. None of them can
> give hard and fast rules about when/how to gather statistics.
>
> At the same time, I have a database which has been in production for
> about 6 months, and the statistics have never been updated. While it's
> not hugely transactional, I think performance is suffering because the
> statistics haven't been updated (and I've confirmed this on our dev
> environment).
>
> I ran the following script in our dev environments for the schemas that
> I felt it was appropriate:
>
>
> dbms_stats.gather_schema_stats( ownname => P_SCHEMA_OWNER,
> options => 'GATHER',
> estimate_percent =>
> dbms_stats.auto_sample_size,
> method_opt => 'FOR TABLE
> FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 30',
> cascade => TRUE );
>
> I was planning to set this up to run once a week as part of a weekly
> process. Before I do, I was wondering if anyone had any problems with
> updating statisitcs to the point it hurt performance, or resulted in
> database crashes.
>
> Thanks -
>
> Tim Burda

Generally speaking the more accurate the information available to the cost based optimizer the better.

Certainly there are a few exceptions, there are for anything, where Oracle takes the best possible information and then goes the wrong way. But those situations are on the margin and easy corrected once identified.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)