RE: oracle can ignore hints
- From: "Niall Litchfield" <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 05 Mar 2004 09:14:15 +0000
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)
1. 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).
2. 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@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:
