DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: runaway process in oracle/unix

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    I usually run top on my sun unix box to see whats running and how much load there is on the box. I find manytimes i get a process in oraclet or oraclep which has taken 15minutes of cpu and a cpu load of 45%. How to i figure why what that process is doing and which user is running that process in oracle.?
    running:
    ORacle 8i with 11i apps. unix

    thanks
    Jigar

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    select s.sid, s.serial#, p.spid server_pid, s.username, s.program, s.server, s.status
    from v$session s, v$process p
    where p.addr = s.paddr
    order by to_number(p.spid)

    In the above query, "server_pid" is the process id from ps -ef
    Jeff Hunter

  3. #3
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    Thanks but what i need to know is what is that process doing and is it a runaway process or dead process? I would like to look at the sql of that process as well if possible

    thanks

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    select s.username, s.terminal, s.program, sa.sql_text
    from v$session s, v$sql_area sa, v$process p
    where s.paddr = s.addr
    and a.sql_address = sa.address
    Jeff Hunter

  5. #5
    Join Date
    Jun 2000
    Posts
    295
    I think you meant V$sqlarea instead of V$sql_area

  6. #6
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    Jeff i dont think your sql is correct
    select s.username, s.terminal, s.program, sa.sql_text
    from v$session s, v$sql_area sa, v$process p
    where s.paddr = s.sql_address
    and sa.address = sa.address

    i modified this sql to run but i freezes up on me

  7. #7
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    Try this:
    rem this is ps_view.sql
    col username format a15
    col osuer format a10
    col program format a20
    set verify off
    select a.username, a.osuser, a.program, spid, sid, a.serial#
    from v$session a, v$process b
    where a.paddr = b.addr
    and spid = '&pid';

    rem this is ps_sql.sql
    set verify off
    column username format a15
    column sql_text format a60
    undefine sid
    undefine serial#
    accept sid prompt 'sid: '
    accept serial prompt 'serial#: '
    select 'SQL Currently Executing: '
    from dual;

    select b.username, a.sql_text
    from v$sql a, v$session b
    where b.sql_address = a.address
    and b.sql_hash_value = a. hash_value
    and b.sid = &sid
    and b.serial# ='&serial';

    select 'Open Cursors: '
    from dual;

    select b.username 'USERNAME', a.sql_text 'SQL_TEXT'
    from v$open_cursor a, v$session b
    where b.sql_address =a.address
    and b.sql_hash_value = a. hash_value
    and b.sid = &sid
    and b.serial# ='&serial';

    Note: pid comes from top command.
    Any problem, let me know.


  8. #8
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    Run all this together? in a shell script? or sqlplus?

    Jigar

  9. #9
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    hello,
    i didnt get an anwer for my question, shall i just take the script you gave me and run it all together.??

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