Monitor the database activity in real time with R

A quick post to let you know that I just finished a R script to monitor the database activity in real time.

The “graph_real_time_db_activity.r” script (You can download it from this repository) basically takes a snapshot based on the v$system_event view then computes and graphs the differences with the previous snapshot.

One graph refreshed in real time is provided. It contains:

  • A sub-graph for the time waited (in ms) per wait class.

real_time_db_activity_time_values

  • A sub-graph for the wait events distribution of the wait class having the max time waited during the last snap.

real_time_db_activity_events_distribution

  • A sub-graph for the wait class distribution since the script has been launched.

real_time_db_activity_wait_class_distribution

The script also provides:

  • a text file that contains the snaps computations.
  • a pdf file that contains the final graph.

As you can see, for a better understanding of the database behavior, I also included a fake “CPU”  wait class (coming from the v$sys_time_model view) as suggested by Guy Harrison into this blog post.

The graph is generated to both outputs (X11 and the pdf file). In case the X11 environment does not work, the pdf file is generated anyway.

Let’s see the script in action:

./graph_real_time_db_activity.r
Building the thin jdbc connection string....

host ?:BDT_HOST
port ?:1521
service_name ?: BDT
system password ?:donotreadthis
Number of snapshots:50
Refresh interval (seconds):2
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
Please enter any key to exit:

As you can see you are prompted for:

  • jdbc thin “like” details to connect to the database (You can launch the R script outside the host hosting the database).
  • oracle system user password.
  • Number of snapshots.
  • Refresh Interval.

So you can choose the number of snapshots and the graph refresh interval.

The output is like:

real_time_db_activity_wait_class_all

Remarks:

  • The script does not create any objects into the database.
  • If you want to install R, a good staring point is into the “Getting Staring” section of this link.
  • Now that I am able to graph in real time with R, my next work is to graph in real time the metrics coming from my asmiostat utility. I’ll keep you posted.
Advertisements
This entry was posted in R scripts. Bookmark the permalink.

One Response to Monitor the database activity in real time with R

  1. Ravin Maharaj says:

    Hi Bertrand,
    I am new to R. I have installed the R-Studio on my Windows 8.1 Laptop.
    As per one of your previous posts I do not need to execute these utilities written in R on the database host
    Please provide instructions on how I can execute these utilities from my Windows laptop
    Thanking You in anticipation

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