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.
- the available hints are not properly documented
- the hints are rarely used properly – because they are not documented properly.
- (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.