How To Use AWR Data To Create Awesome Wait Event Based Histograms

Posted on 26-May-2015 by Craig Shallahamer,

In this post I will walk you through how to quickly and selectively pull dba_hist_event_histogram data and transform it so it can be quickly pasted into an R script creating two pages of histograms.

Many times I find myself solving Oracle performance problems that have already happened. Perhaps the incident occurred a few hours ago, yesterday, last week or even last month. But regardless, I have a nice juicy problem to solve.

If I need to understand a specific Oracle wait event timing situation in more depth than the average wait time, I'm in luck. That is, if either Statspack or AWR data is being collected and retained. That's because both AWR and Statspack store v$event_histogram data. In AWR, the table name is dba_hist_event_histogram.

In my previous post I walked you though how to interactively gather wait event timing details from v$event_histogram using my free OSM tool swEhVdGet.sql and creating a proper histograms using my R script swEhCharts.r. In this post, it's all about getting historical data from the AWR table dba_hist_event_histogram.

How to easily pull targeted dba_hist_event_histogram data, create some basic statistics and histograms, and then understand the information is what this post is all about!

I You Are Just Joining Us...

Over the past weeks I have blogged and made the focus of my Firefighting Friday "How To" Webinars about installing and using the free statistical package R primarily for understanding wait times for a specific wait event. We did this by examining some basic statistics and histograms. If you just joined this thread, you may want to review what I've already covered.

Here is a partial list of resources.

Gathering Wait Event Times To Create A Histogram

In my OSM Toolkit there are a number of tools to gather/report Oracle wait event times. I am going to focus on pulling the data out of the AWR table dba_hist_event_histogram using the swEhAwrGet.sql script and then copy/paste its output into the R script swEhCharts.r resulting in a magnificent two-page histogram montage. It's pretty cool.

The script has only two main parts. The first part helps you quickly pick the best AWR snap IDs. (If you want to develop your own set of AWR script, this first part of the script is a great place to start.) The second part, pulls the appropriate data from dba_hist_event_histogram and outputs it into a format so you can simply copy and paste the output directly in the swEhCharts.r script.

How To Use swEhAwrGet.sql

Suppose on 8 August between 2pm and 4pm overall, the Oracle system looked great. However, a couple very upset users called in saying the application all of a sudden froze solid (like a laptop incased in ice) for over a minute and then mysteriously released control. After using my BloodHound toolkit to analyze the historical AWR data stored in the dba_hist_active_sess_history table, I discovered the situation was exactly as the users described. That is, overall performance was great, but a few users where essentially "hung" waiting on a row level lock. The Oracle wait event was, enq: TX - row lock contention.

The objective now was to get a better understanding of the long but infrequent wait times and be able to explain the situation to others.

Select Your Snapshot ID Range

Below is the entire dialog from selecting the best AWR snap IDs to the final output ready to be pasted into the R script.

Notice first, the script shows me what AWR data is available. This happens very quickly as I pull directly from the indexed dba_hist_snapshot table.

SQL> @swEhAwrGet

---------- --------------- ------------- -------------------- ------------ --------------------
4217380439               1          2464 28-Dec-2014 16:00:07         3369 04-Feb-2015 09:00:43
6243270432               1         31089 12-Jul-2014 02:00:10        32532 11-Aug-2014 09:30:13
6243270432               2         31089 12-Jul-2014 02:00:10        32532 11-Aug-2014 09:30:13
6243270432               3         31089 12-Jul-2014 02:00:10        32532 11-Aug-2014 09:30:13

Rarely, do I know the exact snapshot IDs I want, so this script prompts me for the day and hour (not minute and seconds) when I'm interested. Then it provides the snapshot ID details for around this range. If find this very useful. And the script is very fast because, like before, it's pulling from dba_hist_snapshot.

ENTER the database ID (DBID)                                : 6243270432
ENTER the instance number                                   : 3
ENTER the approximate starting date/hour (DD-Mon-YYYY HH24) : 08-Aug-2014 12
ENTER the approximate ending   date/hour (DD-Mon-YYYY HH24) : 08-Aug-2014 15

---------- -------------------- --------------------
     32391 08-Aug-2014 11:00:11 08-Aug-2014 11:30:28
     32392 08-Aug-2014 11:30:28 08-Aug-2014 12:00:05
     32393 08-Aug-2014 12:00:05 08-Aug-2014 12:30:23
     32394 08-Aug-2014 12:30:23 08-Aug-2014 13:00:41
     32395 08-Aug-2014 13:00:41 08-Aug-2014 13:30:24
     32396 08-Aug-2014 13:30:24 08-Aug-2014 14:00:02
     32397 08-Aug-2014 14:00:02 08-Aug-2014 14:30:22
     32398 08-Aug-2014 14:30:22 08-Aug-2014 15:00:36
     32399 08-Aug-2014 15:00:36 08-Aug-2014 15:30:07

9 rows selected.

Generate The Raw Histogram Data & Review

In my situation, the users called in around 2pm. Looking at the above output, this means the starting snapshot ID of 32395 with an ending snapshot ID of 32397 should be fine. With the actual snapshot IDs and the specific wait event I'm interested in, I reply to the scripts' prompts like this:

ENTER the beginning snap_id                 : 32395
ENTER the ending    snap_id                 : 32397
ENTER the wait event (default: db%seq%read) : enq%TX%row%lock%

Copy and paste the below R code directly into the swEhCharts.r
It must be the last entry in the #2 area of swEhCharts.r

In a split second the below output is created. Regardless of how much AWR data is retained, the output is created extremely fast.

Let's look at the output for a bit. The output relates to the event histogram bins found in both v$event_histogram and dba_hist_event_histogram, which are in milliseconds. For example, within the snapshot ID interval, there were 4888 TX row level lock occurrences that lasted between 0ms and 1ms. Notice there are 7 wait occurrences that lasted over 1 second, with two of them over 65 seconds!

So perhaps our users are not such shlemiels after all?

### START ###

eName = "enq%TX%row%lock%"

count0to1 = 4888
count1to2 = 2354
count2to4 = 2052
count4to8 = 764
count8to16 = 347
count16to32 = 149
count32to64 = 5
count64to128 = 2
count128to256 = 3
count256to512 = 3
count512to1024 = 2
count1024to2048 = 1
count2048to4096 = 0
count4096to8192 = 0
count8192to16384 = 0
count16384to32768 = 3
count32768to65536 = 1
count65536to131072 = 2
count131072to262144 = 0

PL/SQL procedure successfully completed.

### END : Do NOT COPY and PASTE the "PL/SQL procedure..." TEXT INTO R ###

Copy & Paste The Output Into The OSM R Script

Next, in a text editor open the OSM R script, swEhCharts.r and scroll down to below the last example. Then copy the above output starting from the "### START ###" to just above "### END" line, being careful to NOT include the "PL/SQL..." line. Now paste that into the R script.

Next, copy and paste the entire R script directly into R. Now press ENTER and you will see two pages of carefully thought out histograms displayed before your eyes!

When you paste the R script into R and press ENTER, R will produce some basic statistics (i.e., mean and median) plus two histogram pages, detailing the wait event activity. Below are the statistics followed by the histograms.

> summary(binValues)
   Min.  1st Qu.  Median    Mean    3rd Qu.    Max. 
   0.00  0.54     1.17      32.38   2.67       129800.00 


Now you can easily create proper and beautiful Oracle wait event histograms based on dba_hist_event_histogram

What Are The Statistics And Histograms Telling Me?

Now that you have some statistics and some histograms, it's time to learn about the underlying data. While I'll provide some guidance, my online video seminar entitled, Using Skewed Performance Data To Your Advantage will teach you to be an expert!

Here are a few observations about the data:


My hope is you can calmly investigate shockingly large wait event times. To support that effort, with AWR or Statspack data, you are able to generate some basic statistics and create histograms. What I have found is the time required to do this is minimal, but the rewards are significant!

To keep this post from getting to long, I did not mention how to use an actual AWR or Statspack report to create proper wait event histograms. How to do that will be the topic of my next post.

Enjoy your work!


Start my FREE 18 lesson Machine Learning For Oracle Professionals E-Course here.

Craig Shallahamer is a long time Oracle DBA who specializes in predictive analytics, machine learning and Oracle performance tuning. Craig is a performance researcher and blogger, consultant, author of two books, an enthusiastic conference speaker a passionate teacher and an Oracle ACE Director. More about Craig Shallahamer...

If you have any questions or comments, feel free to email me directly at craig at

How To Use The Oracle ASH time_waited Column Four Options For Oracle DBA Tuning Training Comparing Oracle Database SQL Execution Times From Different Systems