In one of my previous post I provided a R script to retrieve and visualize system statistics from AWR. Now with this post I will provide a R script to retrieve and visualize the same metrics in real time.

For that purpose I created a R script “graph_real_time_sysstat.r” (You can download it from this repository)  that provides:

  1. A graph for the VALUE metric refreshed in real time.
  2. A graph for the VALUE PER SECOND metric refreshed in real time.
  3. A graph for the Histogram of VALUE refreshed in real time.
  4. A graph for the Histogram of VALUE PER SECOND refreshed in real time.
  5. A pdf file that contains those graphs.
  6. A text file that contains the output of the query used to build the graphs.

Basically the script takes a snapshot based on the v$sysstat view then computes and graphs the differences with the previous snapshot.

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. In that particular case the pdf file contains a page per snapshot.

Let’s see the script in action:

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

host ?: bdt_host
port ?: 1521
service_name ?: BDT
system password ?: donotreadthis
Display which sysstat (no quotation marks) ?: physical reads
Number of snapshots: 60
Refresh interval (seconds): 20
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.
  • The statistic we want to focus on.
  • Number of snapshots.
  • Refresh Interval.

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

When the number of snapshots is reached the output is like:

real_time_physical_reads

The graphs sequence that leads to this final graph is the following (see the real_time_physical_reads pdf file).

Remarks:

  • All the points will be graphed (No points will be moved outside the graph), even if:

  • For lisibility the X axis could contains not all the ticks.

  • 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 from this link.

  • The graphs “value” and “value_per_sec” should looks like the same: It may not be the case if the snaps are not sampled at regular interval (unexpected delay during the collection).

Conclusion:

We now have 4 scripts at our disposal to retrieve and display with R:

  • for a particular system statistic:
  1. real time metrics (script described into this post)
  2. AWR based metrics (script described this post)
  • and also for a particular wait event:
  1. real time metrics (script described this post)
  2. AWR based metrics (script described this post)