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:

        1. A graph for the TIME_WAITED_MS metric over the period of time
        2. A graph for the NB_WAITS metric over the period of time
        3. A graph for the MS_PER_WAIT metric over the period of time
        4. A graph for the Histogram of MS_PER_WAIT over the period of time
        5. A pdf file that contains those graphs
        6. 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:

db_file_sequential_read

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:

About these ads
This entry was posted in R scripts. Bookmark the permalink.

One Response to Retrieve and visualize wait events metrics from AWR with R

  1. flashdba says:

    This is really cool… I need to get myself into R

    Thanks for sharing!

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