Can Gathering Statistics Ever Hurt Performance?

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 Received on Mon Feb 13 2006 - 13:17:16 CST