Heavy load on Server... Find the session details of the user

If the CPU utilization is too high on the Server, then run the below command at OS level to find the Process Id and then use the same PID in the next query to find the session details which is consuming the resources

To find the PID of the top most resource consuming process

ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10

%CPU   PID USER     COMMAND
 9.8 22834 oraprod  oraclePROD (LOCAL=NO)
 9.7 22832 oraprod  oraclePROD (LOCAL=NO)
 8.9 21278 oraprod  oraclePROD (LOCAL=NO)
 8.8 21953 oraprod  oraclePROD (LOCAL=NO)
 8.5 20334 oraprod  oraclePROD (LOCAL=NO)
 6.7 22610 oraprod  oraclePROD (LOCAL=NO)
 4.7 18182 oraprod  oraclePROD (LOCAL=NO)
43.6 10136 oraprod  oraclePROD (LOCAL=NO)
40.4 20247 oraprod  oraclePROD (LOCAL=NO)



To find the session details of the user, use the PID from the above command as the input for the below statement


SELECT s.sid, p.spid "OS Pid", s.module, s.process, s.schemaname "Schema", s.username "Username",
s.osuser "OS User", s.program "Program", a.sql_id, substr(a.sql_text,1,550) "SQL Text"
FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr
and s.sid = (select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid = &p);