Use the top tool to identify processes that consume more CPU

1. Use the top tool to identify processes that consume more CPU

[email protected] ~]$ Top

Top-10:48:27 up 23:15, 4 users, Load average:1.09, 0.43, 0.15

tasks:161 Total, 3 running, 158 sleeping, 0 stopped, 0 zombie

Cpu (s): 74.3%us, 23.7%sy, 0.0%ni, 0.0%id, 2.0%wa, 0.0%hi, 0.0%si, 0.0%st

mem:900096k Total, 894948k used, 5148k free, 6436k buffers

swap:2097144k Total, 24692k used, 2072452k free, 666588k cached

  PID user      pr  ni  virt  res  SHR S%cpu%mem     time+  command                                                                                                                                       

10306 oracle    25   0  326m 171m 168m R 93.4 19.5   1:51.53 oracle                                                                                                                                        

 3258 oracle    16   0  338m  32m  29m s  3.3  3.7    1:31.02 oracle                                                                                                                                       

3256 Oracle 0 347m 191m 175m S 1.3 21.8 0:04.65 Oracle

You can see that the user Oracle process is pid=10306 CPU-intensive 93.4

2. Based on the PID you just found, find information about the process in the database

SYS @ prod >select pid,spid,addr from v$process where spid=10306;

PID SPID ADDR

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

10306 2e61b254

3. Find V$session related session information based on the PID found

SYS @ prod >select sid,saddr,paddr from v$session where paddr= ' 2e61b254 ';

SID saddr paddr

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

146 2e6fc134 2e61da40

147 2e6fd3e8 2e61c370

2E700C04 2e61b254

4. Based on the SID found, find the sql_hash_value of the session

SYS @ prod >select Sid,sql_hash_value from v$session where sid=150;

SID Sql_hash_value

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

3514920902

5. Based on the Sql_hash_value found, find the corresponding SQL statement

SYS @ prod >select Sql_text from V$sqltext where hash_value= ' 3514920902 ';

Sql_text

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

declare begin for I in 1..10000000 loop insert into test values (i); end loop; End;

Use the top tool to identify processes that consume more CPU