2 Replies Latest reply on Oct 9, 2012 4:17 AM by Nikolay Savvinov

    AWR Report - High wait and contentions

      I am getting high wait and high contention on DB. but i cannot really know where the problem is coming from. I need a help in analyzing this AWR.

      Instance Efficiency Percentages (Target 100%)
      Buffer Nowait %: 99.69 Redo NoWait %: 100.00
      Buffer Hit %: 96.62 In-memory Sort %: 100.00
      Library Hit %: 100.03 Soft Parse %: 99.99
      Execute to Parse %: 0.24 Latch Hit %: 99.76
      Parse CPU to Parse Elapsd %: 0.12 % Non-Parse CPU: 99.90

      Shared Pool Statistics Begin End
      ------ ------
      Memory Usage %: 78.81 78.84
      % SQL with executions>1: 89.29 97.32
      % Memory for SQL w/exec>1: 87.49 89.84

      Top 5 Timed Events Avg %Total
      ~~~~~~~~~~~~~~~~~~ wait Call
      Event Waits Time (s) (ms) Time Wait Class
      ------------------------------ ------------ ----------- ------ ------ ----------
      enq: TX - row lock contention 33,827 14,163 419 30.4 Applicatio
      gc buffer busy 20,921 5,318 254 11.4 Cluster
      enq: US - contention 10,873 5,252 483 11.3 Other
      enq: TX - index contention 8,845 4,248 480 9.1 Concurrenc
      gc current block busy 4,399 3,710 843 8.0 Cluster
      ^LRAC Statistics DB/Inst: NG1PRTP/NG1PRTP1 Snaps: 12992-12993

      Begin End
      ----- -----
      Number of Instances: 2 2

        • 1. Re: AWR Report - High wait and contentions
          Welcome to the forum!

          Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
          I am getting high wait and high contention on DB
          What high wait and contention are you referring to in what you posted?

          The main thing I noticed was this:
          Execute to Parse %: 0.24
          That is extremely low. What period of time was the AWR for?

          Do you know what what going on in the DB at that time?

          See this link for a good explanation about execute to parse.
          • 2. Re: AWR Report - High wait and contentions
            Nikolay Savvinov

            welcome to the forum!

            a few pieces of advice:

            1) when posting excerpts from an AWR report, please don't leave out the header (version, platform, RAC/standalone etc.) and load profile sections
            2) when evaluating a problem, it's important to have some frame of reference. So this database, is it always behaving like this? Or since a certain date? Since a certain change? Is it possible to obtain an AWR report for a healthy period?
            3) your top wait event indicates an application problem -- apparently, parallel threads try to modify same data at the same time, and Oracle row-level locking is correctly preventing that. You need to find the top DML statements that lock each other and see why the locking is so bad. Most likely, it's an application design issue

            Best regards,