How to Find top CPU consuming queries in Unix
In Unix we generally use “top” and “vmstat” commands to determine the utilisation of CPU.
The top comand usually refreshes after every 5 secs or which can be altered .
The output of top command can be similar to below
load averages: 0.71, 0.70, 0.71;                   up 2+14:47:19  11:50:45
189 processes: 188 sleeping, 1 on cpu
CPU states: 82.5% idle, 9.1% user, 8.4% kernel, 0.0% iowait, 0.0% swap
Memory: 8192M phys mem, 2581M free mem, 2007M swap, 2007M free swap
  PID USERNAME LWP PRI NICE SIZE  RES STATE   TIME   CPU COMMAND
 4835 oracle    2 36   0 3801M 3775M sleep  30:49 8.06% oracle
 4831 oracle    2 29   0 3801M 3775M sleep  77:22 7.54% oracle
 4758 oracle    1 53   0 3807M 3780M sleep  44:39 5.91% oracle
 4788 oracle    1 49   0 3798M 3772M sleep  17:53 3.76% oracle
 27438 root      1 59   0 2552K 1856K sleep   0:44 3.68% vmstat
 4772 oracle    2 39   0 3809M 3772M sleep  16:24 2.65% oracle
 4839 oracle    2 59   0 3800M 3775M sleep   3:00 2.02% oracle
 4762 oracle    1 49   0 3807M 3780M sleep  20:21 1.75% oracle
 4790 oracle    1 59   0 3798M 3772M sleep  15:46 1.43% oracle
 4756 oracle    1 59   0 3807M 3780M sleep   6:38 1.32% oracle
 4786 oracle    1 59   0 3798M 3772M sleep   7:56 1.14% oracle
 4776 oracle    2 59   0 3800M 3774M sleep   0:53 1.12% oracle
 4829 oracle    2 59   0 3801M 3775M sleep  69:53 0.96% oracle
 4821 oracle    1 59   0 3798M 3772M sleep   8:17 0.80% oracle
  671 root     42 59   0  61M  52M sleep   4:24 0.67% crsd.bin
From above we can determine the process consuming maximum CPU along with user.
After determining the oracle process pid consuming the highest CPU log into sqlplus as sys and fire the following command to determine the oracle process address for corresponding CPU consuming oracle process
SQL> select addr,pid,spid,username,serial#,program,background from v$process where spid=<OS pid of the CPU consuming process determined from top command>
From the SID,SERIAL# we can determine the corresponding oracle session consuming CPU
SQL>select * from v$session where paddr=<oracle process address of the CPU consuming process determined from addr column of v$process>
Now we can determine the exact query and sql plan for that query from the view v$sqlarea and v$sql_plan by determining the sql_address,hash_value,sql_id from above v$session view
Sql> select sql_text from v$sqlarea where address = < sql_address determined from above v$session view>
SQL> select * from v$sql_plan where hash_value=< hash value determined from v$session>