11 Replies Latest reply: Feb 28, 2014 12:51 AM by sam995972 RSS

    high cpu usage

    sam995972

      Hi All,

       

      Oracle 9.2.0.8

      Solaris10

       

      server is having only one database.

      My CPU is going to 100% usage, how to check which SQL is causing this issue.

      i have taken statspack report, but am not able to conclude which is causing this issue.

       

      kindly suggest.how to proceed further ...

       

      thanks,

      SAM.

        • 1. Re: high cpu usage
          Suntrupth

          You can use TOP command to know which user is consuming CPU.

           

          If Oracle is the top cpu consuming user, you can check "SQL ordered by CPU" to know which sqls are consuming high cpu.

           

          Regards,

          Suntrupth

          • 2. Re: high cpu usage
            sam995972

            thanks for quick reply. if possible...can you please share select query ....

            • 3. Re: high cpu usage
              KarK

              Hi,

               

              You can query from views like V$SQL or V$SQLAREA , cpu_time column.

               

              Something like below:

               

              select sql_text, cpu_time cpu_time_in_microseconds from v$sqlarea order by cpu_time desc;

              • 4. Re: high cpu usage
                Aman....

                The question is -how are you saying that it's the database that's consuming the 100% cpu? As Suntrupth mentioned, fire the TOP command and see what's really going on. Just blaming the database for running out of CPU is not correct.

                 

                HTH

                Aman....

                • 5. Re: high cpu usage
                  Smohib

                  yes, you 1st need to check which process is using max memory.

                   

                  I am not sure but "prstat" command is used to check CPU usage in Solaris.

                  TOP I have used in Linux.

                   

                  Also if its Oracle, then its better you check AWR Report for "SQL ordered by CPU" as mentioned by Suntrupth i.e.; you can check the % of CPU which is max used, from there you get SQL_ID which can be intern used to get whole SQL Query using "dba_hist_sqltext" & you can generate explain plan to check how the query is getting executed.

                   

                  Hope it helps.

                  Correct me if I am wrong...

                  Mohib

                  • 6. Re: high cpu usage
                    hitgon

                    We are using below steps in linux you can check same steps in Solaris

                     

                    You can monitor the heavy process using prstat command

                    and pass the spid in below query.

                     

                    SELECT

                      s.sid,

                      s.serial#,

                      p.spid,

                      s.username,

                      s.program,sql_id,PREV_SQL_ID

                    FROM v$session s

                      JOIN v$process p ON p.addr = s.paddr

                    WHERe p.spid= &spid;

                     

                    Pass the sql_id in below cmd for check the sql query

                     

                    select * from table(dbms_xplan.display_cursor('&sql_id',0));

                     

                    Best of luck

                    hitgon

                    • 7. Re: high cpu usage
                      Anand...

                      Hi,

                       

                      Using TOP -c command check for the pid consuming most of the CPU. Once you have the pid check for the what it is doing within the db

                       

                      <pre>

                      set serveroutput on size 50000

                      set echo off feed off veri off

                      accept 1 prompt 'Enter Unix process id: '

                       

                      DECLARE

                        v_sid number;

                        s sys.v_$session%ROWTYPE;

                        p sys.v_$process%ROWTYPE;

                      BEGIN

                        begin

                          select sid into v_sid

                          from   sys.v_$process p, sys.v_$session s

                          where  p.addr     = s.paddr

                            and  (p.spid    = &&1

                             or   s.process = '&&1');

                        exception

                          when no_data_found then

                            dbms_output.put_line('Unable to find process id &&1!!!');

                            return;

                          when others then

                            dbms_output.put_line(sqlerrm);

                            return;

                        end;

                       

                        select * into s from sys.v_$session where sid  = v_sid;

                        select * into p from sys.v_$process where addr = s.paddr;

                       

                        dbms_output.put_line('=====================================================================');

                        dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);

                        dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.program);

                        dbms_output.put_line('Shadow      : '|| 'PID: '||p.spid||' - '||p.program);

                        dbms_output.put_line('Terminal    : '|| s.terminal || '/ ' || p.terminal);

                        dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);

                        dbms_output.put_line('Ora User    : '|| s.username);

                        dbms_output.put_line('User Module : '|| s.module);

                        dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);

                        dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));

                        dbms_output.put_line('Login Time  : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));

                        dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '990.0') || ' min');

                        dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));

                        dbms_output.put_line('Latch Spin  : '|| nvl(p.latchspin, 'NONE'));

                       

                        dbms_output.put_line('Current SQL statement:');

                        for c1 in ( select * from sys.v_$sqltext

                                    where HASH_VALUE = s.sql_hash_value order by piece) loop

                          dbms_output.put_line(chr(9)||c1.sql_text);

                        end loop;

                       

                        dbms_output.put_line('Previous SQL statement:');

                        for c1 in ( select * from sys.v_$sqltext

                                    where HASH_VALUE = s.prev_hash_value order by piece) loop

                          dbms_output.put_line(chr(9)||c1.sql_text);

                        end loop;

                       

                        dbms_output.put_line('Session Waits:');

                        for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop

                          dbms_output.put_line(chr(9)||c1.state||': '||c1.event);

                        end loop;

                       

                      --  dbms_output.put_line('Connect Info:');

                      --  for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop

                      --    dbms_output.put_line(chr(9)||': '||c1.network_service_banner);

                      --  end loop;

                       

                        dbms_output.put_line('Locks:');

                        for c1 in ( select /*+ ordered */

                                decode(l.type,

                                -- Long locks

                                            'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',

                                            'UL', 'PLS USR LOCK',

                                -- Short locks

                                            'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',

                                            'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',

                                            'CU', 'CURSOR BIND ',

                                            'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',

                                            'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',

                                            'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',

                                            'FI', 'SGA OPN FILE',

                                            'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',

                                            'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',

                                            'LS', 'LOG SWITCH  ',

                                            'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',

                                            'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',

                                            'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',

                                            'RW', 'ROW WAIT    ',

                                            'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',

                                            'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',

                                            'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',

                                            'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',

                                            'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',

                                            'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',

                                            'TYPE='||l.type) type,

                             decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',

                                             4, 'S',    5, 'RSX',  6, 'X',

                                             to_char(l.lmode) ) lmode,

                             decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',

                                               4, 'S', 5, 'RSX', 6, 'X',

                                               to_char(l.request) ) lrequest,

                             decode(l.type, 'MR', o.name,

                                            'TD', o.name,

                                            'TM', o.name,

                                            'RW', 'FILE#='||substr(l.id1,1,3)||

                                                  ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,

                                            'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,

                                            'WL', 'REDO LOG FILE#='||l.id1,

                                            'RT', 'THREAD='||l.id1,

                                            'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),

                                            'ID1='||l.id1||' ID2='||l.id2) objname

                             from  sys.v_$lock l, sys.obj$ o

                             where sid   = s.sid

                               and l.id1 = o.obj#(+) ) loop

                          dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);

                        end loop;

                       

                       

                        dbms_output.put_line('=====================================================================');

                       

                      END;

                      /

                      undef 1

                      set feedback on;

                      set veri on;

                      </pre>

                       

                      Identify the sql and see decide next what to do. You can even run the ?/rdbms/admin/addmrpt.sql for the period when issue occurs and check the report. Also check the AWR reports 'SQL ordered by CPU' and 'SQL ordered by Buffer Gets'

                      • 8. Re: high cpu usage
                        tvCa-Oracle

                        awrrpt.sql will tell you

                        • 9. Re: high cpu usage
                          tvCa-Oracle

                          The information given may not be complete, as he IS saying the OS is at 100% for CPU, so something or somebody did do a check somehow. Possibly Enterprise Manager ...

                          • 10. Re: high cpu usage
                            Aman....

                            Yeah , possible. But many do post that the database is CPU bound just when they see the DB CPU timed event in the awr report as well.

                             

                            Aman....

                            • 11. Re: high cpu usage
                              sam995972

                              thanks all for quick reply...we have identified issue...with stats pack thorough  analysis ...issue is due to archive log net backup...it is chewing high cpu while backup running.