How to match OS processes to Oracle processes

From time to time I come across the problem of trying to figure out what a particular OS Oracleprocess (PID) is doing in an Oracle database. When monitoring the DB server you can easily discover the processes that are taking most of the CPU (with “top” or “prstat -a”, for example). Once you have the PID, you want to know what that particular process is doing in the DB. To find out you can use the SQL below. As is, it gives you a list of all the Oracle process and the latest SQL that it was running. Its an easy way to match the OS PID to the Oracle session:

SELECT 
 p.username pu
 , s.username su
 , s.status stat
 , s.sid ssid
 , s.serial# sser
 , lpad(p.spid,7) spid
 , substr(sa.sql_text,1,540) txt 
FROM 
 v$process p
 , v$session s
 , v$sqlarea sa 
WHERE 
 p.addr=s.paddr 
 AND s.username is not null 
 AND s.sql_address=sa.address(+) 
 AND s.sql_hash_value=sa.hash_value(+) 
ORDER BY 1,2,7

 

Posted in Databases, Sysadmin, Unix. Bookmark the permalink. RSS feed for this post. Leave a trackback.

Swedish Greys - a WordPress theme from Nordic Themepark.