AVERAGE_WAIT

The AVERAGE_WAIT column in V$SYSTEM_EVENT records how long Oracle has had to wait (on average) for a given event. The value is in hundredths of a second:

 

SQL> l

  1  select event, average_wait

  2  from v$system_event

  3  where event in

  4  ('db file sequential read',

  5*  'db file scattered read')

SQL> /

 

EVENT                          AVERAGE_WAIT

------------------------------ ------------

db file sequential read                1.67

db file scattered read                 5.16

 

SQL>

 

The example above was run on an Oracle 11.2 test database running on Solaris with datafiles on a Celerra filer. The first line relates to single block reads and the second to multiblock reads. Here are the same values from an Oracle 11.2 production database, also running on Solaris but with datafiles on dedicated disks:

 

SQL> l

  1  select event, average_wait

  2  from v$system_event

  3  where event in

  4  ('db file sequential read',

  5*  'db file scattered read')

SQL> /

 

EVENT                          AVERAGE_WAIT

------------------------------ ------------

db file sequential read                  .2

db file scattered read                  .58

 

SQL>

Finally, here are the figures from an Oracle 10.2.0.1.0 database running on Windows XP SP3 on a PC I assembled myself:

SQL> l
  1  select event, average_wait
  2  from v$system_event
  3  where event in
  4  ('db file sequential read',
  5*  'db file scattered read')
SQL> /

EVENT                          AVERAGE_WAIT
------------------------------ ------------
db file sequential read                6,49
db file scattered read                 5,88

SQL>