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@xxxxxxxxx> 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@xxxxxxxxxxxx> 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


Other related posts: