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:
- 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.