3 Replies Latest reply: Mar 7, 2013 11:32 AM by Yoav RSS

    Performance issue due to CPU bottleneck.

    Yoav
      Hi,
      Version 10204.
      End users are compliend about poor performance of some of our daily jobs.

      I have run addm and awr report and adviced that the performance is beacuse of CPU bottleneck .


      Addm shows :
      ============
      FINDING 1: 77% impact (11626 seconds)
      -------------------------------------
      Host CPU was a bottleneck and the instance was consuming 17% of the host CPU.
      All wait times will be inflated by wait for CPU.
      
         RECOMMENDATION 1: Host Configuration, 67% benefit (10222 seconds)
            ACTION: Consider adding more CPUs to the host or adding instances serving the database on other hosts.
         ...
            RECOMMENDATION 2: SQL Tuning, 9.3% benefit (1415 seconds)
         ..
            RATIONALE: SQL statement with SQL_ID "bys8bks9azc46" was executed 516616
                  times and had an average elapsed time of 0.0028 seconds.
            RATIONALE: Average CPU used per execution was 0.0018 seconds.
         ....
         ADDITIONAL INFORMATION:  Host CPU consumption was 94%.
      AWR shows :
      ============
         Elapsed:               60.32 (mins)
         DB Time:              252.61 (mins)
      
      Cache Sizes
      ~~~~~~~~~~~                       Begin        End
                                   ---------- ----------
                     Buffer Cache:    16,496M    16,496M  Std Block Size:         4K
                 Shared Pool Size:     1,824M     1,824M      Log Buffer:    14,340K
      
      Load Profile
      ~~~~~~~~~~~~                            Per Second       Per Transaction
                                         ---------------       ---------------
                        Redo size:          1,644,501.64            879,327.35
                       User calls:              5,103.88              2,729.08
                           Parses:                595.08                318.19
                      Hard parses:                  0.12                  0.06
                            Sorts:                574.83                307.37
                           Logons:                  0.27                  0.15
                         Executes:              3,563.43              1,905.39
                     Transactions:                  1.87
      
        % Blocks changed per Read:   26.58    Recursive Call %:    18.22
       Rollback per transaction %:    0.34       Rows per Sort:     5.77
      
      Instance Efficiency Percentages (Target 100%)
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                  Buffer Nowait %:   99.96       Redo NoWait %:  100.00
                  Buffer  Hit   %:   98.62    In-memory Sort %:  100.00
                  Library Hit   %:  100.01        Soft Parse %:   99.98
               Execute to Parse %:   83.30         Latch Hit %:   99.97
      Parse CPU to Parse Elapsd %:   55.60     % Non-Parse CPU:   98.98
      
      
      Top 5 Timed Events                                         Avg %Total
      ~~~~~~~~~~~~~~~~~~                                        wait   Call
      Event                                 Waits    Time (s)   (ms)   Time Wait Class
      ------------------------------ ------------ ----------- ------ ------ ----------
      db file sequential read           1,343,144       9,696      7   64.0   User I/O
      CPU time                                          4,935          32.6
      
      Operating System Statistics 
      
      Statistic                                       Total
      -------------------------------- --------------------
      AVG_BUSY_TIME                                 340,619
      AVG_IDLE_TIME                                  21,003
      AVG_IOWAIT_TIME                                 8,422
      AVG_SYS_TIME                                  216,556
      AVG_USER_TIME                                 123,968
      BUSY_TIME                                   2,725,866
      IDLE_TIME                                     168,918
      IOWAIT_TIME                                    68,261
      SYS_TIME                                    1,733,286
      USER_TIME                                     992,580
      LOAD                                                3
      OS_CPU_WAIT_TIME                    6,648,289,867,500   <<<<--------
      RSRC_MGR_CPU_WAIT_TIME                              0
      VM_IN_BYTES                                61,071,360
      VM_OUT_BYTES                                        0
      PHYSICAL_MEMORY_BYTES                  68,649,152,512
      NUM_CPUS                                            8
      NUM_CPU_SOCKETS                                     8
      ....
      TOP SQL ordered by CPU Time is as follow:
      As you can see bellow and as was suggested by addm the statments are running very fast and there is no place for tunning the statment themself.
          CPU      Elapsed                  CPU per  % Total
        Time (s)   Time (s)  Executions     Exec (s) DB Time    SQL Id
      ---------- ---------- ------------ ----------- ------- -------------
             974      1,452      516,616        0.00     9.6 bys8bks9azc46
             806        848      614,295        0.00     5.6 27k2t7quuvfgw
             430        687      646,527        0.00     4.5 4vzwkqwcf57g9
             284      1,578      516,638        0.00    10.4 1dzphp4huys5c
             227      1,262      149,889        0.00     8.3 a41jpq2za18t1
      Also , SQL ordered by Parse Calls shows high volume of parsing (mainly soft pars)
      -> Total Parse Calls:       2,153,838
      
                                  % Total
       Parse Calls  Executions     Parses    SQL Id
      ------------ ------------ --------- -------------
           140,309      140,310      6.51 6wz3unjt67675
            90,425       90,425      4.20 27axdr2bax09z
            90,425       90,425      4.20 41rjjukjazn79
            90,425       90,425      4.20 4f746wudj51br
            90,423       90,423      4.20 0cqfxvkv3x8ca
            90,423       90,423      4.20 112k25pgmqtxx
      ....
      But it seems that in total activity it does not effect performance alot.
      Statistic Name                                       Time (s) % of DB Time
      ------------------------------------------ ------------------ ------------
      sql execute elapsed time                             14,557.7         96.0
      DB CPU                                                4,934.6         32.6
      PL/SQL execution elapsed time                           175.7          1.2
      parse time elapsed                                       77.3           .5
      connection management call elapsed time                  58.2           .4
      inbound PL/SQL rpc elapsed time                          36.3           .2
      sequence load elapsed time                               33.4           .2
      RMAN cpu time (backup/restore)                            4.7           .0
      hard parse elapsed time                                   3.0           .0
      hard parse (sharing criteria) elapsed time                1.1           .0
      PL/SQL compilation elapsed time                           0.3           .0
      repeated bind elapsed time                                0.1           .0
      failed parse elapsed time                                 0.1           .0
      hard parse (bind mismatch) elapsed time                   0.0           .0
      DB time                                              15,156.5          N/A
      background elapsed time                               5,638.8          N/A
      background cpu time                                     169.2          N/A
      Any suggestion what should be done as a DBA (beside adding more cpus ...)

      Thanks
        • 1. Re: Performance issue due to CPU bottleneck.
          JustinCave
          First off, the value being reported for OS_CPU_WAIT_TIME cannot possibly be accurate. That's hundredths of a second so converting it to centuries, you get roughly 21 centuries
          SQL> ed
          Wrote file afiedt.buf
          
            1  select 6648289867500/100/60/60/24/365.25/100 centuries
            2*   from dual
          SQL> /
          
           CENTURIES
          ----------
          21.0671593
          Unless you've been experimenting with time travel, it seems far more likely that some counter rolled over and the value is meaningless.

          If you use operating system tools to look at CPU consumption, what do you see? Are there other things running on the host than this database? If so, those other processes could certainly be potentially eating up some or all of the available resources on the server.

          Based on the AWR report, I would tend to focus on tuning the SQL statements that are doing a lot of I/O since sequential reads account for 64% of the database time.

          Justin
          • 2. Re: Performance issue due to CPU bottleneck.
            Jonathan Lewis
            Your host CPU usage is much higher than your database CPU usage - what else is running on the machine, because it's killing it.

            Your disk I/O times could be better, and you seem to be doing more random I/O in Oracle than one might deem reasonable for the size of machine you've got (especially if other applications are hammering it at the same time).

            The number of executions per second is much higher than the number of user calls - suggesting that you have embedded lots of lightweight SQL statements inside pl/sql (either as functions called from SQL, or as wrappers to sequences of SQL statement). It is possible that every SQL statement on your system is very efficieint but the coding strategy is wrong.

            I wouldn't be inclined to go for more, or faster, CPUs until I had found out who was using the 80% that you're not using. I'd also look for options for reducing I/O because it's a little slow at present, and I'd look for the cause of the high execution count and aim to reduce that.

            Regards
            Jonathan Lewis
            • 3. Re: Performance issue due to CPU bottleneck.
              Yoav
              Hi,
              You was right about : OS_CPU_WAIT_TIME
              I have check it with support and they have update that there is a known bug on this issue.
              Bug 7447648 - HPUX: OS_CPU_WAIT_TIME value from V$OSSTAT is incorrect on HPUX
              Thanks
              Yoav