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.
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 UTC Feb 1,2007 |
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 UTC Feb 2,2007 |
Hi
In AWR reports there is no event histogram statistics!
Comment by David Lopez — May 28, 2007 @ 11:02 am UTC May 28,2007 |
David, Long live statspack !
Comment by Jonathan Lewis — May 28, 2007 @ 11:26 am UTC May 28,2007 |
[...] 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 UTC Jun 13,2008 |
[...] 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 UTC Jun 16,2008 |