Re: oracle can ignore hints
- From: "Ryan" <ryan.gaffuri@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Mar 2004 19:06:05 -0500
hints are non-scalable solutions. if your data changes or the number of
records retrieved changes, your hint stays there.
----- Original Message -----
From: "STEVE OLLIG" <sollig@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, March 12, 2004 11:11 AM
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:
