Oracle Faq

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>