RE: oracle can ignore hints

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 05 Mar 2004 10:35:53 +0000
Message-Id: <s048581a.063@bristol21.bristol.ac>

> > (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'.

I agree, what I was getting at was that disproving it ought to be relatively easy given the number of folks who have hints that Oracle ignores. I would have thought that the burden of proof was on those who suggest that oracle ignores hints or , as with the tar, that it adjusts the costs that the CBO uses. On the other hand given that there are a number of credible reliable sources out there that do suggest that Oracle can ignore hints then I feel somewhat circumspect about claiming the contrary with only 'a good case' rather than clear evidence.

> 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'.

True, but as with the theory of general relativity as the number of observations in accordance with the theory grows the weight one can attach to the theory increases.

>
> > 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.

Not rambling at all, at least not by my standards :(. It wouldn't entirely surprise me if stored outlines and new features were a bit flaky, but this is the first time I have actually heard that.

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Mar 05 2004 - 04:33:46 CST