FreeLists / oracle-l / RE: How do I find top CPU consumers on a windows machine running Oracle 9i (9.2.0.4 Enterprise Edition)?

["Johnson, William L (TEIS)" <WLJohnson@xxxxxxxxxxxxxxxxxxx>]

Thanks for the replies - this appears to have confirmed what I feared.
I have seen this on a UNIX machine, but not on a Windows machine to
date...

 

Here is what I believe I am seeing using the procexp.exe utility...

 

1.      Thread 1234 using 100% of a cpu - it's parent process is the
oracle.exe for the prd3 instance.
2.      Thread 1234 does not exist in the Oracle database when looking
at every single entry in v$process.

 

I believe this probably means that someone had connected to the
database; terminated their session which went away from the Oracle
database, but the Operating System never cleaned up the O/S process.  We
have folks using TOAD, SqlDeveloper, MS Access,...so it will be a
nightmare to find the offender at this point-in-time.

 

Killing the thread using the procexp.exe utility appears to have fixed
my problem.  The server is back down to 16% CPU utilization now.

 

Thank you for your responses/help!

Bill

 

 

________________________________

From: Jason Heinrich [mailto:jheinrich@xxxxxxxx] 
Sent: Tuesday, July 03, 2007 10:04 AM
To: Johnson, William L (TEIS); oracle-l@xxxxxxxxxxxxx
Subject: Re: How do I find top CPU consumers on a windows machine
running Oracle 9i (9.2.0.4 Enterprise Edition)?

 

You're on the right path.  In Process Explorer, double-click on the
oracle.exe and view the threads tab.  Select one of the threads that's
causing high CPU usage, and look at its Thread ID.  The thread ID ties
to the PADDR column in v$process.  So you can find the session
information like so:

select * from v$session where paddr = (select addr from v$process where
spid=:threadid);


On 7/3/07 8:08 AM, Johnson, William L (TEIS) wrote:

HP Unix is simple - use a top command.
 
Solaris is not as friendly - but you can use prstat.
 
When I use task manager in Windows XP, I can only see CPU being consumed
by the Oracle.exe process.  I can then perform a "tlist" on the process
number to find out which instance of Oracle is consuming the cpu.  
 
I tried a utility called procexp.exe, but it doesn't tie back to any
sessions in the database.
 
Does anyone have a utility to find the top CPU consumers on a windows
machine?
 
I am desperate and just about ready to stop both production instances
since I can not find the offenders and a 4-way, dual core machine has
been running 100% cpu for a while...
 
Thanks!






---------------
Jason Heinrich
Oracle Database Administrator
Pensacola Christian College
(850) 478-8496 x2509
jheinrich@xxxxxxxx