RE: oracle can ignore hints
- From: "Niall Litchfield" <n-litchfield@xxxxxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 13 Mar 2004 17:06:25 +0000
Well the argument in Dan's book (as far as I understand it) is that for most
typical apps there exists an optimal or near optimal execution plan (usually
but not always driven by NL access to data via appropriate indexes) and that if
you figure out this execution plan it will stay pretty much optimum throughout
the life of the db (robustness). The book then tells you how you might figure
out this plan. In other words get the plan right early and you can be pretty
sure it will remain acceptable.
I'm busy getting infuriated by the book at the moment - it is *extremely* well
written and the premise (that there is a 'method' to determining optimum
execution plans rather than hit and miss guesswork) resonates hugely with me,
at the moment there are too many assertions for me to be entirely happy with
it, but that is probably a reflection of the fact that a) I haven't read it all
and b) stating things up front as true and leaving the proof or otherwise till
later irritates me. It also seems to me that it has OLTP in mind, but then that
is the class of apps that we run so that is fair enough.
I *do* think the book is important, I'm not yet sure how far I go along with
it.
Niall
-----Original Message-----
From: AC.GWIA.oracle-l@xxxxxxxxxxxxx [mailto:AC.GWIA.oracle-l@xxxxxxxxxxxxx]
Sent: Sat 13/03/2004 00:06
To: oracle-l@xxxxxxxxxxxxx; ryan.gaffuri@xxxxxxx
Cc:
Subject: Re: oracle can ignore hints
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
