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

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,
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

Quick Links:
Have a Oracle Question
Do you have an Oracle Question?

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

Best Regards,
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.