-
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!
-
^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
-
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.
-
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
-
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%
-
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|