Real-Time database utilities grouped into a single script

In most of my previous posts I provided some perl scripts used to collect real-time information from the database based on cumulative views.

Those cumulative views provide a lot of useful information but are useless when real-time information is needed.

So, the idea of those utilities is more or less to take a snapshot each second (default interval) of the cumulative views and compute the differences with the previous snapshot to get real-time information.

I aggregated those perl scripts into a single one (real_time.pl) (Click on the link, and then on the view source button and then copy/paste the source code or download it from my shared directory here) :

You can choose the number of snapshots to display and the time to wait between snapshots.
This script is oracle RAC aware: you can work on all the instances, a subset or the local one.
You have to set oraenv on one instance of the database you want to diagnose first.
The script has been tested on Linux, Unix and Windows.

Let’s have a look to the main help:

 ./real_time.pl -help

Usage: ./real_time.pl [-interval] [-count] [-type] [-help]
 Default Interval : 1 second.
 Default Count    : Unlimited

  Parameter      Value                        Comment
  ---------      -------                      -------
  -type          sysstat                      Real-Time snaps extracted from gv$sysstat
                 system_event                 Real-Time snaps extracted from gv$system_event
                 event_histogram              Real-Time snaps extracted from gv$event_histogram
                 sgastat                      Real-Time snaps extracted from gv$sgastat
                 enqueue_statistics           Real-Time snaps extracted from gv$enqueue_statistics
                 librarycache                 Real-Time snaps extracted from gv$librarycache
                 segments_stats               Real-Time snaps extracted from gv$segstat
                 sess_event                   Real-Time snaps extracted from gv$session_event and gv$session
                 sess_stat                    Real-Time snaps extracted from gv$sesstat and gv$session

  -help          Print the main help or related to a type (if not empty)                                                                                

  Description:
  -----------
  Utility used to display real time informations based on cumulative views
  It basically takes a snapshot each second (default interval) of the cumulative view and computes the differences with the previous snapshot
  It is oracle RAC aware: you can work on all the instances, a subset or the local one
  You have to set oraenv on one instance of the database you want to diagnose first
  You can choose the number of snapshots to display and the time to wait between snapshots

Example: ./real_time.pl -type=sysstat -help
Example: ./real_time.pl -type=system_event -help

So, as you can see it allows to deal with some type of snapshots, that is to say:

sysstat                  Real-Time snaps extracted from gv$sysstat
system_event             Real-Time snaps extracted from gv$system_event
event_histogram          Real-Time snaps extracted from gv$event_histogram
sgastat                  Real-Time snaps extracted from gv$sgastat
enqueue_statistics       Real-Time snaps extracted from gv$enqueue_statistics
librarycache             Real-Time snaps extracted from gv$librarycache
segments_stats           Real-Time snaps extracted from gv$segstat
sess_event               Real-Time snaps extracted from gv$session_event and gv$session
sess_stat                Real-Time snaps extracted from gv$sesstat and gv$session

Each type has its own help associated.

To get more help about a particular type, just call the help with the associated type:

./real_time.pl -type=sysstat -help

Usage: ./real_time.pl -type=sysstat [-interval] [-count] [-inst] [-top] [-statname] [-help]
 Default Interval : 1 second.
 Default Count    : Unlimited

  Parameter         Comment                                                      Default
  ---------         -------                                                      -------
  -INST=            ALL - Show all Instance(s)                                   ALL
                    CURRENT - Show Current Instance
                    INSTANCE_NAME,... - choose Instance(s) to display

  -TOP=             Number of rows to display                                    10
  -STATNAME=        ALL - Show all STATS (wildcard allowed)                      ALL

Example: ./real_time.pl -type=sysstat
Example: ./real_time.pl -type=sysstat -inst=BDTO_1,BDTO_2
Example: ./real_time.pl -type=sysstat -statname='bytes sent via SQL*Net to client'
Example: ./real_time.pl -type=sysstat -statname='%bytes%'

Conclusion:

There is no need anymore to use the “individuals” perl scripts provided so far, as they have been grouped into this main utility.

Two perl scripts still remain outside this main utility as they are not related to database cumulative views, that is to say:

exadata_metrics.pl : This perl script is used to collect exadata cell’s real-time metrics based on cumulative metrics. You can find a practical case in the Exadata real-time metrics extracted from cumulative metrics post.
os_cpu_per_db.pl : This perl script is used to collect real-time cpu utilization per database . You can find a practical case in the Real-Time CPU utilization per oracle database post.

Please do not hesitate to give your feedback and report any issues you may found.

Update: real_time.pl now contains asmiostat type (see this post)