Find the Oracle session that’s using a lot of resources on a Linux system

To find that, I did the following:

[oracle@dbserver marius]$ ps aux | sort -nrk 3,3 | head -n 10
oracle 28986 75.8 1.6 10739264 1602896 ? Rs Jan24 137897:06 ora_q002_DBINST
oracle 23660 63.1 1.2 10730424 1221500 ? Rs Feb12 97718:28 ora_q003_DBINST
oracle 8360 15.7 6.0 10734592 5970484 ? Ss May28 377:34 oracleDBINST (LOCAL=NO)
oracle 2264 15.5 5.8 10736632 5832076 ? Rs May28 352:31 oracleDBINST (LOCAL=NO)
oracle 8356 15.1 5.5 10736640 5491732 ? Rs May28 364:28 oracleDBINST (LOCAL=NO)
oracle 5592 15.0 0.1 10731024 152936 ? Ss 13:22 0:00 ora_j000_DBINST
oracle 8358 14.9 5.6 10736632 5635028 ? Rs May28 358:14 oracleDBINST (LOCAL=NO)
oracle 8350 14.8 6.1 10736644 6055308 ? Rs May28 355:42 oracleDBINST (LOCAL=NO)
oracle 2266 13.5 5.8 10736632 5779248 ? Rs May28 306:35 oracleDBINST (LOCAL=NO)
oracle 8342 13.3 5.9 10736636 5858864 ? Rs May28 319:50 oracleDBINST (LOCAL=NO)

 

And then on the sqlplus:

SYS @ DBINST > select s.sid, s.serial#, s.username,
 to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,
 p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
 s.program clientprogram, s.module, s.machine, s.osuser,
 s.status, s.last_call_et
from gv$session s, gv$process p
where p.spid=nvl('&unix_process',' ')
and s.paddr=p.addr
order by s.sid 2 3 4 5 6 7 8 9 
 10 ;
Enter value for unix_process: 28986

SID SERIAL# USERNAME LOGON_TIME ORACLEPID ServerPID ClientPID
------------ ------------ ------------------------------ ------------------------ ------------ ------------------------ ------------------------
CLIENTPROGRAM MODULE MACHINE
------------------------------------------------ ---------------------------------------------------------------- ----------------------------------------------------------------
OSUSER STATUS LAST_CALL_ET
------------------------------ -------- ------------
 3027 1 24-JAN 08:54:00 40 28986 28986
oracle@dbserver (Q002) Streams dbserver
oracle ACTIVE 10902893


Elapsed: 00:00:00.01
SYS @ DBINST >

 

So it seems that the STREAMS are very busy 🙂

 

Advertisements