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:

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)

Advertisements
This entry was posted in Perl Scripts, ToolKit and tagged , . Bookmark the permalink.

14 Responses to Real-Time database utilities grouped into a single script

  1. jeanmichel says:

    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

    • bdrouvot says:

      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

  2. Max says:

    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.

  3. JMA says:

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

    • Max says:

      Bertrand, yes the problem have been with copy/paste. I’ve downloaded through google docs and script works fine!

      Thx.

      • 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.

  4. goryszewskig says:

    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

    • bdrouvot says:

      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

      • goryszewskig says:

        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

      • bdrouvot says:

        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

  5. fouedgray says:

    Merci Bertrand 🙂

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