Re: oracle can ignore hints
- From: "Ryan" <ryan.gaffuri@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Mar 2004 18:59:09 -0500
what are
"(use of
transitivity predicates, elimination of implicit coercions, etc.) then
you'll find yourself needing hints far less often."
----- Original Message -----
From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, March 12, 2004 4:16 PM
Subject: RE: oracle can ignore hints
> 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
> -----------------------------------------------------------------
----------------------------------------------------------------
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:
