Monitoring CPU usage of multiple oracle databases running on Shared server

Hi Everyone!!

Here is interesting stuff on monitoring CPU usage of multiple oracle databases running on single unix box. I have seen many times CPU usage of the box crosses 90-95% and when the box hosts multiple databases it becomes cumbersome to troubleshoot the culprit. It will be easy to troubleshoot in case of dedicated database server.

How to find which database is using much of the CPU?

Here is a simple and powerful script which does the basic calculation. It is closer to the perfect value. Since Oracle acquired Sun, I am providing script for Sun OS.


$ cat cpu.ksh
#!/bin/ksh
echo "CPU usage at `date '+DATE: %m/%d/%y TIME:%H:%M:%S'` on host `hostname`"
echo "--------------------------------------------------------------"

for dbname in `ps -eo comm| grep -v grep|grep pmon|sed s/ora_pmon_//`
do
echo "$dbname usage= " `ps -eo pcpu,args|grep -i $dbname|awk '{ sum+=$1} END {print sum}'`
done

$

Output of the script will look like the below

$ ./cpu.ksh
CPU usage at DATE: 03/02/10 TIME:05:06:52 on host ebizdb1
--------------------------------------------------------------
rmanprd1 usage= 7.3
ebizprd1 usage= 10.3
mfgprd1 usage= 47
ebizarc1 usage= 4
ebizarc2 usage= 9
asgprd1 usage= 0.1
asgprd2 usage= 0.8
$

From the above you can see mfgprd1 is the highest CPU consumer. This is pretty simple script but powerful in troubleshooting.
If you find any bug with the script let me know.

Hope this helps. As usual leave your comments.