How to Diagnose High CPU Utilization of ORACLE.EXE in Windows Environment

Unlike Unix/Linux, Windows OS do not show separate processes running for the database in "Task Manager".  All the Oracle processes (Called as threads in Windows) are shown as the single process Oracle.exe in "Task Manager". So if one oracle session is using a lot of CPU, then this is simply displayed as Oracle.exe using lot of CPU. In this blog, we will see how to identify which thread is consuming CPU.

1.    Sysinternals Process Explorer:  Process Explorer is a standalone executable downloadable from the direct link: http://technet.microsoft.com/en-US/sysinternals/bb896653.

This utility uses a graphical interface to allow you to monitor the CPU usage of processes and, more importantly for Oracle, individual threads. Once you have the thread details you can use those to dump information from within oracle to determine what the process in question is doing, allowing you to debug its activity.

Run Process Explorer on the system where you are interested in investigating Oracle's CPU usage with administrator's account.

2.    Search for Oracle.exe process in Process Explorer tool.  Right click on Oracle.exe and select properties.

3.    Select the "Threads" Tab from the properties window, a list of threads within the process should be displayed.  Threads running under Oracle.exe process will be displayed in this window. Sort the threads according to CPU usage.

4.    Get the Thread ID (Displayed as TID) which is consuming highest CPU. Below query can be used to check the details of the session in the database.

SELECT prc.spid "Thread ID",

        bgp.name "Background Prc",

        sess.username "UserName",

        sess.osuser "OSUser",

        sess.status "STATUS",

        sess.sid "SessionID",

        sess.serial# "SerialNo",

        sess.program "OSProgram"

FROM v$process prc,

        v$bgprocess bgp,

        v$session sess

WHERE sess.paddr  = prc.addr

AND bgp.paddr(+) = prc.addr

AND prc.spid     = --TID value-- ;

5.    Additionally we can run the following query to get the SQL Text of the current SQL:

SELECT sqla.sql_text

FROM v$process prc,

        v$session sess,

        v$sqlarea sqla

WHERE prc.addr = sess.paddr

AND sess.SQL_HASH_VALUE = sqla.HASH_VALUE

AND prc.spid = --TID value--;