RE: oracle can ignore hints

Hi Steve,

The reason that you want to be careful using hints, is that when you upgrade
your database, that hint may no longer be the most efficient way to access
the data.

So if someone has been very liberal about using hints in application code,
you may find that portions of the code get noticeably slower after the
upgrade.

Hints should usually be a last resort if you can't get optimal execution
from the CBO and changing the table structures is not an option.

-----------------------------------------
Alan Davey
Senior Analyst/Project Leader
Oracle 9i OCA; 3/4 OCP
w) 973.267.5990 x458
w) 212.295.3458



-----Original Message-----
From: STEVE OLLIG [mailto:sollig@xxxxxxxxxxxxx]
Sent: Friday, March 12, 2004 10:07 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: oracle can ignore hints


Sorry for chiming in late on this thread, but Lex's post caught my attention
and then something in Niall's post prompted this.  I've been reading Dan
Tow's new SQL Tuning book.  In it he addresses the attitude Niall's
describing with this statement:

"... bright people often look down on hints or treat them as a quick fix/
workaround. If you view every hint you give as handicapping Oracle in some
way you tend to avoid sprinkling them liberally throughout your code."

I have to admit that I recognized myself when I read that (being bright and
all).  But Dan's book gave me a very different perspective.  Hints may not
be such a bad thing that should be avoided at all costs.  Granted, we need
to be smart about the usual care and feeding of the CBO so it can tune the
vast majority of the queries that run in our databases.  I know I don't have
time to manually tune all the queries I write (not to mention other
duhveloper's queries that need far more help).  So I let the CBO do most of
the work for me.  And a well cared for CBO can do a pretty darn good job.
But when I do manually tune a query, why not hint away at the exact robust
plan I know works efficiently?  Anyone?

I'm not through the entire book, but I have already applied several of Dan's
ideas with great results.  Thanks Dan!  

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Lex de Haan
Sent: Friday, March 12, 2004 5:45 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: oracle can ignore hints


excellent explanation of Oracle hints. the only exception I know of is the
STAR TRANSFORMATION hint; there are some built-in heuristics that must be
met to open up that join optimization -- but even there you could argue that
if the method is not even considered in the first place, the hint will not
change that behavior; the CBO will only do so when the start transformation
method is initially considered but rejected.

cheers,

Lex.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Niall Litchfield
Sent: vrijdag 12 maart 2004 12:06
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: oracle can ignore hints


Comments embedded - catching up on 400 messages after 2 days away :(
> -----Original Message-----
> From: Jared.Still@xxxxxxxxxxx
> Sent: 10 March 2004 13:22
> To: Jared.Still@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: Re: oracle can ignore hints
>
>
> Correct, so did Oracle 'ignore' the hint?
>
> I guess the point is this:  does Oracle ignore your hints,
> or are they merely unusable?

I don't think that the experiment goes quite far enough.

I have repeated it with the following modification

Run the statement without the hint and c1 is null
Run the statement with the hint and c1 is null.

Next

Run the statement without the hint and c1 is not null
Run the statement with the hint and c1 is not null.

I.E. First see what oracle does on its own, then see what adding the hint
does.

Output each of these to a separate 10053 trace file and compare.

In the null case Oracle *never* considers the index (as you should expect).
In the not null case Oracle considers the index and rejects it without the
hint in favour of the FTS, with the hint it only considers the index as an
access path into t.

In other words I think this demonstrates quite well that hints (at least
access path hints) don't ADD new access paths for consideration, but
restrict the available access paths. Thus hinting an index that cannot be
used* and so will never be considered is not (IMO) a case of "ignoring" the
hint, but a case of supplying an invalid hint (just as if I'd supplied
select /*+ index (idx t) */ to the query). In general I find this way of
thinking about hints (as restricting the available choices for the
optimizer) as quite helpful. It enables you when the CBO is 'ignoring' your
hint to ask questions like - is the access path that I want actually
available, have I restricted the optimizer's choices enough - as well as
giving a hint (sorry for the pun) as to why bright people often look down on
hints or treat them as a quick fix/ workaround. If you view every hint you
give as handicapping Oracle in some way you tend to avoid sprinkling them
liberally thorughout your code.

> You could also try this with a bitmapped index.
>
> Jared

And actually there is a prediction that can be made from the above for this
case. Bitmapped indexes index nulls, therefore the bitmapped index will be
available to the optimizer. Thus I predict that the bitmapped index will be
*considered* without the hint (i.e it will show in a 10053 trace), and will
be *chosen* with the hint.

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805

* NB it is perfectly possible to hint Oracle to use an index that is marked
'UNUSABLE' I think of this as a special case especially as the unhinted
optimizer will also choose an 'UNUSABLE' index. I regard this as a bug -  to
me Oracle knows the index is unusable so it should not consider it at all.
You can look at it as a feature (reducing invalidations of execution plans
when tables move/indexes are set unusable).



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

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


"This information in this e-mail is intended solely for the addressee and
may contain information which is confidential or privileged.  Access to this
e-mail by anyone else is unauthorized.  If you are not the intended
recipient, or believe that you have received this communication in error,
please do not print, copy, retransmit, disseminate, or otherwise use the
information. Also, please notify the sender that you have received this
e-mail in error, and delete the copy you received."


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

Other related posts: