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.  <FONT face=Arial size=2>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 -----
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">   <DIV
  style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From:   <A title=lisa.koivu_at_efairfield.com
  href="mailto:lisa.koivu_at_efairfield.com">Koivu, Lisa   To: <A title=ORACLE-L_at_fatcity.com
  href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L   

  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 Received on Fri Oct 05 2001 - 14:17:47 CDT