I was running a PeopleSoft Application Engine process that I suspected was hogging the CPU on a Windows environment. I wanted to see whether this process was in fact the cause of the CPU intensive activities on the database.

Interestingly, I discovered that on a Windows environment, an Oracle instance is composed of one oracle.exe process with many different threads. Each thread represents either a background process (PMON, SMON, etc.) or a foreground user session. So, when you are running Oracle server on a Windows environment and encounter a situation where the server CPUs are nearing 100% utilisation, Task Manager only reveals that oracle.exe is taking up the CPU time. There are no details as to which specific thread or session is responsible for the high load.

Fortunately, I managed to find out which thread was responsible for the CPU hogging by downloading a tool (ProcessExplorer by sysinternals) that supports displaying threads within processes. You can download that here.

Process Explorer shows all the processes that are currently running. After double-clicking on the oracle.exe process, I was able to find the TID (Thread ID) that had the highest CPU utilisation. I then ran the following query in SQL Developer, substituting :1 for the TID that had the highest CPU utilisation.

[sourcecode language=”sql”]
select proc.spid TID, sess.username, sess.osuser,
sess.status, sess.sid, sess.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 (:1);
[/sourcecode]

This query can be modified to include more columns to be displayed from v$session, v$process and v$bgprocess depending on your specific preferences.

I was also able to identify the specific SQL that the thread was running, by executing the following SQL and substituting :1 for the TID identified earlier:

[sourcecode language=”sql”]select sqlarea.*
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 (:1);
[/sourcecode]

I was interested to see a clean view of what the bind variables were for the currently executing SQL statement. I used the following query:

[sourcecode language=”sql”]
select * from v$sql_bind_capture
where sql_id = ‘<ENTER SQL_ID HERE>’;
[/sourcecode]

I then created indexes on columns in the SQL statements that I thought were problematic.

It’s also worth noting that if you really need to, you can kill the session by issuing the following command:

[sourcecode language=”sql”]ALTER SYSTEM KILL SESSION;[/sourcecode]

I use the following shutdown command to turn off my computer after a certain timeperiod:
[sourcecode language=”powershell”]
shutdown /s /t 3600
[/sourcecode]

/s means that we want to shutdown the pc
/t indicates a delay in the time that we want to shutdown/restart. In this case, we want the local pc to shutdown in 3600 seconds (1 hour).

Here’s a good reference for other options available to the shutdown command:
http://pcsupport.about.com/od/commandlinereference/p/shutdown-command.htm