| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: oracle can ignore hints
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_at_audit-commission.gov.uk>:
> 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- -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-
-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 nestedloops 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_at_freelists.org [mailto:AC.GWIA.oracle-l_at_freelists.org]
>
> Sent: Sat 13/03/2004 00:06
> To: oracle-l_at_freelists.org; ryan.gaffuri_at_cox.net
> 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_at_lifetouch.com>
> To: <oracle-l_at_freelists.org>
> 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_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Niall Litchfield
> > Sent: Friday, March 12, 2004 9:54 AM
> > To: oracle-l_at_freelists.org
> > 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_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
> > -----------------------------------------------------------------
> > ----------------------------------------------------------------
> > 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
> > -----------------------------------------------------------------
> >
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
-- 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 Sat Mar 13 2004 - 16:51:10 CST
 |
 |