- Use 'top' command to find which processes (PID) eat much CPUs.

- Join v$process & v$session & v$sqlarea to know what happens in.

Check alert log for any error messages/trace file generated.

You can also sice the oracle processes in windows.  For that you have to install microsoft's qslice. (can be downloaded from microsoft site)

Take note that using 'ALTER SYSTEM KILL SESSION' is not enough to kill absolutely a session; it just kill Oracle session. As we know, one session in V$SESSION will have one corresponding process on Server Operating System. So we also need to kill the server process.

On *nix, we use 'kill' command to kill a process. On Windows, we can use $ORACLE_HOME/bin/orakill.exe.

For finding which process eat much CPU resource, you can use this sql CODE:

CPU Usage By Session
CPU USAGE BY SESSION NOTES:

Username - Name of the user
SID - Session id
CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)

select  nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
VALUE cpu_usage
from  v$session ss,
v$sesstat se,
v$statname sn
where   se.STATISTIC# = sn.STATISTIC#
and   NAME like '%CPU used by this session%'
and   se.SID = ss.SID
order   by VALUE desc

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips