Find the SQL associated with a Unix process

If the server is slow, and you do a "top" or "prstat" and there is a process chewing up CPU, and it's an Oracle process, this is how to see what SQL it's running.

Note that I've truncated some output. Also, it looks a mess, but I can't see any better formatting options.

prstat

PID USERNAME TIME CPU PROCESS/NLWP
25475 oracle 5:52:23 24% oracle/1

ps -ef | grep 25475

oracle 25475 14487 23 Apr 06 ? 352:27 oracleDEV (LOCAL=NO)

sqlplus "/ as sysdba"

select s.username,
s.SID,
s.serial#,
q.SQL_TEXT
from v$session s,
v$process p,
v$sql q
where s.paddr=p.addr
and p.spid=25475
and s.sql_hash_value=q.hash_value;


USERNAME SID SERIAL# SQL_TEXT
MAXIMO 59 22149 select count(*) from trans where message is not null

Since you have the SID and SERIAL#, this session can be killed if required:

alter system kill session '59,22149';