3 Replies Latest reply on Nov 16, 2012 3:51 PM by Nikolay Savvinov

    Performance issue (slowness in app) - 11gr2

      OS=Microsoft Windows x86 64-bit     
      DB=, NO RAC

      One my customer application has perf issues. It has BUFFER CACHE HIT ratio as 70%. As a first step we gathered starts for indexes then later BUFFER CACHE HIT RATION increases to 99.91.

      But still they complains in slow ness. Here is my excerpts from my AWR. Any suggestion is appreciated.

      Instance Efficiency Percentages (Target 100%)

      Buffer Nowait %:     100.00     Redo NoWait %:     100.00
      Buffer Hit %:     99.91     In-memory Sort %:     100.00
      Library Hit %:     81.27     Soft Parse %:     52.88
      Execute to Parse %:     68.76     Latch Hit %:     99.92
      Parse CPU to Parse Elapsd % 92.87     % Non-Parse CPU:     60.11

      Load Profile

      Load Profile

      Per Second     Per Transaction     Per Exec     Per Call
      DB Time(s):     0.6     0.0     0.00     0.00
      DB CPU(s):     0.3     0.0     0.00     0.00
      Redo size:     71,786.4     1,907.4          
      Logical reads:     11,442.4     304.0          
      Block changes:     356.9     9.5          
      Physical reads:     2,295.5     61.0          
      Physical writes:     16.1     0.4          
      User calls:     352.4     9.4          
      Parses:     88.2     2.3          
      Hard parses:     41.6     1.1          
      W/A MB processed:     0.8     0.0          
      Logons:     0.5     0.0          
      Executes:     282.5     7.5          
      Rollbacks:     29.9     0.8          
      Transactions:     37.6               

      I see that below values are not in 90%, Anything I can do here to increase these metrics?
      Execute to Parse %:     68.76
      Soft Parse %:     52.88

        • 1. Re: Performance issue (slowness in app) - 11gr2
          use bind variable in the code
          • 2. Re: Performance issue (slowness in app) - 11gr2
            Dom Brooks
            Preserve report formatting by using the code tag.

            Buffer Cache Hit Ratio is debunked as a method of performance tuning.

            See http://jonathanlewis.wordpress.com/statspack-examples/ for AWR strategies.

            Does this AWR report correspond to a period of performance problems?

            Do you have an AWR report from when performance was ok?

            The numbers in a report depend on the timeframe.

            This isn't obviously a particular "busy" database.
            But assuming this report is relevant to the problem, relative to soft parses you're doing quite a lot of hard parses.
            Relative to logical io you're doing a high amount of physical IO.
            12 gig is not much memory but we know nothing about your database.

            See link above and examine other sections of your report.

            AWR is a database level report. SQL Trace might be a better approach for specific application functionality.
            • 3. Re: Performance issue (slowness in app) - 11gr2
              Nikolay Savvinov

              as Dom has already said, possibly the best approach is to ask your users what exactly is slow, and trace it. Having said that, a couple of things don't look right in your AWR:

              1) 41 hard parse per second -- that's a lot for a system with only 2 CPUs (considering that you only have 282 executes per seconds). You should look at DB and OS CPU metrics, and pay close attention to library cache contention events
              2) you are doing 29.9 rollbacks per second (!). Actually, the majority of your transactions are rollbacks (!!). Do you have any reasonable explanation for this? Rollbacks are very expensive performancewise and should be used sparingly.

              For a more complete analysis, please post top timed events and time model statistics sections. Be sure to preserve formatting using
              Best regards,