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

From: Johnson, William L (TEIS) <WLJohnson_at_tycoelectronics.com>
Date: Tue, 3 Jul 2007 10:30:22 -0400
Message-ID: <70C6CD0D975AFC43859A976DFD98C2FE7E1015@us194mx20.tycoelectronics.net>

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_at_pcci.edu] Sent: Tuesday, July 03, 2007 10:04 AM
To: Johnson, William L (TEIS); oracle-l_at_freelists.org 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_at_pcci.edu

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 03 2007 - 09:30:22 CDT