I agree with you Don that hints are not always warranted - yes, on a small dev system any execution plan is going to be out of whack anyway.
But avoiding them in general, I guess with performance problems hints have been the best quick fix, always. Fresh stats, sometimes. A missing index, sometimes. A correct hint 70% of the time rendered a query that screamed.
Just my .02
Lisa Koivu
Oracle Database Administrator and Terrible Perl Programmer
Fairfield Resorts, Inc.
954-935-4117
"The line ++@_[0] is a cute example of why nonprogrammers think Perl is obscure. Try showing that line to your grandmother and explaining that you write stuff like that for a living." -- Perl Black Book
-----Original Message-----
From: Don Granaman [SMTP:[EMAIL PROTECTED]]
Sent: Friday, October 05, 2001 4:36 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: perplexing plan?
From a general perspective, I have to agree with Remco.� If hints are over-used, they may be "optimal" only for a specific release of Oracle and/or a narrow range of data volumes and/or in a specific configuration.��Often, they eliminate any other tuning possibilities - create a new index and the application ignores it, the hint is great for 1000 records but is abysmal for 10,000,000, or �partition the data to improve performance and the app still can't take advantage, etc.� This is not to say that hints shouldn't be used, but other factors - like the level of your influence over the developers to change them as need arises - weigh in the determination.
�
As an example, I once spent over six months trying to get an out-sourced application changed to remove a few very harmful hints.� The�contract developers had determined, on their small test system, that forcing an access-path�sort by use of an otherwise wildly inappropriate index hint and a stopkey was more efficient than using an order by clause.� In production, the reverse was true - the hint caused each submission�of one of these statements CPU utilization�of 72x and I/O of�6200x�of what the order by did.� This was for a query that was submitted by their application 50,000 - 100,000 times a day!��Guess who was constantly called upon to "tune the database" to�fix this nightmare...
�
-Don Granaman
[OraSaurus - Honk if you remember UFI!]
�
----- Original Message -----
From: Koivu, Lisa <mailto:[EMAIL PROTECTED]>
To: Multiple recipients of list ORACLE-L <mailto:[EMAIL PROTECTED]>
Sent: Friday, October 05, 2001 1:30 PM
Subject: RE: perplexing plan?
Remco, why do you say don't use hints unless you really have to?�
Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117
