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

[ssmith]

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!

[SANJAY_G]

^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

[ssmith]

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.

[SANJAY_G]

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

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

[pando]

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%

[pando]

if you have job process this query would show you which are they

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

[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.

[samdba]

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,

[pando]

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