Jun 12, 2008

Oracle.exe CPU utilization 100% in Windows

You may find oracle.exe process consume 100% CPU in Windows platform.
1.
Download qslice.exe from here
Find oracle.exe detail threads and who use 100% CPU , frind TID ffrom qslice.exe console.
2. TID is a HEX number , need to translate it as DEC , you can translate through this web site. http://www.parkenet.com/apl/HexDecConverter.html
3. Using following sql statement to find thread information.
select p.spid "Thread ID", b.name "Background Process", s.username "User Name", s.osuser "OS User", s.status "STATUS", s.sid "Session ID", s.serial# "Serial No.", s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr
and b.paddr(+) = p.addr
and p.spid= -- TID number

4. Using following command to find thread doing what.
select s2.sql_text
from v$process p,v$session s1,v$sqlarea s2
where p.addr = s1.paddr
and s1.SQL_HASH_VALUE = s2.HASH_VALUE
and p.spid= -- TID number
5. Terminate problematic sesion.
alter system kill session 'SID,#Serial NUM';

0 意見: