I’ve written several notes about hinting, such as:
- Hints again: do you know what they really mean.
- Ignoring hints: new DDL makes old hints “ignorable”.
- Full Hinting: the difficulty of getting it right.
- qb_name: a really important hint introduced in 10g
- push_subq: how upgrades can stop hints working.
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 producton system is when I’m sure that there is no alternative safe mechanism for making the optimizer do what I want.
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 having craft data sets and set database parameters so make a plan appear ’spontaneously’.
Always be cautious about adding hints to production systems.
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 UTC 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 UTC 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 UTC 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 UTC 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 UTC May 3,2008 |
Does this also apply to stored outlines?
Comment by Polarski Bernard — May 5, 2008 @ 6:16 am UTC 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 UTC May 5,2008 |
So…
Hinting and “unhinting” is all about invoicing!
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 UTC 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 UTC 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 UTC 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 UTC May 13,2008 |