Re: want to stop analyze once the database is stable

Zhu,

Why dont you enable table monitoring and gather stats using 'gather auto' option?

 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.

My any chance, optimizer droping few of the filters/access. I have discussed similar things at my blog, about Transitive Closure, go through it, it might hlep you.

http://jaffardba.blogspot.com/

If you are using oracle 10g, you can skip the tables while collecting the stats.

Jaffar

On 4/2/06, zhu chao <zhuchao_at_gmail.com> wrote:
> 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
>
>
>

--
Best Regards,
Syed Jaffar Hussain
8i,9i & 10g, OCP DBA
Banque Saudi Fransi,
Saudi Arabia
http://jaffardba.blogspot.com/
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."
--
http://www.freelists.org/webpage/oracle-l