RE: oracle can ignore hints
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Mar 2004 17:07:42 -0500
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.
-- Mark --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Goulet, Dick
Sent: Thursday, March 04, 2004 4:31 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxx [mailto:ryan.gaffuri@xxxxxxx]
Sent: Thursday, March 04, 2004 1:44 PM
To: oracle-l@xxxxxxxxxxxxx
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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
