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.

9 comments:

Rajesh S. Sawadatkar said...

Hi Suresh,
Rajesh Sawadatkar this side, I am working in Reliance Globalcom ltd as oracle Apps DBA.
Please accept my chat invitation

Thanks & Regards
Rajesh S. Sawadatkar

Karthikeyan K said...

I tried in RHEL.
It does not bring any o/p
Whats needs to be changed?

Suresh Lakshmanan said...

Hi Karthikeyan,

Please try this.



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

for dbname in `ps -ef|grep pmon|awk '{print $8}'|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


Suresh

Anonymous said...

Thanks Suresh, It was really helpful.

Cheers
Karthik

Anonymous said...

Excellent ,Suresh.It will very useful
After two days of searching I found a script for CPUusage for different databases on a shared server in your blog. Excellent, Honestly I tried for searching the script for one day; finally I got it in your blog. Thank you so much for the script. If you get a chance can you please add how to collect the Memory usage and IO usage also

Anonymous said...

Great stuff Suresh, but i have a worry in my case using the script for databases with similar names.

here is an example:

I have three database naming: test,testsal,testops. when i try the above script I get the following results:

$ ./cpu.ksh
CPU usage at DATE:
--------------------------------------------------------------
test usage= 20 --- > not right
testsal usage= 10
testops usage= 15


here the cpu used by test db is only 5 but the output includes all three db results for test db.

Is there a way to get accurate output for dbs' with matching names.

thanks for your help in advance.

Anonymous said...

Great stuff Suresh, but i have a worry using the script for databases with similar names.

here is an example:

I have three database naming: test,testsal,testops. when i try the above script I get the following results:

$ ./cpu.ksh
CPU usage at DATE:
--------------------------------------------------------------
test usage= 20 --- > not right
testsal usage= 10
testops usage= 15


here the cpu used by test is only 5 but the script output includes all three db results.

Is there a way to get accurate output for dbs' with matching names.

thanks for your help in advance.

Anonymous said...

Great stuff Suresh, but i have a worry using the script for databases with similar names.

here is an example:

I have three database naming: test,testsal,testops. when i try the above script I get the following results:

$ ./cpu.ksh
CPU usage at DATE:
--------------------------------------------------------------
test usage= 20 --- > not right
testsal usage= 10
testops usage= 15


here the cpu used by test is only 5 but the script output includes all three db results.

Is there a way to get accurate output for dbs' with matching names.

thanks for your help in advance.

Suhas Dwarakanath said...

Phenomenal! Thanks a million!