2 Replies Latest reply on Nov 6, 2012 8:12 AM by Osama_Mustafa

    When query is taking too long time

    udayjampani
      When query is taking too long time,Where and how to start tuning it?

      Here i've listed few things need to be considered,out of my knowledge and understanding
      1.What the sql is waiting for(wait events)
      2.Parameter modification need to be done at system/session level
      3.The query has to be tuned (using hints )
      4.Gathering/deleting statistics

      List out any other things that need to be taken into account?

      Which approach must be followed and on what basis that approach must be considered?
        • 1. Re: When query is taking too long time
          Sunny kichloo
          This will help

          http://xanpires.wordpress.com/2012/01/14/oracle-sql-tuning-tune-individual-sql-statements/
          • 2. Re: When query is taking too long time
            Osama_Mustafa
            When query is taking too long time,Where and how to start tuning it?
            explain plan will be good start . trace also
            Here i've listed few things need to be considered,out of my knowledge and understanding
            1.What the sql is waiting for(wait events)
            When Oracle executes an SQL statement, it is not constantly executing. Sometimes it has to wait for a specific event to happen befor it can proceed.

            Read
            http://www.adp-gmbh.ch/ora/tuning/event.html
            2.Parameter modification need to be done at system/session level
            Depend on parameter , define parameter , trace done on session level for example
            3.The query has to be tuned (using hints )
            Could be help you but you must know how to use .
            4.Gathering/deleting statistics
            Do it in non working hours , it will impact on database performance , but its good
            List out any other things that need to be taken into account?
            Which account ?
            Which approach must be followed and on what basis that approach must be considered?
            you could use lot of tools , Trace , AWR