Re: want to stop analyze once the database is stable
- From: "The Human Fly" <sjaffarhussain@xxxxxxxxx>
- To: zhuchao@xxxxxxxxx
- Date: Sun, 2 Apr 2006 17:20:45 +0300
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
> --
> //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."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
