Re: want to stop analyze once the database is stable
- From: "zhu chao" <zhuchao@xxxxxxxxx>
- To: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- Date: Sun, 2 Apr 2006 07:12:18 -0700
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
Other related posts:
