FreeLists / oracle-l / RE: oracle can ignore hints

["Niall Litchfield" <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>]

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------