Here is what I found out from Oracle metalink to debug and figure out what SQL causes oracle to go 100%.
- Download processExplorer, and launch it
- Locate the program that's using 100% CPU, mostly it will be Oracle.exe
- Double click on the executable name, and open its properties window.
- Ignore any warning messages if any
- On the properties window, click on Thread tab
- Find the thread with high CPU%
- 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' ;
