Re: segment monitoring, stats, histograms

Comments inline.

Goulet, Dick wrote:

> Wolfgang has his reasons for not liking this approach, and I have to
> agree they are very significant and make sense. Basically it's a "if
> it's not broke, then don't fix it" approach.

This "if it ain't broke, don't fix it" of mine extents to the practice of gathering statistics. If what you have implemented works for you, then leave it alone. DON'T "fix" it, least of all because I said something based on my experiences. All I am trying to advocate is caution when implementing a new approach and think of the costs, benefits and risks. If the risks and costs outnumber the benefits then think again.

>
> I would highly recommend evaluating both approaches at your location.
 > Mine works here and Wolfgang's has been proven not to. My approach is not "not to analyze". It is to leave the statistics alone as much as possible. If some bad plans develop I investigate and if it is due to (really) stale statistics then I will re-analyze. If statistics of a particular table frequently go stale and cause performance problems I will analyze why and implement a strategy that fits those circumstances, and that may very well at that point involve monitoring the table and base the need to analyze on a determination of staleness from that usage pattern. But that could mean that much less than 10% modifications trigger "staleness", or just one of the DML operations (only inserts, or updates, or deletes, or any combination of two).
You could say that I am inherently lazy. If doing nothing is not causing any problems, then I choose to do nothing rather than doing something and risk that I have to do more to undo it. If that doing nothing IS causing problems, then I get off my butt and DO something about it so I can get back to doing nothing.

> Each site, each database instance, may function differently &
> consequently require a different approach. I see it as a part of the
> DBA job to apply the approach that works best in the respective
> environment.

Amen to that.

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l