RE: oracle can ignore hints
- From: Dan Tow <dantow@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 13 Mar 2004 14:54:22 -0800
Hi, I appreciate the constructive criticism and commentary. I'm wading in
again, with a few comments on the comments regarding my book, with my comments
inline:
Dan
Quoting Niall Litchfield <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>:
> Well the argument in Dan's book (as far as I understand it) is that for most
> typical apps there exists an optimal or near optimal execution plan (usually
> but not always driven by NL access to data via appropriate indexes) and that
> if you figure out this execution plan it will stay pretty much optimum
> throughout the life of the db (robustness). The book then tells you how you
> might figure out this plan. In other words get the plan right early and you
> can be pretty sure it will remain acceptable.
>
> I'm busy getting infuriated by the book at the moment - it is *extremely*
> well written and the premise (that there is a 'method' to determining optimum
> execution plans rather than hit and miss guesswork) resonates hugely with me,
> at the moment there are too many assertions for me to be entirely happy with
> it, but that is probably a reflection of the fact that a) I haven't read it
> all and b) stating things up front as true and leaving the proof or otherwise
> till later irritates me. It also seems to me that it has OLTP in mind, but
> then that is the class of apps that we run so that is fair enough.
>
Glad you found it well-written - sorry you find it infuriating! I gather that
the main source of infuriation is the assertion-first-proof-later style. That
was a deliberate, tough call, and I knew my choice would bother some of my
favorite readers (e.g., members of this advanced group). As explanation, if not
excuse: I made a guess that the other choice would bother even more readers -
my idea was that most readers would be impatient to learn how to actually fix
queries, and many would not even be interested in the reasons behind the
method, taking the material more or less on faith. The method is already
complex enough that I couldn't really complete the answer to how-to-fix-basic-
queries until Chapter 6 (just over half-way through), and I was concerned too
many readers would give up if I folded in too much explanation. Everything I've
heard since reinforces the notion that the book *does* require some patience,
and I was trying to keep that under control. My pardon to the readers advanced
enough, patient enough, and self-sufficient enough to prefer the proofs up
front.
Niall and Steve have both accuarately stated my views on hints, and on finding
the right plan once and for all, which is really to say, the right plan until
functionality demands a change in the SQL, which probably won't be long, and
will likely require fresh tuning work. (I would certainly never advocate
keeping hints in place, unexamined, if you change the FROM or WHERE clause! I'd
also *never* advocate replacing simple tables with complex views in the process
of evolving a database, without re-examining every high-impact queries that
references those new views. This is true even if the queries do not have hints,
but it is even more true if they do, because the hints will almost certainly
become wrong, if they are more specific than
/*+ FIRST_ROWS */
)
There's been an awful lot of concern in and out of this group about whether the
book's method applies outside OLTP. Since databases don't care whether they are
handing rows to an OLTP application or a non-OLTP application, I'd like to
propose a more useful classification for the discussion of where the method
applies, and how well (I can't say whether I'm re-inventing the wheel, here
(anyone?), but this is original as far as I know:
Consider 4 quadrants, based on the size of the biggest table queried, and the
fraction of rows returned from that biggest table (apologies if this crude
graphic gets distorted on your screen):
Q2 | Q3 - high fraction returned
---------------------------------------------------------------------
Q1 | Q4 - low fraction returned
|
only small | Big biggest table
tables
Just to be concrete, let's say that a "big" table is over 100K rows, and a high
fraction is over 1%, though practically speaking the dividing line will be
fuzzy, and might even move somewhat with technology.
Q1 queries are so easy that you generally have to go out of your way to make
the optimizer fail to find a fine plan on its own - they are rarely the subject
of tuning exercises.
Q2 queries (think a blind picklist against a small table, possibly joined to
some other small tables, or a comprehensive report against small tables) are
slower than Q1 queries (unless the tables are *really* small) and are also
generally easy for the CBO to optimize well, because high caching and the at-
least-relatively-modest returned rowcount makes these hard to screw up *too*
badly. Theoretically, Q2 queries might need tuning because they get repeated
really often (they are fast, but need to be even faster) during some process,
but comprehensive queries like this rarely need be repeated in a loop.
Q3 queries are rare (in terms of how often they run, how often they are
written, and how often they need tuning), and should be avoided in OLTP. This
is the class of queries I spent relatively little space discussing in the book.
This was a conscious (but possibly not super-well-explained) decision based on
several factors:
-Since they are rare (based on the rarity that a very high number of returned
rows is useful in *even non-OLTP applications*), they deserve less focus.
-Optimizers actually handle them relatively well - join order tends to matter
less than for Q4, and hash joins are likely fine.
-Very often, the best solution to these is not to tune the query at all, but to
alter the application so it does not *need* so many rows, or needs them much
less frequently, so current performance becomes tolerable. This sort of outside-
the-SQL solution is the main focus of my last chapter, Chapter 10.
I *do* discuss the main modification to the usual method when you really need
to tune Q3 queries, however, in the 4-page section "How to Consider Hash Joins"
near the end of Chapter 6, however. I suspect that the brevity (and lateness)
of this section is behind the general idea that I completely dissed
(that's "disrespected" or undervalued, to readers not up on American slang)
hash joins in my book, and therefore surely had given the non-OLTP world little
thought. My intention was to lay out a method that gets the best possible OLTP
plan *first*. In most cases (especially the more-common Q1 and Q3 cases), that
plan turns out to be best, or close enough to best that the difference doesn't
matter. In the cases where it *does* matter, you still need it, though, because
only after you know the best-possible OLTP join order, and the number of rows
it will touch in every table in that order, can you correctly decide whether to
replace any given nested-loops join with a hash join. Since the join-type
decision is pretty simple and decoupled from the rest of the optimization
problem, once you have that best-possible nested-loops join order in hand, that
part of the solution only took 4 pages to describe, leaving the impression that
I think very little of hash joins. This was really more a function of the ease
of the problem, though, than of the importance of hash joins.
I contend that if you apply the Chapter-10 rules for getting rid of Q3 queries,
where possible, and the Chapter-6 section on hash joins, the book handles Q3
queries just fine. However, I admit that I didn't emphasize these queries
enough to give the non-OLTP tuner confidence, if he or she has a lot of these.
Q4 queries are by far the most common queries that you will need to tune, and
especially include the bulk of the *hard* tuning problems that call for a
sophisticated tuning approach. These queries are common in OLTP applications,
obviously, *but they are also very common in non-OLTP applications*! Q4 queries
are very likely to need nested loops to the largest tables, especially as the
fraction-returned gets really small, and the book's main-line message and
emphasis revolves around tuning these queries well. Even Q4 queries often
benefit *some* from doing hash joins to the smaller tables, in place of nested-
loops joins (as I point out in the book, under different terminology). This
choice, and the benefit from it, is also covered in the little section on hash
joins near the end of Chapter 6, but the benefit in overall runtime is usually
slight because joining to a modest number of rows of a small, well-cached table
is fast *any* way you do it - I generally trust the optimizer to join to the
smallest tables any way it wants (as long as it's not in the wrong point in the
join *order*) - the CBO is usually right regarding these choices, and almost
never *seriously* wrong.
Also, I contend that the *main* reason that hash joins look good to most
professionals is that they often do a much better job when the join order is
*wrong*! A Q4 query that drives from the *wrong* table is quite likely to badly
need hash joins to minimize the damage of that wrong choice! Keep the wrong
join order, and replace a hash join with nested loops, and you may well kill
the query. Fix the join order, though, and nested loops to the big tables are
much more likely to shine.
> I *do* think the book is important, I'm not yet sure how far I go along with
> it.
Thanks. Hope the above helped.
>
> Niall
>
> -----Original Message-----
> From: AC.GWIA.oracle-l@xxxxxxxxxxxxx [mailto:AC.GWIA.oracle-l@xxxxxxxxxxxxx]
>
> Sent: Sat 13/03/2004 00:06
> To: oracle-l@xxxxxxxxxxxxx; ryan.gaffuri@xxxxxxx
> Cc:
> Subject: Re: oracle can ignore hints
>
>
>
> hints are non-scalable solutions. if your data changes or the number of
> records retrieved changes, your hint stays there.
> ----- Original Message -----
> From: "STEVE OLLIG" <sollig@xxxxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Friday, March 12, 2004 11:11 AM
> Subject: RE: oracle can ignore hints
>
>
> > agreed. first be sure to care for and feed the CBO. it can save you a lot
>
> > of work.
> >
> > but when you've done that and you still have a query that needs help, then
>
> > hints may not be such a bad thing. it was my thinking that hints were
> bad -
> > didn't mean to imply it was yours. i was trying to say that Dan's book got
>
> > me to soften that view. and that, i think, is a good thing.
> >
> > and on whether a robust plan is still efficient in 3 years, 5 years,
> > whatever - Dan's book contends probably. i'm inclined to agree.
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Niall Litchfield
> > Sent: Friday, March 12, 2004 9:54 AM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: RE: oracle can ignore hints
> >
> >
> > Hi Steve
> >
> > > Sorry for chiming in late on this thread, but Lex's post
> > > caught my attention
> > > and then something in Niall's post prompted this. I've been
> > > reading Dan
> > > Tow's new SQL Tuning book. In it he addresses the attitude Niall's
> > > describing with this statement:
> > >
> > > "... bright people often look down on hints or treat them as
> > > a quick fix/
> > > workaround. If you view every hint you give as handicapping
> > > Oracle in some
> > > way you tend to avoid sprinkling them liberally throughout your code." >
> >
> > > I have to admit that I recognized myself when I read that
> > > (being bright and
> > > all). But Dan's book gave me a very different perspective.
> > > Hints may not
> > > be such a bad thing that should be avoided at all costs.
> >
> > I didn't necessarily mean to imply that hints were always bad and should be
>
> > avoided, I was more hoping to suggest that hints had a downside that isn't
>
> > always immediately apparent. Hints are great for getting slow running
> > queries to work fast enough again, quickly. They have the downside though >
> that upgrades or data changes may break the 'fix'.
> >
> > > Granted, we need
> > > to be smart about the usual care and feeding of the CBO so it
> > > can tune the
> > > vast majority of the queries that run in our databases. I
> > > know I don't have
> > > time to manually tune all the queries I write (not to mention other
> > > duhveloper's queries that need far more help). So I let the
> > > CBO do most of
> > > the work for me. And a well cared for CBO can do a pretty
> > > darn good job.
> > > But when I do manually tune a query, why not hint away at the
> > > exact robust
> > > plan I know works efficiently? Anyone?
> >
> > Will it still be efficient in 3 years time after a merger/demerger/change
> of
> > legislation/software upgrade/hardware upgrade etc etc? because 9 times out
>
> > of 10 the hint gets forgotten about.
> >
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission
> > +44 117 975 7805
> >
> >
> >
> > **********************************************************************
> > This email contains information intended for
> > the addressee only. It may be confidential
> > and may be the subject of legal and/or
> > professional privilege. Any dissemination,
> > distribution, copyright or use of this
> > communication without prior permission of
> > the sender is strictly prohibited.
> > **********************************************************************
> >
> > ----------------------------------------------------------------
> > 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
> > -----------------------------------------------------------------
> >
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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:
