Real-Time database utilities grouped into a single script

bdrouvot Perl Scripts, ToolKit 30/01/201315/02/2013

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)

Published by bdrouvot

View all posts by bdrouvot

Published 30/01/201315/02/2013

16 thoughts on “Real-Time database utilities grouped into a single script”

jeanmichel says:

31/01/2013 at 8:45 am

Dear dba colleague,
I was wondering if your new perl script needs to create a specific schema in the database or is it a tanel-poder-snapper like script?
Thanks a lot.
We here need such tools because we do not have the money to buy awr or the diagnostic pack.

Salutations
Jean michel ALZINGRE

Reply
1. bdrouvot says:
  
  31/01/2013 at 9:26 am
  
  Hello Jean Michel,
  
  This utility does not create any objects into the database as it uses perl’s arrays to store the snaps.
  Please do not hesitate to come back to me if you have any questions or issues related to it.
  
  Thx
  Bertrand
  
  Reply
Max says:

31/01/2013 at 4:33 pm

Hi there. I’ve downloaded you script real_time.pl. He have many errors.

#perl -c real_time.pl

Bareword found where operator expected at real_time.pl line 210, near “%sort_fielNHITS from”
(Missing operator before from?)
Bareword found where operator expected at real_time.pl line 210, near “$librarycache where”
(Missing operator before where?)
Number found where operator expected at real_time.pl line 210, near “where 1”
(Do you need to predeclare where?)
String found where operator expected at real_time.pl line 214, near “$report_format_header=””
(Might be a runaway multi-line “” string starting on line 210)
(Missing semicolon on previous line?)
Bareword found where operator expected at real_time.pl line 214, near “%02d”
(Missing operator before d?)
Bareword found where operator expected at real_time.pl line 214, near “%02d”
(Missing operator before d?)
Bareword found where operator expected at real_time.pl line 214, near “%02d”
(Missing operator before d?)
Bareword found where operator expected at real_time.pl line 256, near “02d”
(Missing operator before d?)
Bareword found where operator expected at real_time.pl line 256, near “%02d”
(Missing operator before d?)
Global symbol “%sort_fielNHITS” requires explicit package name at real_time.pl line 210.
syntax error at real_time.pl line 210, near “%sort_fielNHITS from gv”
Global symbol “$librarycache” requires explicit package name at real_time.pl line 210.
Global symbol “$segstat” requires explicit package name at real_time.pl line 232.
Global symbol “$re” requires explicit package name at real_time.pl line 254.
syntax error at real_time.pl line 256, near “$report_format_values=”%02d:%”
(Might be a runaway multi-line %% string starting on line 254)
Unmatched right curly bracket at real_time.pl line 266, at end of line
syntax error at real_time.pl line 266, near “}”
Unmatched right curly bracket at real_time.pl line 267, at end of line
syntax error at real_time.pl line 331, near “>”
real_time.pl has too many errors.

Reply
1. bdrouvot says:
  
  31/01/2013 at 5:24 pm
  
  Hi,
  
  I don’t have any errors, did you follow one of this procedure ?
  
  1) Click on the link, and then on the view source button and then copy/paste the source code
  or
  2) download it from https://docs.google.com/folder/d/0B7Jf_4JdsptpRHdyOWk1VTdUdEU/edit
  
  Thx
  Bertrand
  
  Reply
  1. Max says:
    
    31/01/2013 at 5:31 pm
    
    I’ve copied source code through source button. But i see errors describe above.
  2. bdrouvot says:
    
    31/01/2013 at 6:08 pm
    
    Looks like you have an issue with the way you paste it. Try the 2) option : download it from my shared directory then no copy/paste needed.
JMA says:

31/01/2013 at 7:25 pm

Hi Bertrand,
Your perl script worked perfectly for me with no errors.
Thank you for this useful tool.
Jean-michel A.

Reply
1. Max says:
  
  31/01/2013 at 7:44 pm
  
  Bertrand, yes the problem have been with copy/paste. I’ve downloaded through google docs and script works fine!
  
  Thx.
  
  Reply
  1. Tanel Poder (@TanelPoder) says:
    
    06/02/2013 at 1:13 pm
    
    Yep, that’s been a pretty common problem with Snapper too – when people copy & paste it, then some editors mess the pasted text up and the script throws weird errors. But once downloaded with “save as…” or wget, it works ok.
goryszewskig says:

02/02/2013 at 9:01 am

Thank You indeed . Colud You tell me if
use DBI;
use DBD::Oracle qw(:ora_session_modes);

those modules are installed by default ?
Regards
GregG

Reply
1. bdrouvot says:
  
  02/02/2013 at 9:56 am
  
  Hello,
  
  It comes preconfigured as of 10g rdbms.
  The “BEGIN” section of the script put the necessary path and variables in place and launch the perl associated with the ORACLE_HOME.
  So just launch it that way : ./real_time.pl and it should work.
  
  Thx
  Bertrand
  
  Reply
  1. goryszewskig says:
    
    02/02/2013 at 10:47 am
    
    You right its working out of the clipboard :).
    Any plans of adding per sid monitoring or I’m just missing some options in current version ?
    Regards
    GregG
  2. bdrouvot says:
    
    02/02/2013 at 2:13 pm
    
    You can filter on sid with type=sess_event or type=sess_stat
    You can get the help with : ./real_time.pl -type=sess_event -help for example.
    
    If you have idea to filter on sid on other informations, please let me know 🙂
    
    Thx
    Bertrand
fouedgray says:

28/03/2016 at 8:07 pm

Merci Bertrand 🙂

Reply
1. Kamran Irshad says:
  
  21/05/2018 at 3:46 pm
  
  Hi
  I am getting the error
  $ ./real_time.pl -help
  : No such file or directory
  
  What could be wrong here?
  
  Reply
  1. bdrouvot says:
    
    21/05/2018 at 4:53 pm
    
    Hi,
    Try to launch it that way:
    
    perl ./real_time.pl