| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: oracle can ignore hints
Oracle can perform transitive closure in some instances, but not others.
If I recall correctly, if you specify "where a=20 and a=b", then Oracle
can figure out that the predicate "and b=20" must also be true. But I
think it cannot figure out that if "a=b and b=c", then "a=c". You have
to specify the "a=c" in order for Oracle to use "a=c" as a join
criterion. If beginning a multi-way join with "a=c" is a great idea, but
you specify only "a=b and b=c", then you're giving CBO less than its
best chance to find the optimal execution plan.
The implicit coercion thing is the old "load_no=10159" thing we talked about on the list a few weeks ago, where in our example, load_no was a varchar2. Oracle will implicitly coerce the varchar2 column reference to a number to do the comparison. So "load_no=10159" becomes "to_number(load_no)=10159", which means that Oracle will not use an index on load_no to perform the predicate calculation.
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
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ryan
Sent: Friday, March 12, 2004 5:59 PM
To: oracle-l_at_freelists.org
Subject: Re: oracle can ignore hints
what are
"(use of
transitivity predicates, elimination of implicit coercions, etc.) then
you'll find yourself needing hints far less often."
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of STEVE OLLIG
> Sent: Friday, March 12, 2004 10:11 AM
> To: 'oracle-l_at_freelists.org'
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Niall Litchfield
> Sent: Friday, March 12, 2004 9:54 AM
> To: oracle-l_at_freelists.org
> 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_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
> -----------------------------------------------------------------
-- 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 Fri Mar 12 2004 - 21:21:11 CST
 |
 |