RE: want to stop analyze once the database is stable

Zhu

>We have a 3rd party application running on our database, and the
>vendor asked us to run daily analyze , for those tables with
>dba_segments/dba_tables shows 5%+ difference for blocks.
>And we consistenly run into problem with the SQL plan changed, as
>sometimes analyze caused the plan goes bad.

If you gather the statistics and you get bad execution plans doesn't mean the statistics are bad! In fact there are at least three main causes to unstable execution plans:

- wrong statistics are gathered (with small sample sizes isn't always possible to have good one, but there are another reasons as well, especially in 8i...)
- statistics are good (correctly describes data!) but the CBO is wrongly configured
- statistics are good and the CBO is correctly configured --> CBO is wrong (let's say buggy...)

>I want to stop the anlyze, as our current plan is ok, which can
>meet our SLA requirement. And our database is already 5TB+, I think if
>we stop analyze, and the CBO statistics does not get updated, the plan
>should be ok and stable.

The main problem I see, in stopping the gathering, is with max values and new partitions. If you stop the CBO, with the time, will get completely wrong statistics. Even if it takes some time... you will have a problem.

>How about your opinion?

IMHO you should investigate *why* the gathering of statistics cause a change of execution plans. Then, when you know the cause, you can solve your problem.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l