Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: CBO irregularity

RE: CBO irregularity

From: Mark W. Farnham <>
Date: Tue, 8 Jun 2004 07:52:55 -0400
Message-ID: <>

What a great thread! Here's hoping I don't screw it up.

Depending on context and situation, I agree either violently or begrudgingly with pretty much everything that has been written here.

So I'd like to mention 3 pet peeves for which I'd like to get support for Oracle improvements.

The first, which dates back to v7.1 pre-betas when the sql hash algorithm was being played around with and often generated huge clumps of sql hashing together, is that while it is wonderful to re-use parsed sql and even essential in cases where thousands of users just need to quickly re-execute exactly the same one line lookup with a different key, IF IT TAKES LONGER TO FIND THE MATCH THAN TO RE-PARSE, SOMETHING STUPID IS IN PROGRESS. So Oracle should have a bail out timer that says, hey, stop looking and just parse the doggone thing. Space in the shared pool is important, but time is more important. If you're hung up on a latch, parse to a one-time area. No excuses, you've coded up lots harder stuff than this. Yes, writing systems intelligently in the first place generally avoids the problem, but that does not solve the problem for the huge bulk of poorly coded essential functionality that is in production around the world (primarily emanating from guess where!)

The second, which Jonathan delightfully referenced, is about bind variable peeking. Now, if you look at the actual cost elements recorded for some sql that has been executed, you'll have a pretty good handle on cost of parsing versus cost of execution. If the execution is really cheap, go ahead, don't bother re-peeking. But if the cost of execution was significant compared to the cost of a parse, let's take another look. You might also want to mark a SQL for a re-plan if one of the bind variables drove a histogram based plan choice. So big deal if you get a few copies of the same code. YOU COULD EVEN USE A HANDFUL OF WINNERS AS STARTING POINTS FOR "PLAN COSTS TO BEAT" IN THE PERMUTATION SEARCH for the next value you get.

The third, which regards the whole question of finding marginally or surprisingly better plans than an existing "good enough and stable plan," is why not give us a tool to run in slack time that says: Take a look at my most consumptive SQLs and the actual execution cost results, ignore my hints, and permute until the cows come home finding me a better plan and let me know about it. Maybe I'll change my mind on the hints.

This last one would help us all clean up from hints that were either legitimate BUG workarounds no longer needed or lucky shots in the dark that happenned to solve a problem some time. Such queries may not have risen to the level of waste that you notice individually, nor would it often have a positive internal rate of return to go out and find them, but it fits together with all the positive aspects of Oracle's tuning automation initiative to find them for us.


-----Original Message-----
[]On Behalf Of Jonathan Lewis Sent: Tuesday, June 08, 2004 4:32 AM
Subject: Re: CBO irregularity

Notes in-line.


Jonathan Lewis The Co-operative Oracle Users' FAQ Optimising Oracle Seminar - schedule updated May 1st

: I've gotta respectfully disagree on this one, Cary. I've tuned a fair
amount of
: 9.2 SQL by now, and there are definitely still common situations where I
: couldn't get it to do the right thing with one of the "Informative" hints
: lists, needing one or more of the directive hints (ordered, index(...))
: instead. The main reasons this still happens (and is likely to continue to
: happen for quite a long time) are:
: 1) Reality conflicts with CBO assumptions. Examples are:
: a) The CBO does not know how selective a condition will be after a
: bind variable is set. Bind variable peeking doesn't solve this either, by
: way - do you want the plan you'll be stuck with till the SQL falls out of
: shared pool to be dependent on whether Oracle first sees a report on a
: date range or a small date range? You thought you had that SQL tuned and
: tested, but 4 hours before the quarter close it starts performing
: only for huge date ranges (likely reverting to a full table scan) because
: happened to age out and come back with the first example of binding seen
: date bind vaiables specifying a huge date range...

    I don't know how others feel, but I think that mandatory     bind variable peeking was a bad mistake. In some cases     it sounds like a nice idea; but I prefer to pay the price of     skewed data (which I think is the target) in different ways.

: b) Conditions/joins are not independent. You and I know that open orders
: generally point to recent order lines, but the databsase doesn't know that
, no
: not even if you give it histograms on every column. When the CBO sees
: redundant conditions (such as an Open_Flag on the order and an Update_Date
on an
: Order_Line), it is very likely to calculate that after it evaluates
: both conditions, it'll be down to essentially 0 rows. From that point, a
: many plans are going to look equally fast, although the reality is that
: vary greatly.

    Agree with the general principle - but it doesn't help that     Oracle is a little bit broken in 'recent dates' area, and that     the pendulum has swung too hard in the direction of     bind-variables when things like 'open flag' should be present     as literals.

: 2) Combinatorics defeat the optimizer search. If the optimizer found the
: plan before it ran up against its search limit, it'd take it, but the
: number of join orders exceeds that limit.

    I've rarely found this to be true. The problem is more likely     to be that one of the issues you've given in part (1) means there     is at least one selectivity that is so far off that Oracle will discard     the optimum path (or would discard it if it got there).

: These are good, solid reasons to use directive hints unapologetically,
even if
: (as is normally the case) you cannot find an outright Oracle bug that
makes the
: hint necessary.
: I like the idea of avoiding directive hints where informative hints and
: stats, including necesary histograms, suffice. Where those do *not*
: I've heard all the theoretical reason to avoid directive hints, but I find
: them unpersuasive:

    Agree (ignoring the minor quibbles). I think the context of the     Tom Kyte comments is that hints are a last resort in tweaking     rather than a first step; and if they are a last resort there are     some hints that are more harmless than others. (Looking at     the list that Cary quoted, though, I'm not in 100% agreement     with Tom's list - but that may, again be context).

: Theoretical-issue) You restrict the optimizer's freedom to do the right
: later.
: Sure, but why is that a problem?

    It depends where you started from.

    Very few people hint their code properly (i.e. completely).     Too many people hint too much of the code.     If you upgrade and find that a perfectly reasonable hint     in the previous version turns into a disaster in the next     version.
    If you get 250 pieces of SQL running very slowly, what     do you do to fix them.
    If you get just 3 pieces of SQL running very slowly, that's     less of a problem.

: a) Data distributions may change, calling for a different plan than
: optimum.
: - As I mentioned in my book, in over 10 years of intensive SQL tuning, I
: *never*, *not once* seen a real-world case where a well-chosen, robust
: execution plan could not deliver good enough performance for all
: production data distributions.

    But think how often you see the complaint:

        This code ran perfectly well in development,
        but the same execution path (which has been
        forced by hints) is a disaster on production.
        Again, it depends on your starting point.
        I see a huge waste of human effort spent on
        fiddling around with hints because of a lack of
        awareness of what cost based optimisation means.

    I think you're right about carefully considered production     systems - each SQL statement (in an OLTP system) is going to     have its one plan which is going to be right for the life of     the business. The probability of norrmal fluctuations in business     patterns is unlikely to make a plan 'the catastrophically wrong'     plan. If the data distribution changes so much that lots of execution     plans become sub-optimal, the business probably has a much     larger problem than worrying about re-tuning a load of SQL     statements.

: I make no claim that none of
 you have seen such
: a case. (If you'll send me a note, personally, I'll inform the group of
: total count of such cases seen across the whole group, without burdening
: group with a lot of details.) However, unless my experience is completely
: freakish, the cost of living with a whole lot of SQL (which I see very
: regularly) that is bad *today* without directive hints will *hugely*
exceed the
: cost of later having to hand-retune one or two statements/year that might
: the line from tuned to untuned as data distributions evolve.

    My experience has been that it's only ever a few SQL     statements that have to be sorted out to deal with the     major excesses anyway.

: b) Oracle, itself, may change, opening up new opportunities for execution
: that are inferior (or simply unavailable) with current code, but will be
: someday.
: - Do we imagine that changes to Oracle will actually make the current
: plan that is optimized today actually *bad*??! I've certainly never seen
: happen - Oracle learns new tricks, certainly, but it has never in my
: actually become *worse* at performing its *old* tricks (at least as far as
: robust execution plans go!)!

    On a reasonable number of occasions, I've found that     removing the existing hints from a badly performing     piece of SQL has magically improved the performance.     Yes, upgrades to Oracle can make an existing set of     hint do something bad. (Sometimes because bugs kick in     at the unluckiest places, and sometimes because of the     selectivity issues you mentioned further up the page).

                                                     On the contrary, even
with normal expected
: hardware improvements, alone, today's just-barely-adequate tuned SQL is
: to be so good at this future date that we don't even *care* if it could be
: better. So, we should except a dead certainty of non-optimal performance,
: today, because we think that at some unknown future date, this SQL
: (if we leave it free of directive hints) *may* be *even better* than
: potential optimum performance after improvement on future hardware? That
: certainly would not be my choice!

    I agree - 'it might get better on the upgrade, new hardware' etc.     is never a reason for leaving a problem in place. The only reason     for leaving it in place is if the fix costs more (in whatever currency     you are accounting) than the benefit.

    However -

: I see some false assumptions behind the case for avoiding directive hints:
: Assumption-I-don't-agree-with A) The SQL, and the hardware performance
will be
: cast in stone, while Oracle evolves, and we're stuck with the SQL,
: forever, once we tune it.
: - Hardly! It is almost certainly the case that functionality changes and
: to the underlying database design will change the SQL *long* before any
: hypothetical future arrives when you'd wish the SQL had fewer

    Got to agree with that. Have to remember, though, the massive     effort that is sometimes involved in getting even an obvious     error fixed - change control and all that.

: Assumption-I-don't-agree-with B) The application has just loads of SQL
: needs tuning, so it would be really horrible if we had to re-visit any SQL
: thought we'd already tuned.

    Change "any SQL" to "lots of SQL" and you can may be     a little more generous on the degree of sympathy.

: - I admit that as someone making his living tuning SQL and teaching SQL
: this assumption has a certain appeal! Unfortunately for my income, the
: is that if you correctly choose which SQL to manually tune (and go ahead
by all
: means and leave the rest to the CBO, with "informative" hints at most), so
: you only manually tune the SQL that demonstrates it truly matters to end
: and to the business, you will almost certainly need to tune at most a few
: statements per application, a tiny fraction of the SQL.

    Absolutely, spot on, 100% agree.

    It's the shotgun approach that sprays hints     indiscriminately around a development     environment (particularly) that causes the     trouble with hints, and leads to a loaded     condemnation of hints as a solution.

If, a few years down
: the line, you need to retune, say, 10% of those statements (which would be
: *way* more than my experience leads me to expect), well, that's probably
: to be *far* less work than the work you're going to need to do, *anyway*,
: because the application, and the way the end users *use* the application,
: evolves at a normal rate.
: Assumption-I-don't-agree-with C) The CBO is a whole lot smarter than you,
: tuner, are, when it comes to tuning SQL.
: - Well, this is maybe a pretty good assumption about the average, untaught
: tuner, but I wouldn't have written my book if I thought the CBO's
: advantage couldn't be overcome.

    But almost everyone is an "average untaught tuner", or worse     an "average, badly taught tuner". How is anyone supposed to     use hints correctly when the performance tuning guide (9.2)     pages 5-4/5-4 comes up with a section titled "Specifying a Full     Set of Hints" then gives an example which is far from a full     set of hints, and even manages to give the impression that     perhaps use_nl(a,b) means "use a nested loop from table a     to table b".

: Having said that, there's a
simple, safe test -
: . determine the best plan you can, following your choice of method for
: determining the best execution plan.
: . add appropriate "Informative" hints and histograms as relevant, and see
: they result in that best plan. If they do, you're done, and you get
evidence of
: the quality of the CBO and all its assumptions.
: . if you didn't get what you found as the best plan, add directive hints
: embarrassment until you have your chosen plan. Compare performance of the
: no-directive-hints SQL with performance of your directive-hints SQL. If
: difference is enough to matter to end users and/or to overall load, well,
: just got evidence that you're at least sometimes a smarter tuner than the

    I love that "enough to matter" and its resonance     with "compulsive tuning disorder". Even so, I would     be concerned about making sure that the hints were     expanded to a complete set of hints to make sure     that there was no way for Oracle to misinterpret them     at a later date. Sure, it may only be one statement     that has to be fixed - but it may be a couple of days     of complaints from users until it is.

: If the CBO beat you with Informative hints, only, use the CBO's best. If
: CBO's best beats your best often, by enough to matter, consider whether
: method of finding the right execution plan is right, or if you just
: mastered it, yet.

One trap you haven't mentioned, by the way, is the problem you face when someone comes along and drops 13 of the indexes on a particular table because they are clearly redundant. Of course, if you're dropping indexes, you will be doing some careful testing anyway (probably), but having to review the hints on all the SQL that references that table is just another stumbling block in the path of change.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Jun 08 2004 - 06:56:10 CDT

Original text of this message