Oracle Scratchpad

February 21, 2007

Ignoring Hints

Filed under: Hints,Ignoring Hints,Oracle — Jonathan Lewis @ 9:04 pm BST Feb 21,2007

Here’s a whimsical, but very telling, example of Oracle “ignoring” hints.

I have the following query, which includes a hint to use a specific index when visiting a certain table. It’s the primary key index, so has no issues relating to null values making the hint invalid – yet Oracle does not use this index. Has it ignored the hint ?


January 18, 2007

Using 10053

Filed under: CBO,Execution plans,Hints,Ignoring Hints,trace files,Troubleshooting — Jonathan Lewis @ 8:51 pm BST Jan 18,2007

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”]

January 3, 2007

Hints and Guesses

Filed under: Hints,Ignoring Hints,Indexing — Jonathan Lewis @ 8:45 pm BST Jan 3,2007

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.


October 28, 2006

Taking a Hint

Filed under: CBO,Execution plans,Hints,Ignoring Hints,Indexing — Jonathan Lewis @ 10:32 pm BST Oct 28,2006

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.


« Previous Page

Powered by