How to monitor Oracle processes (threads) on Win 2000?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to monitor Oracle processes (threads) on Win 2000?

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    At the moment we are having a performance problem where the CPU is 100%. We monitor the processes on the Windows 2000 server and the Oracle.exe process is 100%. I now need to find out with thread/threads are using the most CPU. I get oracle/1#1, oracle/1#2, oracle/2, oracle/2#1, as threads on the Win 2000 server, now how can I tell which thread is relating to which session on oracle? If I get the spid from the database - which is in hex (e.g. 1212), then convert to decimal (e.g. 4626) this doesn't relate to any threads!

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    ^C & ^V from orafaq.com

    One normally kills Oracle processes using the "alter system kill session" command. One can also use the orakill.exe command that comes with Oracle for NT/2000 to kill a thread. You need to specify the thread ID in Hex. Try orakill /? or orakill /h for more details. Example:

    SQL> select spid as thread, osuser, s.program
    SQL> from sys.v_$process p, sys.v_$session s
    SQL> where p.addr = s.paddr;

    DOS> orakill.exe sid thread

    Sanjay

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    The problem is I can't match a thread id from the Windows 2000 server (in perfmon), with a session/process in Oracle. The threads in perfmon are oracle/1, oracle/1#1, oracle/1#2, oracle/2,oracle/2#1, from the thread id above, what spid or sid should it relate to in the database?
    N.B. I don't want to kill the process, I want to find out which process is causing the problems so we can solve the performance problem.

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I don't know what is perfmon but here is a simple script to monitor CPU and DISK I/O for Oracle processes by thread

    Code:
    select p.spid thread, s.username,
        decode(nvl(p.background,0),1,bg.description,
             s.program ) program,
        ss.value/100 CPU,physical_reads disk_io
     from v$process p,
        v$session s,
        v$sesstat ss,
        v$sess_io si,
        v$bgprocess bg
     where s.paddr=p.addr
      and ss.sid=s.sid
      and ss.statistic#=12 
      and si.sid=s.sid
      and bg.paddr(+)=p.addr
     order by ss.value desc;
    Hope this helps.

    Sanjay

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    sanjay's script is very useful
    if you dont see CPU usage you have to set timed_statistics=true

    many times v$session_wait also tells you why you CPU is spinning 100%

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    if you have job process this query would show you which are they

    Code:
    select
     p.spid thread,
     s.username,
         case
         when p.background is null and s.program is null then bg.description
         when p.background is null then s.program
         when p.background = 1 then bg.description
         end program,
         ss.value/100 CPU,physical_reads disk_io
    from  v$process p,
         v$session s,
         v$sesstat ss,
         v$sess_io si,
         v$bgprocess bg
    where  s.paddr=p.addr
      and  ss.sid=s.sid
      and  ss.statistic#=12
      and  si.sid=s.sid
      and  bg.paddr(+)=p.addr
    order  by ss.value desc
    it's sanjay's query modified


  7. #7
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    The scripts are great but the figure are cumulative. I need to monitor the processes that are running at the time the performance is slow. Perfmon is the performance monitor for Windows NT/2000. It should be able to tell you which thread (i.e. oracle session) is causing the problem but I can't relate the thread to an oracle session.

  8. #8
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Sanjay & Pando,

    Great queries from you guys.

    I see 583.25 value in CPU column on my database. How to interpret it? Should it go more than 100%?

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Originally posted by ssmith
    The scripts are great but the figure are cumulative. I need to monitor the processes that are running at the time the performance is slow. Perfmon is the performance monitor for Windows NT/2000. It should be able to tell you which thread (i.e. oracle session) is causing the problem but I can't relate the thread to an oracle session.
    at real time the useful view is v$session_wait, look the event column

    also if you want to monitor you can also create a table as the query then insert the stat values and get the delta, well if it's not too troublesome for you but I would start looking at v$session_wait

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