RE: Optimizer behavior

Other people have mentioned that running ANALYZE estimate will indeed overwrite the COMPUTE stats. I would like to add that the frequency for doing analyze really depends on the rate of changes to the data. If the data is fairly static your monthly may be enough to keep up. If on the other hand you are doing a lot of OLTP with the inserts and updates really flying you may want to a look at daily estimates during off hours. Then you could tune the few problem-child queries using fixed plans and hints to make up for CBO quirks and use 25% estimates. Someone suggested 49% estimates, but that's so close to the 50% of a COMPUTE analyze that I don't know if it's worth the difference. Perhaps if someone has determined that a 50% estimate is still faster than a COMPUTE that would be interesting.

Regards,
Chris Gait

> We have a database set to CHOOSE. Although we would like to
> run compute statistics weekly on a particular schema's tables,
> it takes too long. If we were to do compute statistics once
> per month and estimate statistics weekly, would that be efficient?
> When estimate statistics is done is it overriding all of the
> statistics from compute statistics?
Received on Mon Jul 10 2000 - 14:11:08 CDT