Re: how does CBO handle old statistics

Ben wrote:
> I supposed it was still using the old statistics. The data wasn't
> consistent what so ever though. The tables had grow probably over 300%
> over the last 2 years. The dba that was here at the time it was last
> analyzed probably used dbms_utility.analyze_schema compute. When I ran
> dbms_stats.gather_schema_stats some queries started running very long.
> I deleted the new stats and forced it to use the rule based which sped
> it up.

Did you save a copy of the stats before you generated the new ones?

If so you should probably fall back to that version for the time being.

There may very well be reasons that new stats were not needed.

> Now I'm toying with the different method_opt settings to see what needs
> to be used to speed it up, but nothing seems to be faster than forcing
> it to use rule based.

Are you experimenting in production?

Probably you should export everything out and do your testing in a development environment. Received on Sat Mar 11 2006 - 19:20:20 CST