Troubleshoot High CPU utilization on oracle database server

Check High CPU 

-  top 

-  top csc

-  Get TOP CPU Consuming PID by grep
example :
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10

OUTPUT EXAMPLE

Input PID from previous result when prompted. You will get SID 

set pages 100
set lines 400
col MACHINE for a20
select vs.sid,vs.serial#,vs.username,vs.osuser,vs.machine,vs.program from v$session vs, v$process vp where vs.paddr = vp.addr and vp.spid = &pid;

OUTPUT:  

Enter value for pid: 6218

old   2: and vp.spid = &pid

new   2: and vp.spid = 6218

       SID    SERIAL# USERNAME                       OSUSER                         MACHINE              PROGRAM

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

      2034      63287 SAN8124                       rishabh                        horclvm1               Algorithm@horclvm1 (TNS V1-V3)

SQL> /

Enter value for pid: 6265

old   2: and vp.spid = &pid

new   2: and vp.spid = 6265

       SID    SERIAL# USERNAME                       OSUSER                         MACHINE              PROGRAM

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

      1247      59430 SCAN8124                       rishabh                        horclvm1               Algorithm@horclvm1 (TNS V1-V3)

SQL> /

Enter value for pid: 6126

old   2: and vp.spid = &pid

new   2: and vp.spid = 6126

       SID    SERIAL# USERNAME                       OSUSER                         MACHINE              PROGRAM

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

        95      59421 SCAN8124                       rishabh                        horclvm1               Algorithm@horclvm1 (TNS V1-V3)

SQL> /

Enter value for pid: 6029

old   2: and vp.spid = &pid

new   2: and vp.spid = 6029

       SID    SERIAL# USERNAME                       OSUSER                         MACHINE              PROGRAM

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

      1842      25717 SCAN8124                       rishabh                        horclvm1               Algorithm@horclvm1 (TNS V1-V3)

SQL> /

Enter value for pid: 6304

old   2: and vp.spid = &pid

new   2: and vp.spid = 6304

       SID    SERIAL# USERNAME                       OSUSER                         MACHINE              PROGRAM

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

      1336      26275 SCAN8124                       rishabh                        horclvm1               Algorithm@horclvm1 (TNS V1-V3)

SQL> /

Enter value for pid: 6367

old   2: and vp.spid = &pid

new   2: and vp.spid = 6367

       SID    SERIAL# USERNAME                       OSUSER                         MACHINE              PROGRAM

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

       241      48737 SCAN8124                       rishabh                        horclvm1               Algorithm@horclvm1 (TNS V1-V3)

SQL> /

Enter value for pid: 908

old   2: and vp.spid = &pid

new   2: and vp.spid = 908

       SID    SERIAL# USERNAME                       OSUSER                         MACHINE              PROGRAM

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

      1841       5411 SCAN8124                       rishabh                        horclvm1               Algorithm@horclvm1 (TNS V1-V3)

SQL>

**Now, By using SID from previous results check which statement is running.

select t.sql_text
from v$session s,v$sqltext t
where s.sql_address=t.address and s.sid = &which_sid
order by sid, piece asc;


Sort the highest CPU users the bottom of report for identification. Third Column is the CPU percentage.)
ps -aux|sort -n +2 



 Check currently running SQL of specific PID:
set pagesize 100
select a.sql_text,OSUSER, client_info, MACHINE, disk_reads
, a.buffer_gets, a.executions , b.sid,c.spid
from v$sqlarea a, v$session b,v$process c
where spid = &spid and
a.address = b.sql_address
and c.addr=b.paddr;

check swap in(si) & swap out (so) in vmstat

vmstat 3 3

jk_assoc[oracle]_pck> vmstat 3 3 __________
procs memory | swap | io system cpu
r b swpd free buff cache | si so| bi bo in cs us sy id wa
2 0 0 1009156 106024 16690876 | 0 0| 1 0 1 0 0 1 0 0
2 0 0 1005972 106024 16690880 | 0 0| 672 35 512 662 29 3 68 0
5 0 0 1000816 106024 16690896 | 0 0| 23712 428 1569 3859 36 6 58 0
|________|

b. Run vmstat command to see if there is an excessive number of page in or page out processes, look for large amounts of Swap in (si) and Swap out (so). (The first set of numbers in the report should be discarded, as it is an average.)


---> Check Network Performance

jk_assoc[oracle]_pck> netstat -i
Kernel Interface table
Iface MTU Met RX-OK RX-ERR RX-DRP RX-OVR TX-OK TX-ERR TX-DRP TX-OVR Flg
eth0 1500 0 1697373546 0 0 0 2712721015 0 0 0 BMRU
eth0:0 1500 0 - no statistics available - BMRU
eth0:5 1500 0 - no statistics available - BMRU
eth1 1500 0 38256346 0 0 0 222076 0 0 0 BMRU
eth2 1500 0 180643985 0 0 0 9680 0 0 0 BMRU
eth3 1500 0 18848028 0 0 0 7438587 0 0 0 BMRU
eth4 1500 0 18845772 0 0 0 7458993 0 0 0 BMRU
lo 16436 0 11443615 0 0 0 11443615 0 0 0 LRU

a. Run netstat to see if there are any errors or drops on the network interface. Excessive errors should be 
investigated by the Unix team & Network Team.

b. Run tnsping and ping commands between the servers looking for long latency in the time to respond. Anything greater than 500ms is an issue that the Unix team & Network Team should look into.