-
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
-
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.
-
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
-
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;
-
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
-
where P.pid = '123160'
should be
where P.spid = 123160
v$process shows server process
v$session shows user process or logon sessions
