This discussion is archived
2 Replies Latest reply: Oct 3, 2013 10:41 AM by Hoek RSS

Performance tuning - Hints

926318 Newbie
Currently Being Moderated

Hi Experts,

I am new to oracle database  developer. currently i am leraning the performance tuning.

I have a question that is it a good practice to use hint as having using oracle database version 10g.

some where i have read and also some Senior members from suggest me that before 9i the optimizer was rule based , hence there was nesecarry to use hint.

after 9i onwards the optimeser is cost based hence there is no need to provide the hints.

but i have seen some queries from the expert where they have used hints.(  even in db version 10g).

so can you please tell me that if we wants to use hints then when to use them>\

 

 

thanks in advance

 

Prashant Mhatre

  • 1. Re: Performance tuning - Hints
    SomeoneElse Guru
    Currently Being Moderated

    > Senior members from suggest me that before 9i the optimizer was rule based

     

    No, the CBO was introduced in Oracle 7.

     

    > so can you please tell me that if we wants to use hints then when to use them>

     

    Hints can be handy during development for tuning and tweaking (but not twerking).

     

    In production code, hints can be poison.  Some of the worst performance problems I've seen were *caused* by inappropriate hints.

     

    Keep in mind I'm talking about "tuning hints".  There are some other hints (like insert /*+ append */) that fall into a totally different category.

  • 2. Re: Performance tuning - Hints
    Hoek Guru
    Currently Being Moderated

    Hints are a path of last resort.

    I suggest you do some searches on http://asktom.oracle.com and you'll get tons of examples and explanations regarding  performance tuning, as well as hints.

    Also, you should read the Oracle Performance Tuning Guide, it comes in 2 flavors:

    2-day Guide: Contents

    Full Guide: Contents

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points