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.

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

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

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:

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.

Retrieve and visualize in real time wait events metrics with R

In one of my previous post I provided a R script to retrieve and visualize wait events metrics 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_event.r” (You can download it from this repository) that provides:

A graph for the TIME_WAITED_MS metric refreshed in real time.
A graph for the NB_WAITS metric refreshed in real time.
A graph for the MS_PER_WAIT metric refreshed in real time.
A graph for the Histogram of MS_PER_WAIT refreshed in real time.
A pdf file that contains those graphs.
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$system_event 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_event.r
Building the thin jdbc connection string....

host ?: bdt_host
port ?: 1521
service_name ?: BDT
system password ?: donotreadthis
Display which system event (no quotation marks) ?: db file sequential read
Number of snapshots: 60
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.
The wait event 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:

The graphs sequence that leads to this final graph is the following (see the real_time_db_file_sequential_read 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.
If you just want a text output in real time then see this blog post.

Conclusion:

We are now able to retrieve and display wait events metrics with R from AWR (see the previous post) and in real time.

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.

Retrieve and visualize ASM Disk Group Usage per database with R

If you are using Oracle Enterprise Manager and ASM, then you may already be familiar with the following page that displays the Disk Group Usage per database for a particular Disk Group:

And it is most likely that you don’t need to read this little post, as it provides a R script to achieve more or less the same result ;-).

But if you are not using Oracle Enterprise Manager, then you may find this post useful.

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

A graph similar to the OEM one.
A pdf file that contains the graph.
A text file that contains the values used to build the graph.

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

Let’s launch it to see the result:

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

host ?: bdt_host
port ?: 1521
service_name for ASM ?: +ASM
sys as sysasm password ?: dontreadthis
Display which disk group (no quotation marks, no +) ?: ARCHIVE
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
Please enter any key to exit:

The output is the following:

As you can see you are prompted for:

jdbc thin “like” details to connect to the ASM instance (You can launch the R script outside the host hosting the ASM instance).
oracle sys user password.
The ASM disk group you want to focus on.

Remarks:

It has been tested on a 11.2.0.3 asm instance.
If you want to install R, you should begin to read “Getting Starting” from this link.

Retrieve and visualize system statistics metrics from AWR with R

Into my last post I gave a way to Retrieve and visualize wait events metrics from AWR with R, now it’s time for the system statistics.

So, for a particular system statistic, I’ll retrieve from the dba_hist_sysstat view:

Its VALUE between 2 snaps
Its VALUE per second between 2 snaps

As the VALUE is cumulative, I need to compute the difference between 2 snaps that way:

SQL> !cat check_awr_stats.sql
set linesi 200
col BEGIN_INTERVAL_TIME format a28
col stat_name format a40

alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

select s.begin_interval_time,sta.stat_name,sta.VALUE,
--round(((sta.VALUE)/(to_date(s.end_interval_time)-to_date(s.begin_interval_time)))/86400,2) VALUE_PER_SEC_NOT_ACCURATE,
round(((sta.VALUE)/
(
(extract(day from s.END_INTERVAL_TIME)-extract(day from s.BEGIN_INTERVAL_TIME))*86400 +
(extract(hour from s.END_INTERVAL_TIME)-extract(hour from s.BEGIN_INTERVAL_TIME))*3600 +
(extract(minute from s.END_INTERVAL_TIME)-extract(minute from s.BEGIN_INTERVAL_TIME))*60 +
(extract(second from s.END_INTERVAL_TIME)-extract(second from s.BEGIN_INTERVAL_TIME))
)
),2) VALUE_PER_SEC
from
(
select instance_number,snap_id,stat_name,
value - first_value(value) over (partition by stat_name order by snap_id rows 1 preceding) "VALUE"
from
dba_hist_sysstat
where stat_name like nvl('&stat_name',stat_name)
and instance_number = (select instance_number from v$instance)
) sta, dba_hist_snapshot s
where sta.instance_number=s.instance_number
and sta.snap_id=s.snap_id
and s.BEGIN_INTERVAL_TIME >= trunc(sysdate-&sysdate_nb_day_begin_interval+1)
and s.BEGIN_INTERVAL_TIME <= trunc(sysdate-&sysdate_nb_day_end_interval+1)
order by s.begin_interval_time asc;

I use the “partition by stat_name order by snap_id rows 1 preceding” to compute the difference between snaps par stat_name.

I also use Extract to get an accurate value per second, you should read this blog post to understand why.

The output is like:

SQL> @check_awr_stats.sql

Session altered.

Session altered.

Enter value for stat_name: physical reads
old  17: where stat_name like nvl('&stat_name',stat_name)
new  17: where stat_name like nvl('physical reads',stat_name)
Enter value for sysdate_nb_day_begin_interval: 7
old  22: and s.BEGIN_INTERVAL_TIME >= trunc(sysdate-&sysdate_nb_day_begin_interval+1)
new  22: and s.BEGIN_INTERVAL_TIME >= trunc(sysdate-7+1)
Enter value for sysdate_nb_day_end_interval: 0
old  23: and s.BEGIN_INTERVAL_TIME <= trunc(sysdate-&sysdate_nb_day_end_interval+1)
new  23: and s.BEGIN_INTERVAL_TIME <= trunc(sysdate-0+1)

BEGIN_INTERVAL_TIME          STAT_NAME                                     VALUE VALUE_PER_SEC
---------------------------- ---------------------------------------- ---------- -------------
2013/03/21 00:00:12          physical reads                              1363483       1132.11
2013/03/21 00:20:17          physical reads                               260228        216.04
2013/03/21 00:40:21          physical reads                                29573         24.56
2013/03/21 01:00:25          physical reads                               231492        192.18
2013/03/21 01:20:30          physical reads                               494749         410.7
2013/03/21 01:40:35          physical reads                               232803        193.02
2013/03/21 02:00:41          physical reads                               318803        264.66
2013/03/21 02:20:45          physical reads                              1253398       1039.57
2013/03/21 02:40:51          physical reads                              2064294       1711.98
2013/03/21 03:00:57          physical reads                               503404        439.13
2013/03/21 03:20:03          physical reads                               138052        114.59

So if you use this sql, you’ll be able to see for a particular system statistic its historical behaviour. That’s fine and I used it a lot of times.

But I like also to have a graphical view of what’s going on, and that is exactly where R comes into play.

I created a R script named: graph_awr_sysstat.r (You can download it from this repository) that provides:

A graph for the VALUE metric over the period of time
A graph for the VALUE per second metric over the period of time
A graph for the Histogram of VALUE over the period of time
A graph for the Histogram of VALUE per second over the period of time
A pdf file that contains those graphs
A text file that contains the metrics used to build the graphs

The graphs will come from both outputs (X11 and the pdf file). In case the X11 environment does not work, the pdf file is generated anyway.

As a graphical view is better to understand, let’s have a look how it works and what the display is:

For example, let’s focus on the “physical reads” system statistic over the last 7 days that way:

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

host ?: bdt_host
port ?: 1521
service_name ?: bdt
system password ?: XXXXXXXX
Display which sysstat (no quotation marks) ?: physical reads
Please enter nb_day_begin_interval: 7
Please enter nb_day_end_interval: 0
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
[1] 11
Please enter any key to exit:

The output will be like:

and the physical_reads pdf file will be generated as well.

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 days to go back as the time frame starting point
Number of days to go back as the time frame ending point

Remarks:

– You need to purchase the Diagnostic Pack in order to be allowed to query the AWR repository.

– If the script has been launched with X11 not working properly, you’ll get:

Loading required package: methods
Loading required package: DBI
Loading required package: rJava
[1] "Not able to display, so only pdf generation..."
Warning message:
In x11(width = 15, height = 10) :
  unable to open connection to X11 display ''
[1] 11
Please enter any key to exit:

But the script takes care of it and the pdf file will be generated anyway.

Conclusion:

We are able to display graphically AWR historical metrics for a particular statistic over a period of time with the help of a single script named: graph_awr_sysstat.r (You can download it from this repository).

If you don’t have R installed:

you can use the sql provided at the beginning of this post to get at least a “text” view of the historical metrics.
Install it 🙂 (See “Getting Starting” from this link)

Update: If you want to see the same metrics in real time then you could have a look to this post.

Retrieve and visualize wait events metrics from AWR with R

In this post I will provide a way to retrieve wait events metrics from AWR and to display graphically those metrics thanks to R over a period of time.

Why R ?: Because R is a powerful tool for statistical analysis with graphing and plotting packages built in. Furthermore, R can connect to Oracle via a JDBC package which makes importing data very easy.

So, for a particular wait event, I’ll retrieve from the dba_hist_system_event view:

TIME_WAITED_MS: Time waited in ms between 2 snaps
TOTAL_WAITS: Number of waits between 2 snaps
MS_PER_WAIT: Avg wait time in ms between 2 snaps

As those metrics are cumulative ones, I need to compute the difference between 2 snaps that way:

SQL> !cat check_awr_event.sql
set linesi 220;

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

col BEGIN_INTERVAL_TIME format a28
col event_name format a40
col WAIT_CLASS format a20
set pagesi 999

select distinct(WAIT_CLASS) from v$system_event;

select e.WAIT_CLASS,e.event_name,s.begin_interval_time,e.TOTAL_WAITS,e.TIME_WAITED_MS,e.TIME_WAITED_MS / TOTAL_WAITS "MS_PER_WAIT"
from
(
select instance_number,snap_id,WAIT_CLASS,event_name,
total_waits - first_value(total_waits) over (partition by event_name order by snap_id rows 1 preceding) "TOTAL_WAITS",
(time_waited_micro - first_value(time_waited_micro) over (partition by event_name order by snap_id rows 1 preceding))/1000 "TIME_WAITED_MS"
from
dba_hist_system_event
where
WAIT_CLASS like nvl('&WAIT_CLASS',WAIT_CLASS)
and event_name like nvl('&event_name',event_name)
and instance_number = (select instance_number from v$instance)
) e, dba_hist_snapshot s
where e.TIME_WAITED_MS > 0
and e.instance_number=s.instance_number
and e.snap_id=s.snap_id
and s.BEGIN_INTERVAL_TIME >= trunc(sysdate-&sysdate_nb_day_begin_interval+1)
and s.BEGIN_INTERVAL_TIME <= trunc(sysdate-&sysdate_nb_day_end_interval+1) order by 1,2,3;

I use the “partition by event_name order by snap_id rows 1 preceding” to compute the difference between snaps per event.

The output is like:

SQL> @check_awr_event.sql

Session altered.

WAIT_CLASS
--------------------
Administrative
Application
Commit
Concurrency
Configuration
Idle
Network
Other
System I/O
User I/O

10 rows selected.

Enter value for wait_class:
old  10: WAIT_CLASS like nvl('&WAIT_CLASS',WAIT_CLASS)
new  10: WAIT_CLASS like nvl('',WAIT_CLASS)
Enter value for event_name: db file sequential read
old  11: and event_name like nvl('&event_name',event_name)
new  11: and event_name like nvl('db file sequential read',event_name)
Enter value for sysdate_nb_day_begin_interval: 7
old  17: and s.BEGIN_INTERVAL_TIME >= trunc(sysdate-&sysdate_nb_day_begin_interval+1)
new  17: and s.BEGIN_INTERVAL_TIME >= trunc(sysdate-7+1)
Enter value for sysdate_nb_day_end_interval: 0
old  18: and s.BEGIN_INTERVAL_TIME <= trunc(sysdate-&sysdate_nb_day_end_interval+1) order by 1,2,3
new  18: and s.BEGIN_INTERVAL_TIME <= trunc(sysdate-0+1) order by 1,2,3

WAIT_CLASS           EVENT_NAME                               BEGIN_INTERVAL_TIME          TOTAL_WAITS TIME_WAITED_MS MS_PER_WAIT
-------------------- ---------------------------------------- ---------------------------- ----------- -------------- -----------
User I/O             db file sequential read                  20-MAR-13 12.00.45.270 AM         286608     271639.345  .947773073
User I/O             db file sequential read                  20-MAR-13 12.20.49.821 AM          32759     125296.732  3.82480332
User I/O             db file sequential read                  20-MAR-13 12.40.54.540 AM           4404       8946.577  2.03146617
User I/O             db file sequential read                  20-MAR-13 01.00.58.981 AM           3617       4737.182   1.3096992
User I/O             db file sequential read                  20-MAR-13 01.20.03.039 AM          22624      94671.254  4.18454977
User I/O             db file sequential read                  20-MAR-13 01.40.07.163 AM          94323     181118.282  1.92019213
User I/O             db file sequential read                  20-MAR-13 02.00.11.636 AM         119458     317204.205  2.65536176
User I/O             db file sequential read                  20-MAR-13 02.20.16.040 AM          81720     212678.865  2.60253139
User I/O             db file sequential read                  20-MAR-13 02.40.20.827 AM          61664     120446.947   1.9532782
User I/O             db file sequential read                  20-MAR-13 03.00.25.531 AM          92493     110715.902  1.19701926
User I/O             db file sequential read                  20-MAR-13 03.20.29.923 AM           2692       6102.149  2.26677155

So if you use this sql, you’ll be able to see for a particular event its historical behaviour. That’s fine and I used it a lot of times.

But I like also to have a graphical view of what’s going on, and that is exactly where R comes into play.

I created a R script named: graph_awr_event.r (You can download it from this repository) that provides:

A graph for the TIME_WAITED_MS metric over the period of time
A graph for the NB_WAITS metric over the period of time
A graph for the MS_PER_WAIT metric over the period of time
A graph for the Histogram of MS_PER_WAIT over the period of time
A pdf file that contains those graphs
A text file that contains the metrics used to build the graphs

The graphs will come from both outputs (X11 and the pdf file). In case the X11 environment does not work, the pdf file is generated anyway.

As a graphical view is better to understand, let’s have a look how it works and what the display is:

For example, let’s focus on the “db file sequential read” wait event over the last 7 days that way:

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

host ?: bdt_host
port ?: 1521
service_name ?: bdt
system password ?: XXXXXXXX
Display which event (no quotation marks) ?: db file sequential read
Please enter nb_day_begin_interval: 7
Please enter nb_day_end_interval: 0
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
[1] 11
Please enter any key to exit:

The output will be like:

and the db_file_sequential_read pdf file will be generated as well.

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 wait event we want to focus on
Number of days to go back as the time frame starting point
Number of days to go back as the time frame ending point

Remarks:

– You need to purchase the Diagnostic Pack in order to be allowed to query the AWR repository.

– If the script has been launched with X11 not working properly, you’ll get:

Loading required package: methods
Loading required package: DBI
Loading required package: rJava
[1] "Not able to display, so only pdf generation..."
Warning message:
In x11(width = 15, height = 10) :
  unable to open connection to X11 display ''
[1] 11
Please enter any key to exit:

But the script takes care of it and the pdf file will be generated anyway.

Conclusion:

We are able to display graphically AWR historical metrics for a particular wait event over a period of time with the help of a single script named: graph_awr_event.r (You can download it from this repository).

If you don’t have R installed:

you can use the sql provided at the beginning of this post to get at least a “text” view of the historical metrics.
Install it 😉 (See “Getting Starting” from this link)

Updates: