RE: CBO irregularity
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 8 Jun 2004 07:52:55 -0400
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.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: Tuesday, June 08, 2004 4:32 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: CBO irregularity
Notes in-line.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
----- Original Message -----
From: "Dan Tow" <dantow@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, June 08, 2004 2:22 AM
Subject: RE: CBO irregularity
: 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:
:
: 1) Reality conflicts with CBO assumptions. Examples are:
: a) 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...
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
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.
:
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
best
: plan before it ran up against its search limit, it'd take it, but the
enormous
: 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
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:
:
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
thing
: 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
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.
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
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.
:
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
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 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
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 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,
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!
:
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
that
: needs tuning, so it would be really horrible if we had to re-visit any SQL
we
: 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
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.
**************************************
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
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.
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
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.
****************************************
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
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.
:
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: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
