Thursday, March 21, 2013

Oracle: 100% CPU Usage

I am running Oracle on a  Windows box, and on several occasions my CPU usage goes to 100% for oracle.exe and stays there for a long time crippling the whole server. Most of the time my session browser does not show anything, so I cannot figure out whats causing oracle to run at 100% CPU

Here is what I found out from Oracle metalink to debug and figure out what SQL causes oracle to go 100%.

  1. Download processExplorer, and launch it
  2. Locate the program that's using 100% CPU, mostly it will be Oracle.exe
  3. Double click on the executable name, and open its properties window.
  4. Ignore any warning messages if any
  5. On the properties window, click on Thread tab
  6. Find the thread with high CPU%
  7. Note down the Thread Id

Run the SQL below, and substitue  XXXX with the thread id. It should give you the session info as well as the SQL causing CPU to go crazy. You can investigate further or kill the session

SELECT p.spid Thread_ID,  b.name Background_Process,  s.username User_Name,  s.osuser OS_User,  s.status STATUS,  s.sid Session_ID,  s.serial# Serial_No,  s.program OS_Program, sql_text
FROM   v$session s, v$process p,  v$bgprocess b, v$sqlarea cql
WHERE s.paddr  = p.addr
AND   p.addr = b.paddr(+)
and   s.sql_hash_value = cql.hash_value(+)
AND   p.spid     = XXXX

If you prefer to kill the session, get the session id and serial no from the above query, and run the command below.
Alter system kill session 'Session_ID,Serial_No' ;