I’ve written a number of notes about hinting in fact, by using at the “Select Category” list to the right, I see that I have (so far) tagged 26 different articles (and this will be the 27th) with the hints tag. So I’ve decided it was time that I made clear my basic guidelines on safe hinting, as follows:
- Don’t
- If you must use hints, then assume you’ve used them incorrectly.
- On every patch or upgrade to Oracle, assume every piece of hinted SQL is going to do the wrong thing … because of (2) above. You’ve been lucky so far, but the patch/upgrade lets you discover your mistake.
- Every time you apply some DDL to an object that appears in a piece of hinted SQL assume that the hinted SQL is going to do the wrong thing … because of (2) above. You’ve been lucky so far, but the structural change lets you discover your mistake.
You will appreciate from these guidelines that I don’t really approve of using hints. The only reason that I leave them in place on a production system is when I’m sure that there is no alternative safe mechanism for making the optimizer do what I want. (And that does mean that I will use hints sometimes on a production system.)
What I use them for on test systems is to check whether a particular execution plan is actually possible, and to track down bugs in the optimizer.
Finally, for the purposes of education, I use them to demonstrate execution plans without first having to craft data sets and set database parameters to make a plan appear ‘spontaneously’.
Always be cautious about adding hints to production systems.
Oracle Scratchpad 
Thanks Jonathan, very informative. Ties in with Tom Kyte’s belief that beyond FIRST_ROWS they’re very much verboten in a production environment unless, as you say, as a last resort to coerce the recalcitrant optimizer in a specific circumstance.
Comment by SeánMacGC — May 2, 2008 @ 10:30 am BST May 2,2008 |
[…] You’ll notice the great stack of 10g hints – I have used this type of code on 8i and 9i in the past, but I’ve always put in a lot of hints to minimise the risk of Oracle doing something “clever” to spoil my plan. 10g introduces cost-based query transformation – and has far more options for unwrapping any clever tricks you introduce to SQL; so if you’re going to try messing about with “manual” optimisation you do need a lot of hints to block every new feature that might cause problems. (Highlighted following note 14 and the link to AskTom – see also “Rules for Hinting”) […]
Pingback by Manual Optimisation « Oracle Scratchpad — May 2, 2008 @ 12:08 pm BST May 2,2008 |
What about:
5. Every time you apply some DML to an object that appears in a piece of hinted SQL, assume that the hinted SQL is going to do the wrong thing…
This really means you should constantly/periodically check whether your hint is still working, and you should especially double-check when you insert/update/delete large amounts of data.
Or is DML actually safe?
Comment by Milo — May 2, 2008 @ 8:07 pm BST May 2,2008 |
Milo,
I omitted your rule 5 on the basis that it would be very difficult to impose a good timetable for checking. But in principle, every time you changed the data and updated the statistics you could find that your hints weren’t quite thorough enough.
Comment by Jonathan Lewis — May 2, 2008 @ 9:16 pm BST May 2,2008 |
Jonathan,
I don’t agree fully with 1. Some Hints may be savely used and even have to be used. APPEND, DYNAMIC_SAMPLING (when working with GTTs), PARALLEL and CURSOR_SHARING_EXACT are some of this save hints in my oppinion. Also I don’t think (and hope) Oracle will change the function of these hints in a patch or major release.
Regards
Wolfgang
Comment by Wolfgang — May 3, 2008 @ 2:58 pm BST May 3,2008 |
Wolfgang,
An incoming ping has just brought my attention back to this note, and I can’t help but point out that:
a) APPEND changes in 11.2 – it now works for some cases where it didn’t previously (viz: values())
b) Dynamic sampling has a changed algorithm – which could make the samples sufficiently different that plans change
c) Parallel has introduced some new options for determining how parallel and what to do, so plans might change and run-time characteristics might change. (direct path or cached – either way)
d) cursor_sharing_exact hasn’t changed and can be very useful – but you shouldn’t have set the cursor_sharing parameter from its default in the first place: it’s a case of two wrongs possibly making a right.
Comment by Jonathan Lewis — April 7, 2011 @ 3:27 pm BST Apr 7,2011 |
I believe that was a bug in 11.1 which has been fixed in 11.2, and now there’s separate APPEND_VALUES hint for that.
Comment by Timur Akhmadeev — April 7, 2011 @ 5:35 pm BST Apr 7,2011 |
Timur,
Thanks for the note and link.
Comment by Jonathan Lewis — April 20, 2011 @ 1:27 pm BST Apr 20,2011
Does this also apply to stored outlines?
Comment by Polarski Bernard — May 5, 2008 @ 6:16 am BST May 5,2008 |
Bernard,
I would relax rule 1 for (“Don’t)” for stored outlines – but I would still avoid using them unless there seemed to be no alternative.
On the plus side, stored outlines are generated by Oracle, so are likely to include all the hints that you should use, rather than just the two or three that seem to be sufficient.
On the minus side, (a) there are some operations that don’t get into stored outlines – which means the stored outline might stop working for no apparent reason and (b) implementation details can change on an upgrade, so the outline may behave differently.
These problems seem to be quite exotic and rare. But it does mean you have to check every query that uses a stored outline on the each patch and upgrade – which is why you should keep their use to a minimum.
Conclusion:
Better than manual hinting, but still leaves some risk of instability.
Comment by Jonathan Lewis — May 5, 2008 @ 7:24 am BST May 5,2008 |
So…
Hinting and “unhinting” is all about invoicing! :lol:
Hints are double-edge knifes.
Not only they can “harm you” when you upgrade, but if table stats change a lot, they can give you sub-optimal execution plans…or worse.
Comment by lascoltodelvenerdi — May 5, 2008 @ 1:41 pm BST May 5,2008 |
[…] not a sufficiently powerful argument to stop you from using it – it’s just a reminder that (as with all code that’s hinted) you have to document and manage the code […]
Pingback by Manual Optimisation - 2 « Oracle Scratchpad — May 9, 2008 @ 1:16 pm BST May 9,2008 |
This has to be one of the most depressing posts I’ve seen in a long time. What’s the point of having this great feature if the gurus that know Oracle best (or at least those who blog) tell you to avoid using them unless absolutely necessary?
I’ve used some very basic hints (e.g. INDEX and ROWID) with good results, but only put them into production after careful testing. And we follow your rule about reviewing their results after each Oracle upgrade (testing all code).
How am I going to show my peers that I know lots of Oracle tricks with hints??? ;-)
Groan.
Comment by Stew Stryker — May 12, 2008 @ 9:22 pm BST May 12,2008 |
[…] All it says is that you have to have repeatedly-executed functions and queries to get faster response time. It says nothing about what kind of queries or functions. It also suggests that the result cache should be used database-wide or shouldn’t be used at all (which is perfectly sound according to Jonathan Lewis’s Rules for Hinting). […]
Pingback by Oracle 11G Result Cache in the Real World — May 13, 2008 @ 7:46 pm BST May 13,2008 |
[…] Jonathan Lewis points out, it’s very difficult to do it […]
Pingback by Problem plans « OraStory — January 15, 2010 @ 1:29 pm GMT Jan 15,2010 |
[…] And hinting begets more hinting. See Rules for Hinting. […]
Pingback by “The Correct Plan” « OraStory — April 7, 2011 @ 1:03 pm BST Apr 7,2011 |
[…] On his Oracle Scratchpad, Jonathan Lewis compiles his rules for hinting. […]
Pingback by Log Buffer #96: A Carnival of the Vanities for DBAs — February 14, 2013 @ 1:19 pm GMT Feb 14,2013 |
[…] hints as the baddies here? My All Things Oracle colleague Jonathan Lewis tells us why in his Rules for Hinting (imagine Brad Pitt reading these out, if you like: The first rule of Fight Club […]
Pingback by A Beginner’s Guide to Optimizer Hints – All Things Oracle — November 25, 2013 @ 3:54 pm GMT Nov 25,2013 |
[…] https://jonathanlewis.wordpress.com/2008/05/02/rules-for-hinting/ […]
Pingback by Hint or do not hint in Oracle ? | Massimo Tinelli — November 7, 2014 @ 12:07 pm GMT Nov 7,2014 |
[…] Lewis has a great post about hints: Rules For Hinting, and he is also warning you not to use […]
Pingback by How To Pass Init.ora Parameters Through Oracle HINTS – DBA Paradise — March 23, 2017 @ 4:04 am GMT Mar 23,2017 |