Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: oracle can ignore hints

Re: oracle can ignore hints

From: Jonathan Lewis <>
Date: Fri, 5 Mar 2004 09:34:54 -0000
Message-ID: <00b701c40295$2329a150$7002a8c0@Primary>

Note in-line


Jonathan Lewis

The Co-operative Oracle Users' FAQ

March 2004 Hotsos Symposium - The Burden of Proof   Dynamic Sampling - an investigation
March 2004 Charlotte OUG ( CBO Tutorial April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

> ----- Original Message -----
> From: "Niall Litchfield" <>
> I too am of the view that 'hint' is a misleading word and that 'directive'
or 'instruction' would be better.
> I'm of this view for the following reasons, which I think are highly
suggestive but not complete evidence
> (I'm not sure how you would prove this in any case - though disproving it
ought to be possible)

That's the biggest problem with the 'hints'/'directives' issue. It is not possible to PROVE by observation that a hint is a directive - no matter how convincing the argument is (short of the original coder actually saying - "that was the code I was attempting to write, according to the specification") it can never be a proof because someone will come up with an observation where they think Oracle 'ignored the hint'.

When you supply them with the correct set of hints, that doesn't prove that Oracle didn't ignore the original hint; and anyway, there's always another person in the queue with another example where Oracle 'ignored the hint'.

> There is the interesting case of plan stability. This appears to consist
essentially of attaching
> a heavily hinted sql statement to a hash value and then passing that
statement to the cbo.
> If the CBO could ignore the hints passed to it then it would appear that
plan stability might
> not be guaranteed. I've heard of no suggestion that plan stability can
'fail' unexpectedly.

There are some funny special cases - typically version dependent, and the cases I've found have been related to undocumented features. For example, I have one case where an UNNEST hint in 8.1.x (x was 6 or 7) produced a suitable plan. When I trapped the execution plan as an outline, and tried the same SQL with the outline hints (not using the plan) the required plan would not appear.

The problem was that an outline also has a join order built in that isn't reported in the user_outline_hints view, you can't introduce the order as a hint anyway until version 10. So in this case, you appear to have a set of hints that Oracle can ignore - but that's because you've derived those hints from an option that was (at the time) a non-costed option, and you haven't been able to use all the required hinting information.

That rambles a bit - but I can't manage to summon up the enthusiasm to rewrite it. Sorry.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Fri Mar 05 2004 - 03:32:00 CST

Original text of this message