Rules for Hinting
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.
