A little while ago I wrote a note about an anomaly that someone was seeing with the index_ss() hint. Since then, they forwarded me a 10053 trace file of the question.
I’ve posted brief analysis of it on my website, just to give you an idea of the sorts of things you can look out for when you’re trying to work out why Oracle seems to be ignoring a hint.
[Further reading on “ignoring hints”]
If you go to this URL, you will find a discussion about the index_ss() hint. It’s an interesting example of how (a) you could argue that Oracle is ignoring hints until (b) you realise that we really can’t tell what’s going on because we don’t know what a hint is supposed to do.
It seems the word is gradually getting around the Oracle world that “hints” are really absolute directives to the optimizer that cannot be ignored – if they are correctly used.
Unfortunately the documentation on hints is far from complete, so it is very easy to produce examples that suggest that a hint has been ignored. Here’s one I came across recently in a note about 10g.