| 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
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
-- 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 - 10:15:13 CST
 |
 |