Oracle Scratchpad

November 11, 2006

What is a hint ?

Filed under: CBO,Execution plans,Hints,Tuning — Jonathan Lewis @ 5:17 pm GMT Nov 11,2006

It’s a great shame that Oracle Corp. decided to use the name “hints” for its optimizer directive mechanism.  “Hints” are not hints, they are interception points in the optimizer code path, and must be obeyed.

Why do people think that Oracle “ignores” hints ? There are two main reasons.

  1. the available hints are not properly documented
  2. the hints are rarely used properly – because they are not documented properly.
  3. (there are a few bugs that make things go really wrong anyway) – and yes, I know that’s the third reason of two

Think of a hint as something which affects the optimizer’s route through its code path. To handle any one statement, the optimizer may work through many join orders, and each join order may entail several thousand calculations and decision points. At each moment, a hint may change a calculation, or affect the outcome of a decision. For example:

  • The /*+ no_merge */ hint makes the optimizer bypass some code that would otherwise transform your query using the rules of complex view merging.
  • The /*+ use_nl(t) */ hint stops the optimizer from evaluating the cost of a merge join or hash join - at a specific set of branch points in the code.
  • The /*+ parallel(t, 6) */ hint will (roughly speaking) make the optimizer divide a critical value by six – at a specific set of points in the code.

But the fact that the optimizer has adjusted some of the numbers or had its options pruned at various steps through the code path doesn’t mean that you will see the effect you were expecting.

The last example – the /*+ parallel(t, 6) */ - is a good example of why this can happen. The optimizer will, as ordered, divide the cost of a tablescan by six when it sees this hint (with an extra “fudge factor” thrown in at 10g) – but if there is a serial indexed access path that is cheaper than the tablescan the optimizer will take that serial access path.

You see, by giving the optimizer a set of hints you reduce the number of different ways in which the optimizer can get from the start to the end of a single join order, and may change the cost that drops out at the bottom of that join order.  But the optimizer still takes the path with the cheapest cost based on the routes that it has managed to find whilst following your directions.

So if you want to use “hints”, make sure you work out how to eliminate every path that you don’t want to happen. Don’t just stop with the odd couple of hints that just happen to get things working on your first attempt – it might just be the luck of the data that got you the correct path today, and things may change tomorrow.

Footnote: 10g gives you several new hints in the “Don’t do X” category. They’re there because they’re useful. Telling the optimizer what not to do is a lot easier than telling it to do only what you want. The impression I get from the new hints is that the optimizer group have adopted a strategy for adding a pair of hints each time they add a new optimizer feature: one to force the feature to be used, and one to block it.

19 Comments »

  1. Jonathan,
    If I were to search for a ‘better’ explanation of hints, where would I look for it. I did read your book on optimizer but I was doubting if I could take it as “all there is to know about hints” since the book is not about hints.
    From your ~marketting~ (loose) of hints, it seems that hints can and should be an integral part of SQL.
    Please advise.

    thanks
    Kirtan Desai

    Comment by Kirtan — November 12, 2006 @ 6:43 am GMT Nov 12,2006 | Reply

  2. To continue with my previous question,

    Should I take oracle documentation as the master source since that’s where it originates?

    Comment by Kirtan — November 12, 2006 @ 6:46 am GMT Nov 12,2006 | Reply

  3. Kirtan, I’m glad you qualified the word “marketing” as you did. If I were to market chain-saws in the same way, I would probably say something like:

    “Use our chainsaws, they’re so good we haven’t bothered to write a users’ guide because people rarely have fatal accidents and typically only lose minor limbs. If you do buy one, you might want to design and fit a safety guard before you use it.”

    You need to know how to use hints, and then avoid using them unless it is really necessary. For additional comments on the topic, here’s a copy of a note I wrote for the NoCOUG magazine a few months ago.

    I don’t know of any good documentation for hints. The Oracle documentation is as good a place as any to start – and you could try a text search for the word “hint” across the entire documentation set, as some of the hints are documented in manuals other than the Performance Guide. You could also search Metalink, as there is some literature there that occasionally helps to clarify the documentation.

    Initially, I was planning to devote about one-third of volume two of Cost Based Oracle to hints, but I’ve had to change my plans; so I may be writing about them in roughly a year’s time.

    Comment by Jonathan Lewis — November 12, 2006 @ 12:20 pm GMT Nov 12,2006 | Reply

  4. Thank You Jonathan. This will give me a good start.

    Comment by Kirtan Desai — November 12, 2006 @ 3:44 pm GMT Nov 12,2006 | Reply

  5. Jonathan,
    As always, insightful and food for thought. Your decision to give blogging a try is a boon for the Oracle community.

    For Kirtan, Oracle documentation is the place to start not the place to end…there is a lot about the Oracle database that are not covered in any official Oracle documentation and that is why we have consultants and gurus…:D

    Again, even with Oracle documentation, you do want to “Trust and Verify”…

    Comment by Peter K — November 12, 2006 @ 4:55 pm GMT Nov 12,2006 | Reply

  6. I HATE that Oracle have opted to use comment syntax for hints. It smacks of something like an Easter Egg that was meant to be hidden, and ‘escaped’ into the public domain, rather than something planned for.
    They corrupt the purpose of comments (which are supposed to be for people not software). Why should stripping comments from code affect the way the code is executed ? Don’t get me started on the no_trigger hint.
    They miss out on syntax/semantic validation. If the optimizer doesn’t do what you expect from a hint, its too easy to put the cause down to it being the wrong hint or that the hint was ignored, when you may actually have a syntax error in the hint (especially when Oracle change the syntax, such as specifying index columns rather than index names).
    How difficult would it have been to actually include an OPTIMIZER DIRECTIVE clause ?

    Comment by Gary — November 12, 2006 @ 10:20 pm GMT Nov 12,2006 | Reply

  7. For Peter K: yep, hence the word “start” in my post. The mention of “consultants and gurus” scares me because I work as a consultants. I don’t expect myself to know everything. I just expect myself to find effective answers for everything.

    Comment by Kirtan Desai — November 13, 2006 @ 2:34 am GMT Nov 13,2006 | Reply

  8. Gary, I think the purpose of subverting the comment syntax was to allow portability. You can’t add any clause to the SQL language unless you’re prepared to make people think your SQL only works because it’s different from any other version of SQL. Portability was king in the days of v7.

    NO_TRIGGER is just one of several extraordinary (and highly temporary) uses of hints. I think that one showed up only in p.q.r.s – it had gone when I tested for it in p.q.r.x. [versions edited out]

    I agree that validation should be possible – with some mechanism (session parameter for example) that allows different levels of error reporting. You can imagine the difficulties, though, if a hint stopped working on a production system because (say) someone had renamed an index. You might prefer the application to be slow, rather than to keep failing with a “hint error”- so how do you report the problem but still keep running (rhetorical question only, there are several possible answers, all with their own individual problems).

    Comment by Jonathan Lewis — November 13, 2006 @ 7:51 am GMT Nov 13,2006 | Reply

  9. Writing about hints is also good for educational purposes – since you can’t speak about them without speaking about the optimizer, and any example about a particular hint is also an example about a particular aspect (or decision tree) of the optimizer. The two are intertwined.

    So a book about hints is useful also for someone who doesn’t want to use them in real life – it’s just another way to walk inside the optimizer museum and show the exhibits contained there.

    The same thing can be probably said for “internals” in general.

    Comment by Alberto Dell'Era — November 13, 2006 @ 7:29 pm GMT Nov 13,2006 | Reply

  10. >> Telling the optimizer what not to do is a lot easier than telling it to do only what you want.

    AMEN to that !!!

    Comment by Robert — November 14, 2006 @ 4:54 am GMT Nov 14,2006 | Reply

  11. Hi Jonathan

    Well i have seen a well written hint is ignored by the oracle optimizer.

    I vaguely remeber the situation like this the query was returning zero rows and the optimizer knew it from the stats but the query developer had put in a hint to use a hash join and oracle always used a Nested Loop Join no matter what.

    regards
    Hrishy

    Comment by Hrishy — January 4, 2007 @ 9:41 am GMT Jan 4,2007 | Reply

  12. Hi Jonathan

    I can also provide a detailed 10053 trace and post it here in case you need to see exactly what was happening behind the scenes and my hint was repeatedly ignored.

    regards
    Hrishy

    Comment by Hrishy — January 4, 2007 @ 10:07 am GMT Jan 4,2007 | Reply

  13. Hrishy, Email the trace file to me, and I’ll see if I can explain what’s going on.

    My email address is jonathan@jlcomp.demon.co.uk.

    By the way, one obvious example of why the /*+ use_hash() */ hint would be “ignored” – if the join condition wasn’t an equality, a hash join is not possible, so the hint would be illegal.

    Updated 5th Jan following an email from Hrishy. My comment about the hash joins and equalities happened to be the appropriate explanation. The critical join condition in his case was a “between” clause.

    Comment by Jonathan Lewis — January 4, 2007 @ 10:27 am GMT Jan 4,2007 | Reply

  14. Hi,
    I have question regarding View and MView.

    1.Can i use Mview in OLTP application ?
    2.Does Mview is better than View ?
    I mean when Appliction do query againest View its on the fly query again query where MView is stored in db so we can take advantage of better plan. Please Explain

    Thanks,
    SP

    Comment by sp — February 14, 2007 @ 11:05 pm GMT Feb 14,2007 | Reply

  15. SP, this isn’t AskTom – I don’t answer arbitrary questions, especially when a proper answer would require a significant extract from the manuals. Here’s a quick clue, though: (1) yes, you can use materialized views in OLTP, but I’d be very careful about exactly how and wouldn’t expect to see them often. (2) materialized views are completely different from views – so cannot be compared in any sensible way. Materialized views require you to have a (potentially expensive) supporting infrastructure – which you have to pay for at some point.

    Comment by Jonathan Lewis — February 15, 2007 @ 3:43 pm GMT Feb 15,2007 | Reply

  16. […] from the function to access the entire data set and produce a result table – either with a no_merge view, or a materialized factored subquery – is a good starting step – then join this result into […]

    Pingback by Functions « Oracle Scratchpad — July 7, 2007 @ 8:41 pm GMT Jul 7,2007 | Reply

  17. […] postings on hints: Hints – again Ignoring Hints What is a hint ? Taking a hint Comments […]

    Pingback by Quiz Night « Oracle Scratchpad — December 15, 2009 @ 9:34 pm GMT Dec 15,2009 | Reply

  18. […] one of them “ignore” the use_hash() hint and do a nested loop ? Remember: the optimizer does not ignore hints (with the usual caveats about legality, spelling, syntax and […]

    Pingback by Ignoring Hints – 2 « Oracle Scratchpad — February 11, 2010 @ 7:29 pm GMT Feb 11,2010 | Reply

  19. […] Lewis stated here (a blog article from 2006) that: “It’s a great shame that Oracle Corp. decided to use the name “hints” for its […]

    Pingback by Defy Logic – the Cost-Based Optimizer does Not Select the Lowest Cost Plan – Implicit Data Type Conversion « Charles Hooper's Oracle Notes — May 28, 2010 @ 6:02 am GMT May 28,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,521 other followers