10g Wait Event Classes and Histograms

Oracle 10g has simplified one important aspect of tuning which is the wait event
interface by broadly classifying wait events into 8-10 major classes
 
This will give us a good overview of which area is contributing the most to a performance
problem and enable us to concentrate our efforts in those areas.
 
While the same information is very well displayed in Oracle 10g EM, it is a good idea to
know the SQL being executed in the background.
 
 
SQL> col wait_class format a30
SQL> select  wait_class,
  2     sum(total_waits), sum(time_waited)
  3  from v$session_wait_class
  4  where wait_class !='Idle'
  5  group by wait_class
  6  order by 3;
 
WAIT_CLASS                     SUM(TOTAL_WAITS) SUM(TIME_WAITED)
------------------------------ ---------------- ----------------
Network                                     199                0
Application                                  32                6
Commit                                      239               70
Configuration                               209              271
Other                                      1120             1035
User I/O                                   8185             5201
Concurrency                                 646             5865
System I/O                               422189           416403
 
8 rows selected.
 
 
To find out which individual events are contributing to the wait class "System I/O" we
can drill down using the next query
 
SQL> col event format a30
SQL> select event, total_waits, time_waited
  2  from v$system_event e, v$event_name n
  3  where n.event_id = e.event_id
  4  and n.wait_class = (select wait_class from v$session_wait_class
  5   where wait_class !='Idle'
  6   group by wait_class having
  7  sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
  8  where wait_class !='Idle'
  9  group by (wait_class)))
 10  order by 3;
 
EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
log file single write                   24          95
Log archive I/O                        458         109
control file sequential read        239678        2503
log file sequential read               424        6677
db file parallel write               79129       86110
log file parallel write              44111       93207
control file parallel write         142355      228264
 
7 rows selected.
 
 
So now we see that the wait event accounting for most I/O waits is the event
related to writing to the control file which is 'control file parallel write'
 
We can go one step further and see the breakdown of the time that has been spent
by sessions waiting on this event by using Histograms.
 
These histograms will organise the wait duration into buckets and will tell us
things like if the wait duration happened often, but for a very short duration,
or happened occasionally, but when it happened the wait time was of a longer
duration.
 
The indicator of a problem is when high wait_count numbers are falling into buckets
located at the far end of the histogram - in this case we see the highest wait_counts
are concentrated at the top end of the histogram.
 
 
SQL> select wait_time_milli bucket, wait_count
  2   from v$event_histogram
  3   where event =
  4   'control file parallel write';
 
    BUCKET WAIT_COUNT
---------- ----------
         1          0
         2      10097
         4      37621
         8      34497
        16      28743
        32      17543
        64       8000
       128       4065
       256       1528
       512        548
      1024         35
      2048          3
      4096          2
      8192          1
 
 

Please follow and like us: