3 Replies Latest reply: Sep 25, 2013 4:18 PM by JAGORACLE RSS

    Need help to under my statspace report.

    Sheth

      I did go through several threads but didn't get answer about CPU Time in statspack report. I am seeing 70+% CPU Elapse Time.


      I generated this report when my system was running very slow.

       

      DB Name     DB IdInstance Inst Num Release Cluster Host

      ------------ ----------- ------------ -------- ----------- ------- ------------

      PROD11I   30860965 PROD11i         1 9.2.0.7.0   NO  prd01

       

                Snap Id Snap Time  Sessions Curs/Sess Comment
              --------- ------------------ -------- --------- -------------------
      Begin Snap: 17253 13-Jun-13 14:00:02  903 100.1
        End Snap: 17255 13-Jun-13 15:00:03  877 101.7
         Elapsed:           60.02 (mins)

       

      Cache Sizes (end)

      ~~~~~~~~~~~~~~~~~

                 Buffer Cache: 3,904M  Std Block Size:      8K
             Shared Pool Size: 2,304M      Log Buffer: 20,006K

       

      Load Profile

      ~~~~~~~~~~~~                        Per Second   Per Transaction
                                     ---------------   ---------------
                    Redo size:         69,004.00          9,084.65
                Logical reads:         87,609.33         11,534.12
                Block changes:            531.70             70.00
               Physical reads:          8,183.65          1,077.41
              Physical writes:            101.22             13.33
                   User calls:            395.05             52.01
                       Parses:            121.09             15.94
                  Hard parses:              0.62              0.08
                        Sorts:            171.11             22.53
                       Logons:              0.69              0.09
                     Executes:          1,466.91            193.12
                 Transactions:              7.60

       

        % Blocks changed per Read:0.61Recursive Call %: 83.02
      Rollback per transaction %:5.09   Rows per Sort:  7.58

       

      Instance Efficiency Percentages (Target 100%)

      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

              Buffer Nowait %:   99.93   Redo NoWait %:100.00
              Buffer  Hit   %:   90.72In-memory Sort %:100.00
              Library Hit   %:   99.95    Soft Parse %: 99.49
           Execute to Parse %:   91.75     Latch Hit %: 99.91
      Parse CPU to Parse Elapsd %:   86.71 % Non-Parse CPU: 99.55

       

      Shared Pool Statistics    Begin   End
                                 ------  ------
               Memory Usage %:   80.81   80.78
      % SQL with executions>1:   71.50   71.22

        % Memory for SQL w/exec>1:   72.36   73.51

       

      Top 5 Timed Events

      ~~~~~~~~~~~~~~~~~~                                                 % Total
      Event                                           WaitsTime (s) Ela Time

      -------------------------------------------- ------------ ----------- --------

      CPU time                                                   10,36171.67
      db file sequential read                     5,761,081   1,76412.20
      PL/SQL lock timer                                 185     850 5.88
      db file scattered read                      5,097,758     775 5.36
      enqueue                                                     151                332     2.30
            -------------------------------------------------------------

       

      -Hansal