Real-Time CPU utilization per oracle database

Suppose you have a lot of oracle databases running on the same host, the server is almost 100% cpu used and you would like to know which database is the top cpu consumer right now.

The well known “top” utility could help ? This is not sure in case the culprit database has a lot of active sessions as “top” works at the process level.

So, to answer this need, I wrote a perl script that collects real-time processes cpu consumption at the os level (the script does not connect to any database) and aggregate the result per database.

The script takes a snapshot of os processes each second (default interval), computes the differences with the previous snapshot and aggregate te result per database.

Let’s see an example of the os_cpu_per_db.pl perl script (click on the link and then on the view source button to copy/paste the source code):

./os_cpu_per_db.pl 

Collecting during 1 seconds......

                               SUMMARY PER DB

20:31:56       DB_NAME		CPU_SEC		NB_CPU    
20:31:56       MYDB1		0               0.0
20:31:56       MYDB3		2		2.0
20:31:56       MYDB2		23		23.0

So we find out our top oracle database cpu consumer: the MYDB2 oracle database spent 23 seconds into the cpus during the last second (default collection interval) that is to say used 23 cpus.

Once you have the top database cpu consumer you can connect to it and diagnose more in depth (Example of cpu spike on this post).

The script does a little bit more in case the cpu issue is not related to any oracle database:

  • It can display the cpu usage per os user.
  • It can display the top os PID and commands.

Let’s see the help:

./os_cpu_per_db.pl help                       

Usage: ./os_cpu_per_db.pl [Interval [Count]] [top=] [displayuser=[Y|N]] [displaycmd=[Y|N]] 

        Default Interval : 1 second.
        Default Count    : Unlimited

Parameter               Comment					Default    
---------               -------      		                -------         
TOP=                    Number of rows to display               10         
DISPLAYUSER=		REPORT ON USER TOO                      N          
DISPLAYCMD=             REPORT ON COMMAND TOO                   N
  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You have to set .oraenv on one instance first.
  • The script has been tested on Linux and Solaris.
Advertisements
This entry was posted in Perl Scripts, ToolKit and tagged , . Bookmark the permalink.

One Response to Real-Time CPU utilization per oracle database

  1. Charles FAKHRY says:

    ps aux | sort -nk +3 | tail -10 🙂 kidding ..

    good job BD

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s