CPU utilization at 100%; What is killing the database

More often than not as a System Administrator or as a DBA, we run into situations where the host machine is running at 100% used CPU or 0% idle cpu.

It is vital to find the processes rather quickly that are chewing up the CPU. On Solaris, we can use
prstat command that is excellent to report the processes in the sorted order.
prstat -s cpu -n 10
The above command shows the top 10 CPU consuming processes.

If indeed it is the memory that is running out, you can tweak the prstat command
prstat -s size -n 10
The above command shows the top 10 memory consuming processes.
Once we find the top CPU or memory consuming processes, we can map those processes to the actual database session to see what really is happening in those sessions.

select inst_id,module,status,sid,serial#,username,last_call_et
from gv$session where paddr in ( select addr from gv$process
where spid = &spid and inst_id=&inst_id)
The above RAC enabled command shows what those sessions are, given the process id from prstat command.

This is a quick method to get a handle on the processes that are killing the host machine.

Have a question? Please leave a comment and I would be glad to answer.