Re: want to stop analyze once the database is stable

Zhu,

I HAVE to reply to this ( and you know it and maybe even counted on it :-) ). The way I see it, you have two choices:

1. Do the daily analyze and then deal with the problems it causes
2. NOT do the daily analyze and then deal with the problems IT causes

I guess everyone can see where I'm heading with this. I see nothing wrong with stopping the daily analyze, particularly given that it causes problems.

Of course, Christian's point of finding out why, and which, change in statistics causes the plan to go bad is valid. But it can just as easily be turned around. You have got a stable system that doesn't change very much structurally. Sure, there are transaction tables tables that keep growing but they have already reached a size where the optimizer has settled on a plan that is likely correct even if the table doubles or triples in size.
There WILL be tables, columns, partitions (if you have them) or indexes for which you need to keep refreshing the statistics. Keep an eye on columns with monotonically growing (or decreasing ) values which are also used in equality or range predicates, especially if these (the predicates) include most recent values which could be beyond what stale statistics say. But the vast majority of objects do not need a constant refresh of statistics.

At 08:56 PM 4/1/2006, zhu chao wrote:
>hi, all,
> We have a 3rd party application running on our database, and the
>vendor asked us to run daily analyze , for those tables with
>dba_segments/dba_tables shows 5%+ difference for blocks.
> And we consistenly run into problem with the SQL plan changed, as
>sometimes analyze caused the plan goes bad.
>
> I want to stop the anlyze, as our current plan is ok, which can
>meet our SLA requirement. And our database is already 5TB+, I think if
>we stop analyze, and the CBO statistics does not get updated, the plan
>should be ok and stable.
>
> How about your opinion? We are still running oracle 8174 on solaris8.
>
>--
>Regards
>Zhu Chao
>www.cnoug.org
>--
>http://www.freelists.org/webpage/oracle-l

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l