2 Replies Latest reply: Oct 3, 2013 12:41 PM by Hoek RSS

    Performance tuning - Hints

    926318

      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

          > 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

            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