Unix Top.PID and V$session.Process

[st2000]

Hi... If I want to know which particular user is really hogging the system, can I know that ?? I am not really looking for which datafile has most disk reads, or IO but something similar to this..

I did a top command in unix and it shows these results..

oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/bdump_> top
USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
wccprod 139834 27.1 0.0 7272 6212 - A 14:51:34 2:02 /home/wcc/app_sh
oracle 123160 16.3 1.0 48204 33448 - A 14:50:09 1:41 oracleODBMSPRD


Hoping that I can trace the oracle process further I did..
oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/bdump_> ps -ef|grep 123160
oracle 96892 138284 0 14:56:48 pts/10 0:00 grep 123160
oracle 123160 1 0 14:50:09 - 1:41 oracleODBMSPRD (LOCAL=NO)


oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/bdump_> ps -ef|grep oracleODBMSPRD |wc -l
92

SQL> select count(*) from v$process;

COUNT(*)
----------
98

SQL> select count(*) from v$session;

COUNT(*)
----------
97
And finally..

SQL> l
1 select substr(username,1,8) username, substr(osuser,1,10) osuser, process osprocess,
2 substr(machine,1,20) host,substr(terminal,1,10) terminal, type, sid, serial#,
3 substr(program ,1,25) program
4* from v$session where process = '123160'
SQL> /

no rows selected

Why would I get "no rows" ..

I basically want to find the user who is responsible for close to 16% of cpu-usage by having the Unix PID, (in v$session it is process)..

Please advise.. what am I missing here or where should I be looking at?

Thanks, ST2000

[Sharma]

Try this one:

select substr(S.username,1,8) username, substr(osuser,1,10) osuser, process osprocess,
substr(machine,1,20) host,substr(P.terminal,1,10) terminal, type, sid, S.serial#,
substr(S.program ,1,25) program
from v$session S, V$PROCESS P
where P.pid = '123160'
and S.paddr=P.addr

OS process id is basically stored in pid column of V$PROCESS.

[st2000]

Yap.. That's what I was using.. Using v$process.PID..

Anyway, below are the results which are same..

oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/bdump_> top
USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
oracle 115508 28.0 0.0 30676 16048 - A 15:36:37 6:38 oracleODBMSPRD
wccprod 91612 21.4 0.0 7544 6480 - A 15:46:02 1:02 /home/wcc/app_sh
oracle 123160 4.5 1.0 48752 23364 - A 14:50:09 5:12 oracleODBMSPRD
wccprod 84960 3.2 0.0 9696 8632 - A 15:44:50 0:14 /home/wcc/app_sh

SQL> select substr(S.username,1,8) username, substr(osuser,1,10) osuser, process osprocess,
2 substr(machine,1,20) host,substr(P.terminal,1,10) terminal, type, sid, S.serial#,
3 substr(S.program ,1,25) program
4 from v$session S, V$PROCESS P
5 where P.pid = '123160'
6 and S.paddr=P.addr
7
SQL> /

no rows selected

[Sharma]

Try this one.

select P.spid "OS Thread", S.username "Name-User", S.osuser, S.program
from V$PROCESS P, V$SESSION S
where P.addr = S.paddr
and S.username is not null;

[st2000]

Yes sir.. It works.. Thanks.. Exactly what I was looking for..

Can you please tell me, why some Unix PID is in v$process and only some in v$session, when you get a chance..

I shall re-write my query now to accommodate both the views..

Thanks, ST2000

[pando]

where P.pid = '123160'

should be

where P.spid = 123160

v$process shows server process

v$session shows user process or logon sessions