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]