RE: oracle can ignore hints
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Mar 2004 15:16:52 -0600
For what it's worth, the opinions I presently hold on the matter of
hints include:
1. Hints are directives, but understanding why they're directives (and
not merely suggestions) is tricky. See Jonathan's and Connor's work for
examples.
2. Hints are wonderful for performance analysts to have, because they
allow us to specify execution plans--both great and horrible--so we can
see how Oracle acts without having to resort to subtle tricks like we
did in the past.
3. I agree with Tom Kyte: For production situations, there are good
hints and there are bad hints. Good hints are the ones that give the
optimizer information that it cannot discover without your help. Good
hints include (ALL|FIRST)_ROWS(n), (NO)?REWRITE, CARDINALITY, etc. Bad
hints are the ones that prevent the optimizer from doing something that
is, or might later become, a good idea. Bad hints include ORDERED,
USE_NL, INDEX, etc.
4. The RULE hint is a nice way to buy yourself some time, but it is not
a permanent solution. If using RULE makes a query faster, then it's very
likely that you've accidentally lied to your CBO (old statistics,
neglect of capturing skew information in histograms, etc.). If you use
RULE hints, then you should invest some time to determine where you've
gone wrong with using the CBO, for two reasons: (1) RULE eventually goes
away, and (2) for *anything* that RBO can do, CBO can do either that
same thing or a much better thing.
5. If you devote more time to making good decisions about how you store
your data (smart use of indexes, IOTs, hash clusters, etc.), how you
describe your data (PK/FK declarations, check constraints, schema
statistics, histograms, etc.), and how you access your data (use of
transitivity predicates, elimination of implicit coercions, etc.) then
you'll find yourself needing hints far less often.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle, 5/7 Dallas
- SQL Optimization 101: 3/29 Dallas, 4/19 Denver, 5/3 Boston, 5/24 San
Diego
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of STEVE OLLIG
Sent: Friday, March 12, 2004 10:11 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: oracle can ignore hints
agreed. first be sure to care for and feed the CBO. it can save you a
lot
of work.
but when you've done that and you still have a query that needs help,
then
hints may not be such a bad thing. it was my thinking that hints were
bad -
didn't mean to imply it was yours. i was trying to say that Dan's book
got
me to soften that view. and that, i think, is a good thing.
and on whether a robust plan is still efficient in 3 years, 5 years,
whatever - Dan's book contends probably. i'm inclined to agree.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Niall Litchfield
Sent: Friday, March 12, 2004 9:54 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: oracle can ignore hints
Hi Steve
> 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.
I didn't necessarily mean to imply that hints were always bad and should
be
avoided, I was more hoping to suggest that hints had a downside that
isn't
always immediately apparent. Hints are great for getting slow running
queries to work fast enough again, quickly. They have the downside
though
that upgrades or data changes may break the 'fix'.
> 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?
Will it still be efficient in 3 years time after a
merger/demerger/change of
legislation/software upgrade/hardware upgrade etc etc? because 9 times
out
of 10 the hint gets forgotten about.
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 //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:
