Re: Optimizer

Typically developers in a large shop code queries to meet deadlines, not terribly worried about the sql performance. When the application bogs down at a production site, it is given to performance experts who say, 'holy cow, how could anyone code this?'

Let the developers code under the assumption that the cost based optimizer will make sense out of their queries. Keep statistics analyzed. Re-analyze only after significant changes in volumes. The cost based optimizer does a pretty good job most of the time. When a bottleneck is detected, find the expensive sql and optimize it. Hints may be added to override the default cost based behaviour.

In other words, worry about the worst 5% of sqls, and leave the others be. There can be many poorly performing sqls, but if they are hardly ever executed, optimizing them is not worth the effort.

As far as query optimization tools go,
we have purchased licenses for Quests' SQL Lab Expert. It will identify expensive sql, load it into a "lab" for explain plans and timings, and even offer hint suggestions.

In article <958301705.11737.0.nnrp-14.9e9864e7_at_news.demon.co.uk>,   "RL" <mark_at_mark-it.demon.co.uk> wrote:
> I appreciate this is probably a huge topic, but can anyone point
> me in the direction of information about the Oracle optimizer.
> In particular, what should the optimizer rule be
> set to (CHOOSE, RULE etc). If the optimizer is using cost
> based optimization, is it necessary to analyze tables regularly or
> can it be run once. Does the cost based optimizer always give better
> results that the rule based optimizer or are there too many variables
> to decide this.
>
> Are there are good tools to help with optimization, (where to add
> indexes etc), I found tkprof output very cryptic.
>
> Many thanks
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun May 14 2000 - 00:00:00 CDT