Re: oracle can ignore hints
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Mar 2004 09:34:54 -0000
Note in-line
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
March 2004 Hotsos Symposium - The Burden of Proof
Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
> ----- Original Message -----
> From: "Niall Litchfield" <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>
>
> 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: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
