Re: want to stop analyze once the database is stable

Thanks, Chris for your valuable inputs!
Yes, we are using 2 percent analyze now. It is bad, maybe. We might need to adjust it to some higher value, The constriant is, the tables are huge and 10 percent sometimes never finish, or fail with 1555. But maybe those huge table are rarely analyzed, as it will be a very long time for those huge tables to grow another 5 percent in size. I do need to audit which tables are analyzed at each time.

You are right, I am also afraid of this, max-value typically comes with columns populated from sysdate. And new added partition got no statistics. We do add partition yearly.

I think we either use stored outline to fix the plan, or stop analyze. There are some huge join like 5-6, even 10-15 tables join in the application and it is impossible to dig into and see why plan got changed after analyze, I am afraid even with compute statistics, CBO can still choose wrong plan.

Thx very much
On 4/2/06, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
> Zhu
>
> 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...)
>
> 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.
>

 >
> 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
>

--
Regards
Zhu Chao
www.cnoug.org
--
http://www.freelists.org/webpage/oracle-l