1 2 Previous Next 18 Replies Latest reply: Jun 28, 2009 7:42 PM by 655554 RSS

    how to troubleshooting high sys cpu usage ???

    655554
      how to troubleshooting high sys cpu usage ???
        • 1. Re: how to troubleshooting high sys cpu usage ???
          amardeep.sidhu
          Little more details won't hurt i guess ;) . Things like OS, version and other details.
          • 2. Re: how to troubleshooting high sys cpu usage ???
            655554
            Thanks for your reply,I just want to get some idea about how to diagnose high sys cpu usage.
            • 3. Re: how to troubleshooting high sys cpu usage ???
              Oded Raz
              The best thing is to run AWR / Perfstat to see what is going on.

              You can allsow do :

              First Identifiy the process number that consunes the most CPU (using TOP in linux or task manager in windows).

              Then Run the following query againt the database to identifiy the SQL that the process is running, that way you can Identifiy the SQL that consuming the most CPU is your system:

              select sql_fulltext from v$session s,v$sql q, v$process p
              where s.paddr=p.addr and
              s.sql_id=q.sql_id and
              p.spid=<Insert process id from top>


              Oded
              [www.dbsnaps.com]
              [www.orbiumsoftware.com]
              • 4. Re: how to troubleshooting high sys cpu usage ???
                655554
                Thanks for your reply !
                I don't say how to troubleshooting high user cpu,I don't think high sys cpu is related to sql which consume high buffer gets
                • 5. Re: how to troubleshooting high sys cpu usage ???
                  Oded Raz
                  O.K, first send us the processes who consumes the most CPU, and we will take it from there
                  • 6. Re: how to troubleshooting high sys cpu usage ???
                    Tanel Poder
                    High SYS mode CPU usage troubleshooting is highly OS specific.

                    So let us know exactly on which version of OS and platform you are and also knowing what's the exact version of Oracle wouldn't hurt.

                    Also, let us know what exactly do you mean by HIGH. Like is it 5% or 95% of total CPU capacity.

                    --
                    Tanel Poder
                    http://blog.tanelpoder.com
                    • 7. Re: how to troubleshooting high sys cpu usage ???
                      655554
                      os: aix 5.3
                      sys% : 40%
                      oracle : 10.2.0.4.0

                      The system has only one double-core cpu ,the runable process about 40,there is no page in and page out.

                      before tuning :
                           Snap Id     Snap Time     Sessions     Cursors/Session
                      Begin Snap:     1224     14-May-09 14:52:21     117     2.4
                      End Snap:     1225     14-May-09 14:55:18     117     1.9
                      Elapsed:           2.96 (mins)            
                      DB Time:           36.20 (mins)            
                      
                      
                           Per Second     Per Transaction
                      Redo size:     9,393.74     128,529.54
                      Logical reads:     24,445.00     334,467.77
                      Block changes:     32.41     443.38
                      Physical reads:     20.30     277.69
                      Physical writes:     0.30     4.15
                      User calls:     6,592.79     90,205.54
                      Parses:     1,242.47     17,000.00
                      Hard parses:     0.13     1.77
                      Sorts:     125.10     1,711.62
                      Logons:     0.02     0.23
                      Executes:     1,244.06     17,021.85
                      Transactions:     0.07      
                      
                      Event     Waits     Time(s)     Avg Wait(ms)     % Total Call Time     Wait Class
                      latch: cache buffers chains     1,449     251     173     11.5     Concurrency
                      CPU time           156           7.2      
                      latch free     1,031     71     69     3.3     Other
                      SQL*Net more data to client     1,007,758     23     0     1.1     Network
                      direct path read     3,575     12     3     .6     User I/O
                      after tuning :
                      DB Name     DB Id     Instance     Inst num     Release     RAC     Host
                      TESTDB     2465204434     testdb     1     10.2.0.4.0     NO     testserver
                           Snap Id     Snap Time     Sessions     Cursors/Session
                      Begin Snap:1403     21-May-09 18:24:32     109     2.0
                      End Snap:     1404     21-May-09 18:37:26     99     1.6
                      Elapsed:           12.90 (mins)            
                      DB Time:           15.89 (mins)            
                      
                      
                      Load Profile
                           Per Second     Per Transaction
                      Redo size:     1,068.64     91,871.56
                      Logical reads:     9,996.25     859,381.00
                      Block changes:     3.82     328.33
                      Physical reads:     43.00     3,696.78
                      Physical writes:     0.39     33.78
                      User calls:     13,463.10     1,157,427.56
                      Parses:     2,367.31     203,518.11
                      Hard parses:     0.06     5.22
                      Sorts:     216.08     18,576.78
                      Logons:     0.05     4.00
                      Executes:     2,368.39     203,611.67
                      Transactions:     0.01      
                      
                      Event     Waits     Time(s)     Avg Wait(ms)     % Total Call Time     Wait Class
                      CPU time           876           91.8      
                      SQL*Net more data to client     9,192,435     187     0     19.6     Network
                      latch free     9,055     109     12     11.5     Other
                      SQL*Net more data from client     3,530,492     65     0     6.9     Network
                      direct path read     33,304     44     1     4.6     User I/O
                      after tuning,the reponse time has reduced and thuoughput has increased ,but the sys% cpu is still 40%
                      • 8. Re: how to troubleshooting high sys cpu usage ???
                        Oded Raz
                        Post the entire SQL Sections of the AWR, and the top 5 event of that awr

                        Edited by: Oded Raz on Jun 28, 2009 5:40 PM
                        • 9. Re: how to troubleshooting high sys cpu usage ???
                          655554
                          Hi,I have post the top 5 event above.

                          before tuning:
                          SQL ordered by Gets
                          •     Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. 
                          •     Total Buffer Gets: 4,348,081 
                          •     Captured SQL account for 73.3% of Total 
                          Buffer Gets      Executions      Gets per Exec      %Total     CPU Time (s)     Elapsed Time (s)     SQL Id     SQL Module     SQL Text
                          1,768,970     2,593     682.21     40.68     32.47     1375.28     a2vm9mf99ma3p
                                 select 
                          712,504     2,883     247.14     16.39     13.14     83.85     66vrg3gz2stvf
                                 select 
                          130,928     7,901     16.57     3.01     7.83     10.49     ctnn0bpfd3v5k
                                 select 
                          92,800     2,902     31.98     2.13     2.73     6.40     2985xzq8x547u
                                 select 
                          81,498     27,159     3.00     1.87     3.12     3.98     1prkhshjp25sc
                                 select 
                          60,513     2,631     23.00     1.39     2.29     5.64     2d8w9016z0kug
                                 select 
                          55,416     15,835     3.50     1.27     2.12     2.39     8tk22zsappg9m
                                 select 
                          44,879     15,838     2.83     1.03     1.74     1.89     c6sjzyfh9jmz0
                                 select 
                          31,677     10,558     3.00     0.73     1.15     1.24     5yr7z98xmcpqz
                                 select
                          26,346     13,172     2.00     0.61     1.31     1.47     36shs0f8kxp5z
                                 select 
                          after tuning:
                          SQL ordered by Gets
                          •     Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. 
                          •     Total Buffer Gets: 7,734,429 
                          •     Captured SQL account for 99.4% of Total 
                          Buffer Gets      Executions      Gets per Exec      %Total     CPU Time (s)     Elapsed Time (s)     SQL Id     SQL Module     SQL Text
                          1,728,723     99,759     17.33     22.35     113.87     119.45     309a6ak09rud2
                                 select 
                          996,920     332,437     3.00     12.89     41.57     44.03     1prkhshjp25sc
                                 select 
                          964,093     199,469     4.83     12.46     32.19     33.28     8tk22zsappg9m
                                 select 
                          531,776     33,241     16.00     6.88     34.38     36.36     2985xzq8x547u
                                 select 
                          432,140     199,473     2.17     5.59     20.86     21.57     c6sjzyfh9jmz0
                                 select 
                          332,406     166,226     2.00     4.30     17.28     17.84     36shs0f8kxp5z
                                 select 
                          265,872     33,234     8.00     3.44     7.74     8.09     4a34fa9tt90wx
                                 select * 
                          232,617     33,243     7.00     3.01     7.54     7.78     53rvrd0ug5gwm
                                 select 
                          232,603     33,237     7.00     3.01     9.24     9.83     9254c5ag3z9qd
                                 select 
                          232,589     33,239     7.00     3.01     8.11     8.40     gum17w9qgrw8p
                                 select co
                          232,547     33,246     6.99     3.01     12.56     13.21     cx8yttzjh9ftz
                                 select 
                          232,484     33,244     6.99     3.01     10.08     11.07     gbx7hs3ykpjqs
                                 select
                          199,446     99,753     2.00     2.58     11.27     11.89     2hpja5k312kwa
                                 select 
                          199,428     33,241     6.00     2.58     9.15     9.56     2d8w9016z0kug
                                 select 
                          199,389     199,469     1.00     2.58     19.59     20.25     b9j0pb2d5cn69
                                 select 
                          133,004     33,253     4.00     1.72     17.56     18.67     a2vm9mf99ma3p
                                 select 
                          99,756     33,255     3.00     1.29     3.77     3.92     5yr7z98xmcpqz
                                 select 
                          99,747     33,258     3.00     1.29     4.58     4.70     1fnshqnqxjtqz
                                 select 
                          • 10. Re: how to troubleshooting high sys cpu usage ???
                            Tanel Poder
                            You say you have a single dual-core CPU (are both cores in use?)

                            Having runnable processes at 40 with 2 CPUs only isn't too normal condition for Oracle, I think this may be the reason for your high sys mode CPU utilization.

                            From the AWR report you sent I checked following things that are potential SYS CPU eaters:

                            1) lots of physical IOs (especially on buffered filesystems) - you didn't have too many physical IOs
                            2) lots of logons (new process startups which have to attach to SHM segments) - you didn't have too much of these either

                            So, leaving the possibility of OS bugs out, the above things aren't likely your problem. When you have a large runqueue like you mentioned - then things like high CPU usage and some latch contention are probably symptoms of the CPU stravation, so I would look into getting the CPU runqueues to normal levels first - and troubleshoot SYS usage only if it still is there.

                            So, I'd either throttle the workload to prevent CPU trashing or try to tune the SQLs to use even less CPU time so that the runqueues would drop.

                            If you really want to see on what exactly the SYS time is spent, you may be able to get some info with truss -fp <PID> on a busy process (be careful to test this in test env first) or go even deeper with trace/trcfmt commands. But the latter one impacts your system performance more during tracing so you probably dont want to go that way.

                            Throttle your workload or tune the SQLs and the SYS CPU will likely disappear as a consequence..

                            --
                            Tanel Poder
                            http://blog.tanelpoder.com
                            • 11. Re: how to troubleshooting high sys cpu usage ???
                              655554
                              Thanks for your reply.

                              Having runnable processes at 40 with 2 CPUs only isn't too normal condition for Oracle, I think this may be the reason for your high sys mode CPU utilization.

                              I also think this is the reason for high sys cpu .
                              • 12. Re: how to troubleshooting high sys cpu usage ???
                                655554
                                The sql is very hard to tune,because the avg buffer gets per sql is very low,most are below 10 buffer gets per sql, the high buffer gets is during to high executions per second
                                • 13. Re: how to troubleshooting high sys cpu usage ???
                                  Tanel Poder
                                  Yep, in this case the hardware is just undersized for given workload... The "buy more hardware" end point in tuning process is a perfectly valid in some cases....
                                  • 14. Re: how to troubleshooting high sys cpu usage ???
                                    Timur Akhmadeev
                                    jinyu wrote:
                                    The sql is very hard to tune,because the avg buffer gets per sql is very low,most are below 10 buffer gets per sql, the high buffer gets is during to high executions per second
                                    Are such high execution rates really needed? Suppose you have a suboptimal procedure(s), which run row-by-row processing - then it is reasonable to rewrite it to a single SQL.
                                    1 2 Previous Next