Processes that Used Too Much CPU Resources

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