How to identify CPU hogging Oracle sessions on a Windows server

Posted by David Yahalom under Oracle

The Oracle Server process model is different between Windows and Linux/Unix. While in Linux the Oracle instance uses a dedicated process model on Windows server, the instance is composed from one oracle.exe process and many different threads. Each thread represent either a background “process” (PMON, SMON…) or a foreground user session.

So, when you are running Oracle server on Windows and encounter a situation where the server CPUs are nearing 100% utilization, a quick glimpse in Task Manager only reveals that oracle.exe hogging the CPU. Nothing regarding which specific thread or session is responsible for the high load.

For us to identify the taxing session we will need to do some basic digging.

First, we’ll need to know which thread within the oracle.exe process is hogging the processor. In order to do so we’ll need a process explorer that supports displaying threads within processes. A good free choice is QSlice, a free program that comes as part of the Windows2000 resource kit. You can get it here.

When running QSlice you’ll see a list of process IDs, names and CPU utilization for each process. Locate oracle.exe and double click it.

Now you’ll see a list composed of TID (Thread IDs), time / CS and % of CPU utilized within the oracle.exe process. Identify the thread which is taxing the CPU (for example, thread 6b88) by looking at the TID (Thread ID) column with the highest user or system CPU time and convert the value in TID column from HEX to DEC.

Open SQL*PLUS and logon as sysdba.

Use the following query to get details about the specific session

SQL> select proc.spid ThreadNO,
sess.username Username,
sess.osuser OSUser,
sess.status Status,
sess.sid SessionID,
sess.program Program
from v$process proc, v$session sess, v$bgprocess bg
where sess.paddr = proc.addr
and bg.paddr(+) = proc.addr
and proc.spid in (TID_VALUE_CONVERTED_TO_DEC)

(you can modify the query to include more columns to be displayed from v$session, v$process and v$bgprocess depending on your specific needs).

You can also retrive the specific SQL the CPU hogging session is running by running the following SQL:

SQL> select sqlarea.sql_text
from v$process proc,
v$session sess,
v$sqlarea sqlarea
where proc.addr = sess.paddr
and sess.sql_hash_value = sqlarea.hash_value
and proc.spid in (TID_VALUE_CONVERETED_TO_DEC)

You can now use the ALTER SYSTEM KILL SESSION command to kill the taxing session if needed.