RE: oracle can ignore hints

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 05 Mar 2004 09:14:15 +0000
Message-Id: <s0484504.075@bristol21.bristol.ac>

I think that I have followed all of the thread so far, apologies if I am repeating stuff.

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)

In every case that I have had where a valid hint was being 'ignored' one of two things was going on
-- the optimizer was indeed considering the hint (for example use index x to access table y) but the resulting cost was lower than an alternative plan that it came up with. My most common mistake is omitting ordered as well as the access method hint, in most cases here Oracle will just switch the join order and irritate me.
- the sql that was being run was not the sql I had submitted (query rewrite is a wonderful thing).
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.

The tool I used to come to my conclusion 1 above was primarily 10053 trace so it would be interesting to see if one of those with misbehaving CBO could repeat the 10053 trace process on their query with hints being ignored. (of course reading a 10053 trace for a 7 table join is what you might term non-trivial).

There is also a terminology issue, I briefly talked with Tom Kyte - who also states that the CBO can ignore hints - at UKOUG2003 and what he seemed to have in mind was the case where the hint was invalid or syntatically incorrect (hinting a non-existing index for example) but oracle doesn't throw an error.

Now my mental picture is

Sqlstatement parsed - hints evaluated for 'correctness' here Parse statement including restrictions imposed by hints passed to CBO for plan generation

Which seems to describe in word pictures the behaviour which I have observed. I know this is simplified and likely is wrong.

It also seems to me that it is possible if it is the CBO itself that is throwing out invalid hints to describe this as 'ignoring' hints, I'm not sure that this would be helpful. Ignoring to me describes paying no attention to, not examining and rejecting. I also can't see the CBO itself evaluating the hints because of the RULE special case.

Incidentally running a 10053 trace when a hint is being used doesn't show all the access methods that are in a non-hinted statement with the desired one with a low cost, but just the hinted access path. So ISTM that the info in the TAR regarding costs being changed is demonstrably false.

I also like the argument from the hints in Oracle delivered code - putting those hints in because 'most of the time' they will be obeyed would eventually bite support.

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 - 03:12:30 CST