| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: oracle can ignore hints
The response from Oracle is very interesting if you read it carefully and
then think about how the optimizer works and how Oracle treats invalid
hints.
Also, remember that Oracle is the primary source of many of the myths about how Oracle works. I do not know how many white papers I have read that had serious errors in them.
Based on experience for versions 7 - 8 the CBO will do as it is told via a syntactically valid hint if the join order and method allow do not invalidate the hint. If you provide an index hint to use an index into table B and Oracle chooses to drive on table B then the hint is unusable. Also the CBO seems to have the ability at least in the never versions to determine that some access paths are not worth following so the CBO may never look at a path where your hint is usable.
So did the CBO "override" your hint? Or was it just unable to use it? If you provide the join order, the join method, and specific indexes to use you can usually get the plan you want, but it is getting harder. Between version 8.1 and 9.0 somewhere around 8 to 12 underbar parameters had there default changed from off to on. These parameters cause the CBO to internally rewrite many queries converting sub-queries into inline views and such. This kind of internal processing can invalidate your hint.
I have communicated with a couple of sites that ran into performance problems with certain types of queries upon upgrading from 8.1 to 9+ and setting these underbar parameters back to the version 8.1 setting seemed to be supports favorite fix.
Maybe it is a point of view issue.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Goulet, Dick
Sent: Thursday, March 04, 2004 4:31 PM
To: oracle-l_at_freelists.org
Subject: RE: oracle can ignore hints
You can take that to the BANK. I've seen several instances of just that very recently.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: ryan.gaffuri_at_cox.net [mailto:ryan.gaffuri_at_cox.net]
Sent: Thursday, March 04, 2004 1:44 PM
To: oracle-l_at_freelists.org
Subject: oracle can ignore hints
We had a discussion on here regarding hints. some of you stated that Oracle cannot ignore a proper hint. I have a TAR open and asked about this. Here is the response.
"A hint simply adds weight to a cost estimation, it can still be overridden by the CBO if the values aren't seen as useful."
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |