6 Replies Latest reply: Nov 16, 2012 1:26 PM by Nikolay Savvinov RSS

    AWR report - Parse CPU to Parse Elapsd %: is 0.01

    user9256814
      Hi Team,

      Recently i have been asked find out performance issue the one of the database. i noticed Parse CPU to Parse Elapsd is almost close to zero. I read in many article but i have not get the clear idea. can any one help me to whether having 'zero' it is good to the database. or it has to be close to the 100. As per the AWR target should be 100%. Please help me on this.

      Instance Efficiency Percentages (Target 100%)

      Buffer Nowait %: 100.00           Redo NoWait %: 100.00
      Buffer Hit %: 94.80                In-memory Sort %: 100.00
      Library Hit %: 99.67                Soft Parse %: 99.76
      Execute to Parse %: 58.49           Latch Hit %: 99.39
      Parse CPU to Parse Elapsd %: 0.01 %      Non-Parse CPU: 97.96

      Thanks.
        • 1. Re: AWR report - Parse CPU to Parse Elapsd %: is 0.01
          Nikolay Savvinov
          Hi,

          1) it's supposed to be close to 100% (you can see that in the section header -- "target 100%")
          2) the meaning of this statistic is to tell you whether parsing time is increased because of waiting on pins, mutexes etc. Apparently, that's indeed the case for your database
          3) instance efficiency percentages, however, are often deceptive, because any ratios hide the scale (see an explanation by J. Lewis at http://jonathanlewis.wordpress.com/2006/12/27/analysing-statspack-2/). I suggest that you rather examine (or post here if having trouble interpreting):

          1) load profile section
          2) time model statistics

          Best regards,
          Nikolay
          • 2. Re: AWR report - Parse CPU to Parse Elapsd %: is 0.01
            Dom Brooks
            Don't pay too much attention to ratios, there are other sections worthy of more attention.

            I'm sure there was a bug in some versions of 11gR1 where this particular ratio was not calculated properly.
            • 3. Re: AWR report - Parse CPU to Parse Elapsd %: is 0.01
              user9256814
              Hi
              Pls find the details

                   Per Second     Per Transaction     Per Exec     Per Call
              DB Time(s):     1.5     4.9     0.01     0.01
              DB CPU(s):     0.7     2.4     0     0
              Redo size:     3,236,675.40     10,681,992.60          
              Logical reads:     11,895.70     39,259.40          
              Block changes:     4,357.70     14,381.80          
              Physical reads:     1,212.30     4,000.90          
              Physical writes:     789.6     2,605.90          
              User calls:     193.3     637.9          
              Parses:     54.3     179.1          
              Hard parses:     0.2     0.7          
              W/A MB processed:     2,958,085.40     9,762,562.60          
              Logons:     35.5     117          
              Executes:     182     600.5          
              Rollbacks:     0.2     0.6          
              Transactions:     0.3               
                                  
                                  
              Statistic Name     Time (s)     % of DB Time          
              sql execute elapsed time     4,278.21     80.25          
              DB CPU     2,574.47     48.29          
              parse time elapsed     72.37     1.36          
              PL/SQL execution elapsed time     9.05     0.17          
              hard parse elapsed time     5.96     0.11          
              connection management call elapsed time     4.97     0.09          
              sequence load elapsed time     3.98     0.07          
              hard parse (sharing criteria) elapsed time     0.33     0.01          
              hard parse (bind mismatch) elapsed time     0.31     0.01          
              repeated bind elapsed time     0.06     0          
              PL/SQL compilation elapsed time     0     0          
              DB time     5,330.98               
              background elapsed time     1,234.69               
              background cpu time     190.18               

              Thanks.
              • 4. Re: AWR report - Parse CPU to Parse Elapsd %: is 0.01
                Nikolay Savvinov
                Hi,

                so you have 0.2 hard parses per seconds with 200 user calls per seconds, and you spend 0.1% of the database time hard parsing. I.e. in your case hard parsing can be ignored as far as performance is concerned. Quod erat demonstrandum.

                Best regards,
                Nikolay
                • 5. Re: AWR report - Parse CPU to Parse Elapsd %: is 0.01
                  jgarry
                  It could be an indicator that your developers are doing it wrong. See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1594740500346667363 http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59asktom-082712.html

                  Nikolay is correct in saying it is not worth pursuing if there are other things that the database needs tuned, but he is incorrect in implying it means your developers do everything right. If you have an app where it just generates dynamic code or something, there isn't much you can do about that, but if you have developers doing it because they think it is cool or don't know any better, that should be fixed.

                  Edit: And of course, if you have the bug that Dom mentions, the ratio is even more useless than usual.

                  Edited by: jgarry on Nov 16, 2012 11:12 AM
                  • 6. Re: AWR report - Parse CPU to Parse Elapsd %: is 0.01
                    Nikolay Savvinov
                    Hi,
                    jgarry wrote:
                    It could be an indicator that your developers are doing it wrong. See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1594740500346667363 http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59asktom-082712.html

                    Nikolay is correct in saying it is not worth pursuing if there are other things that the database needs tuned, but he is incorrect in implying it means your developers do everything right.
                    I never implied anything of the kind. I simply stated that ratios can't be trusted -- and as it turns out, I was quite right about that. Hard parse rate and time are both ridiculously low and any effort to reduce would be a waste of time.
                    If you have an app where it just generates dynamic code or something, there isn't much you can do about that, but if you have developers doing it because they think it is cool or don't know any better, that should be fixed.
                    Nobody said anything about dynamic code.

                    Best regards,
                    Nikolay