This discussion is archived
3 Replies Latest reply: Nov 16, 2012 7:51 AM by Nikolay Savvinov RSS

Performance issue (slowness in app) - 11gr2

IamHariKrishna Newbie
Currently Being Moderated
OS=Microsoft Windows x86 64-bit     
CPU=2
RAM=11.99
DB=11.2.0.2.0, 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

Thanks
  • 1. Re: Performance issue (slowness in app) - 11gr2
    793965 Explorer
    Currently Being Moderated
    use bind variable in the code
  • 2. Re: Performance issue (slowness in app) - 11gr2
    Dom Brooks Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Hi,

    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
     tags.
    
    Best regards,
      Nikolay                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Legend

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