RE: CBO irregularity

From: Dan Tow <dantow_at_singingsql.com>
Date: Mon, 7 Jun 2004 20:22:04 -0500
Message-ID: <1086657724.40c514bc138a5@www.singingsql.com>

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 Cary 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:

Reality conflicts with CBO assumptions. Examples are:
The CBO does not know how selective a condition will be after a particular bind variable is set. Bind variable peeking doesn't solve this either, by the way - do you want the plan you'll be stuck with till the SQL falls out of the shared pool to be dependent on whether Oracle first sees a report on a large 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 optimally only for huge date ranges (likely reverting to a full table scan) because it happened to age out and come back with the first example of binding seen for date bind vaiables specifying a huge date range...
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 partially 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 great many plans are going to look equally fast, although the reality is that they vary greatly.
Combinatorics defeat the optimizer search. If the optimizer found the best plan before it ran up against its search limit, it'd take it, but the enormous number of join orders exceeds that limit.

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 good stats, including necesary histograms, suffice. Where those do *not* suffice, I've heard all the theoretical reason to avoid directive hints, but I find them unpersuasive:

Theoretical-issue) You restrict the optimizer's freedom to do the right thing later.

Sure, but why is that a problem?

Data distributions may change, calling for a different plan than today's optimum. - As I mentioned in my book, in over 10 years of intensive SQL tuning, I have *never*, *not once* seen a real-world case where a well-chosen, robust execution plan could not deliver good enough performance for all real-world, production data distributions. 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 the total count of such cases seen across the whole group, without burdening the 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 cross the line from tuned to untuned as data distributions evolve.
Oracle, itself, may change, opening up new opportunities for execution plans that are inferior (or simply unavailable) with current code, but will be best someday. - Do we imagine that changes to Oracle will actually make the current execution plan that is optimized today actually *bad*??! I've certainly never seen *that* happen - Oracle learns new tricks, certainly, but it has never in my experience actually become *worse* at performing its *old* tricks (at least as far as robust execution plans go!)! On the contrary, even with normal expected hardware improvements, alone, today's just-barely-adequate tuned SQL is likely 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 statement (if we leave it free of directive hints) *may* be *even better* than today's potential optimum performance after improvement on future hardware? That certainly would not be my choice!

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, unchanged forever, once we tune it.
- Hardly! It is almost certainly the case that functionality changes and changes to the underlying database design will change the SQL *long* before any hypothetical future arrives when you'd wish the SQL had fewer restrictions!

Assumption-I-don't-agree-with B) The application has just loads of SQL that needs tuning, so it would be really horrible if we had to re-visit any SQL we thought we'd already tuned.
- I admit that as someone making his living tuning SQL and teaching SQL tuning, this assumption has a certain appeal! Unfortunately for my income, the reality 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 that you only manually tune the SQL that demonstrates it truly matters to end users and to the business, you will almost certainly need to tune at most a few dozen statements per application, a tiny fraction of the SQL. 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 going 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, the 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 brute-force advantage couldn't be overcome. 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 if 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 without embarrassment until you have your chosen plan. Compare performance of the best no-directive-hints SQL with performance of your directive-hints SQL. If the difference is enough to matter to end users and/or to overall load, well, you just got evidence that you're at least sometimes a smarter tuner than the CBO. If the CBO beat you with Informative hints, only, use the CBO's best. If the CBO's best beats your best often, by enough to matter, consider whether your method of finding the right execution plan is right, or if you just haven't mastered it, yet.

Thanks,

Dan Tow
650-858-1557
www.singingsql.com

Quoting Cary Millsap <cary.millsap_at_hotsos.com>:

> It's what CBO is built to do: take into account a tremendous number of
> variables, each of which has a right to influence the optimizer's choice for
> best plan. CBO tends to work pretty well when (a) you give it the
> information it needs, and (b) you let it do its job (okay, and maybe also
> "(c) you wait 'til 9.2 to use it").
>
>
>
> By (a), I mean things like:
>
>
>
> - Make sure that your schema statistics reasonably represent your
> data. For example, if you tell CBO that a million-row table has 100 rows in
> it, then CBO will make dumb decisions about what to do with the table.
>
> - Make sure that your system statistics reasonably represent the
> operational characteristics of your system. For example, if your system
> really averages 3.7 blocks per multi-block read, then CBO will make dumb
> decisions about whether to do full-table scans on systems were
> db_file_multiblock_read_count=128.
>
> - Collect histograms for skewed data. For example, if you tell CBO
> that an attribute with domain cardinality 2 has uniformly distributed
> values, then CBO will make dumb decisions if the real distribution is 99/1
> instead of 50/50.
>
> - Use informative hints. For example (from a Tom Kyte training
> course I attended), use ALL_ROWS, FIRST_ROWS(n), FIRST_ROWS, CHOOSE,
> (NO)REWRITE, DRIVING_SITE, (NO)PARALLEL, (NO)APPEND, CURSOR_SHARING_EXACT,
> DYNAMIC_SAMPLING, and CARDINALITY.
>
>
>
> By (b), I mean things like:
>
>
>
> - Don't use plan-restricting hints, except in test situations where
> you're trying to cause bad performance. For example, don't use hints like
> RULE, ORDERED, USE_NL, INDEX, USE_HASH, FULL, AND_EQUAL, etc. in production.
>
>
>
> If you really want plan stability, stored outlines can give you that. But
> the adaptive intelligence of a well-informed and properly functioning CBO is
> a smarter long-term decision except in rare cases.
>
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> Nullius in verba
>
> Upcoming events:
> - Performance <http://www.hotsos.com/courses/PD101.php> Diagnosis 101: 6/22
> Pittsburgh, 7/20 Cleveland, 8/10 Boston
> - SQL Optimization <http://www.hotsos.com/courses/OP101.php> 101: 5/24 San
> Diego, 6/14 Chicago, 6/28 Denver
> - Hotsos Symposium 2005: March 6-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Rick Stephenson
> Sent: Monday, June 07, 2004 2:36 PM
> To: oracle-l_at_freelists.org
> Subject: CBO irregularity
>
>
>
> The CBO has been nothing short of a pain in the butt to me. Going from
> Development to QA to a live environment achieves unexpected results. It
> seems that you never know what you are going to get when it comes to an
> execution plan. The developers run Oracle on their Windows box and the
> execution path is one way, but when it gets moved to a QA environment it
> chooses another way. At least with the RULE base optimizer you know what
> you are going to get.
>
>
>
> Sometimes I think I am the only one with this problem. How do you work
> this? Do you always use hints, do you use stored outlines..?
>
>
>
> Thanks,
>
>
>
> Rick Stephenson
>
>
>
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jun 07 2004 - 20:19:02 CDT