What's the process or the user doing?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: What's the process or the user doing?

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hello DBAs:

    Using Top Command on Unix RS/6000 I was able to figure out the process ID costing lots of CPU..

    oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/udump_> top
    USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
    oracle 148158 39.8 0.0 27172 13080 - A 09:59:29 4:02 oracleAAMPROD (
    oracle 56374 7.9 0.0 32356 16520 - A 09:03:07 9:46 oracleODBMSPRD


    Using v$process and v$session, I was also able to find out the machine and the user (application user)..

    SQL> l
    1 select substr(s.username,1,8) username, substr(s.osuser,1,10) osuser, s.process osprocess, p.sp
    2 substr(s.machine,1,30) host,substr(s.terminal,1,30) terminal, s.type, s.sid, s.serial#,
    3 substr(s.program ,1,25) program
    4 from v$session s, v$process p
    5 where p.addr=s.paddr
    6* and p.spid = &spid
    SQL> /
    Enter value for spid: 56374
    old 6: and p.spid = &spid
    new 6: and p.spid = 56374

    USERNAME OSUSER OSPROCESS SPID HOST
    -------- ---------- --------- --------- ------------------------------
    TERMINAL TYPE SID SERIAL#
    ------------------------------ ---------- ---------- ----------
    PROGRAM
    -------------------------
    COTPROD Anon014 1128:1434 56374 CG_CITRIX\CG_CITRIX1
    CG_CITRIX1 USER 7 4474
    uniface.exe

    Since it's the application user (Application is on Citrix), We can get only user as Anonxxx (anonymous), but I can track from Terminal which is same for all Anonymous users..

    Can I further query the database to find out what the user is doing? I mean which SQL or Procedure's is he/she running?

    Thanks, ST2000

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Yes. You can see the running SQL statement from the following statement:

    select a.osuser, c.spid, a.username, a.sid, a.status,
    a.program, b.sql_text
    from v$session a, v$sqltext_with_newlines b, v$process c
    where a.sql_address = b.address
    and a.sql_hash_value = b.hash_value
    and a.type != 'BACKGROUND'
    and a.paddr = c.addr
    order by a.status, a.osuser, a.username, a.sid, b.piece ;

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