Re: 7.23 UC4 vs. 7.30 UC3

snip.... > > I've done a lot of performance tuning, and most of the time when the > > optimizer chooses the wrong path its because it needs better statistics > > to push it in the right direction. Have you explored this angle yet? > > Its really preferable to hardwiring hints into your code. > > > > Greg > > > > Good point Greg. To do this I'll have to sit down determine what is to be > done when. Some of our tables are large and rather dynamic. How do you handle > scheduling of update statistics on your tables? I already know about > statistics versus distribution, and that this can be done at the column > level. Did you have to do a lot of analysis and planning, and how did you > manage this? snip... Sounds like you have a bunch of typical not terribly volatile tables, and a couple of handfuls of "problem children" that need substantially more analysis and attention. You might routinely run update stats medium on all tables weeklyish, then right afterward, and every weeknight run a continuously developed script (via cron and dbaccess maybe) that does a much more careful and detailed ststistical update. I did that on an assignment prior to now and it worked great. Every time I found a new "problem child" (usually via some app programmer or end user complaining about suckey performance...) I added it to the script that ran every night of the week. It usually ran update stats high with lots of distribution buckets on hand chosen columns to make darn sure the optimizer knew what it was dealing with. It almost always made the right decision too - I was very impressed at the time, even more so now that I'm getting more familiar with Oracle. I believe Art Kagel has written some 4gl code to implement Informix' update stats recommended general strategy. I haven't played with it, but it may be enough without further analysis. I'll bet it works great, Art is clearly among the sharpest DBA's out there - look at it. Greg