Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle position on hints

RE: Oracle position on hints

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Mon, 10 Mar 2003 14:44:20 -0800
Message-ID: <F001.005656C3.20030310144420@fatcity.com> 





Connor - Congratulations



Stephane,



I'm in the camp of "don't put hints in when not necessary".  My reasons for
this include (some have been mentioned already):



    
  
  1.   You are limiting functionality when the database is upgraded - I have
seen several examples where Oracle went from 7 to 8 and noone looks at
every SQL statement to reevaluate the validity of every hint.

  
  2.   Changes in data volume or distribution can invalidate the benefits of
the hint.  The classic example would be a developer coding hints based on
anything other than a perfect copy of production data.

  
  3.   Index renaming can cause real grief.  In a test environment recently I
had to create a subset of data and in my rush / laziness to get the job
done asap I renamed the existing table (we needed the data back later on)
and created a new table and new indexes.  The hints of course then failed
and performance died until I realised the problem.






Like I said - my motto is that if it doesn't require a hint then don't add
one.  Some people wisely mentioned that there are categories of hints -
those which add a requirement (like append, first_rows and to a lesser
degree parallel) are pretty safe, whereas those which dictate execution
(like index and use_hash) are a bit more risky.  Finally, you can sometimes
use hints (like index_desc and index_asc, combined with rownum = 1) to get
a minimum or maximum value without using a sort operation - and these can
give tremendous performance gains for relatively little risk.



Regards,


     Mark.




                                                                                                                              
                    "Stephane Paquette"                                                                                       
                    <stephane.paquette_at_standa       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    rdlife.ca>                      cc:                                                                       
                    Sent by: root_at_fatcity.com       Subject:     RE: Oracle position on hints                                 
                                                                                                                              
                                                                                                                              
                    11/03/2003 08:15                                                                                          
                    Please respond to                                                                                         
                    ORACLE-L                                                                                                  
                                                                                                                              
                                                                                                                              








Congratulations !



I had never thought that my question on hints would have generated so much
trafic.



Stephane



-----Original Message-----


McDonald


Sent: Saturday, March 08, 2003 8:54 PM


To: Multiple recipients of list ORACLE-L




Just to add my meaningless diatribe to the discussion,
I think that there are "good" hints and "bad" hints.



For example, first_rows(n) is a good hint, in that you
are giving the optimizer more information as to what
your motivations are for this query, without stifling
it into a choice that may not be appropriate down the
track.



Hints such as index(tab,idx) are "bad" hints, because
you are more exposed to grief.  If the index is
renamed, then its no longer a hint.  If the data
distribution changes, or the index is rebuilt in
reverse or compressed, then it may no longer be a good
option, etc.



In terms of the vendor speak of "hints imply weak
optimizer" I think thats nonsense.  Having worked with
DB2 in the past, nothing used to be more irritating
then coming up with bizarre convolutions on an
otherwise simple SQL to ensure that certain indexes
were or were not chosen.



Cheers


Connor



PS - Its probably a little sad that I'm chit-chatting
on hints on the morning of my wedding day, but hey,
you gotta pass the time somehow whilst the other half
is getting the hair and makeup done :-)








Connor McDonald


web: http://www.oracledba.co.uk


web: http://www.oaktable.net


email: connor_mcdonald_at_yahoo.com



"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"





Do You Yahoo!?


Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: hamcdc_at_yahoo.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Paquette
  INET: stephane.paquette_at_standardlife.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Mon Mar 10 2003 - 16:44:20 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US