Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: oracle can ignore hints

RE: oracle can ignore hints

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Thu, 4 Mar 2004 17:07:42 -0500
Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AA2A@USAHM018.amer.corp.eds.com>


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



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

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

-----------------------------------------------------------------
----------------------------------------------------------------
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 Thu Mar 04 2004 - 17:30:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US