Oracle Scratchpad

January 31, 2007

Event Histograms – 2

Filed under: Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 8:38 pm BST Jan 31,2007

[Forward to part 3]

In an earlier blog [Part 1], I mentioned the 10g view v$event_histogram and described how useful it could be.

If you don’t have the licences that allow you to use the Automatic Workload Repository (AWR) in 10g, you will have noticed that the event histogram has made its way into Statspack for 10g.

In fact, 10.1 and 10.2 have different ways of presenting the information – so it looks like there’s life in the old package yet.

Here’s a 10.1.0.3 extract from the event histogram section:

Event
--------------------------------------------------
    0 - 1 ms     1 - 4 ms    4 - 8 ms    8 - 16 ms    16 - 32 ms      32+ ms
------------ ------------ ------------ ------------ ------------ ------------
db file sequential read
      61,007      224,777      979,871      818,944      363,765      229,022    

And  the 10.2.0.2 version. It’s a matter of taste, of course, whether you prefer the denser packing of the 10.2.0.2 version, or the absolute values of the 10.1.0.3 version. I prefer the tidier tabulation, which also gives room for a slightly greater range of timings from the histogram, and a certain consistency of style in the numbers:

                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  &<=1s   <1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
buffer busy waits          8481   21.8    .8    .8   1.2    .7    .5  74.3
db file sequential read    3408K   4.8    .7   4.7  14.7   6.9   5.1  51.3  11.7   

Other parts of the Statspack report have evolved at the same time. For example, the Top-5 section from 10.1.0.3:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                      % Total
Event                                               Waits    Time (s) Call Time
db file sequential read                         2,677,441      37,239     23.57

Followed by the equivalent from a 10.2.0.2 instance, which conveniently shows the average wait time in the Top-5 list, rather than forcing you to work it out in your head or cross-check it with the main event listing:

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read                      3,420,809   1,302,453    381   80.7

If you’re wondering about the figures – both sets came from a one-hour snapshot. Both systems have performance issues relating to I/O; one of them is just a bit overloaded, the other has a slightly more interesting problem.

[Back to part 1][Forward to part 3]

6 Comments »

  1. 3.4 Mwaits on dfsr in one hour! Man, that reminds me of the RH3/9ir2 database at my previous job…

    Comment by Noons — February 1, 2007 @ 2:55 am BST Feb 1,2007 | Reply

  2. Someone called Rita left a question asking if I could elaborate on the problems these two sites had. Unfortunately this got marked as spam, and I deleted it by accident when clearing the spam list.

    So – the first site was literally just working too hard. Modifying a couple of indexes to better precision, and fixing a couple of very popular queries, then adding on a stack of extra disks was sufficient to deal with the overload. If you could see the whole histogram, you would see the classic “long tail” indicative of typical queueing load.

    In the second case, a hardware problem made some of the disc response times very bad. It was a long time ago amd I don’t remember the details but it might have been a microcode error in the caching algorithms. The problem went away after the disc manufacturer installed a patch. You get a hint about this because the histogram suggests a bell curve centred on the 8ms area, with a sudden spike and tail off further to the right.

    Comment by Jonathan Lewis — February 2, 2007 @ 5:37 pm BST Feb 2,2007 | Reply

  3. Hi

    In AWR reports there is no event histogram statistics!

    Comment by David Lopez — May 28, 2007 @ 11:02 am BST May 28,2007 | Reply

  4. David, Long live statspack !

    Comment by Jonathan Lewis — May 28, 2007 @ 11:26 am BST May 28,2007 | Reply

  5. [...] Event Histograms Filed under: Troubleshooting — Jonathan Lewis @ 8:44 pm UTC Jan 10,2007 [Forward to Part 2] [...]

    Pingback by Event Histograms « Oracle Scratchpad — June 13, 2008 @ 7:54 pm BST Jun 13,2008 | Reply

  6. [...] by Event Histograms – 2 « Oracle Scratchpad — June 16, 2008 @ 9:11 am UTC Jun [...]

    Pingback by Event snapshots « Oracle Scratchpad — June 16, 2008 @ 9:12 am BST Jun 16,2008 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers