Unix Top.PID and V$session.Process
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Unix Top.PID and V$session.Process

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    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


  2. #2
    Join Date
    Nov 2000
    Posts
    224
    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.

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    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

  4. #4
    Join Date
    Nov 2000
    Posts
    224
    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;

  5. #5
    Join Date
    Oct 2000
    Posts
    449
    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

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    where P.pid = '123160'

    should be

    where P.spid = 123160

    v$process shows server process

    v$session shows user process or logon sessions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width