db file scattered read

I tested this example on Oracle 11.2.

I wanted to see whether the TIME_WAITED column in V$SESSION_EVENT is only incremented for events such as db file scattered read and db file sequential read if Oracle has to go to disk to satisfy a read request. I decided to check this out using the db file scattered read event, which is associated with full table scans. Before I started, I created a table called ANDREWS_TABLE. Then I checked the total time my session had waited for the event in question:

SQL> select time_waited

  2  from v$session_event

  3  where sid = (select distinct sid from v$mystat)

  4  and event = 'db file scattered read'

  5  /

TIME_WAITED

-----------

        520

SQL>

Next I counted the rows in the table. This was the first time I had done this so Oracle had to get the rows from disk:

SQL> set timing on

SQL> select count(*) from andrews_table

  2  /

  COUNT(*)

----------

     12308

Elapsed: 00:00:00.18

SQL> set timing off

SQL>

I checked the total time waited for the event and it had increased by 0.15 seconds – so far so good:

SQL> select time_waited

  2  from v$session_event

  3  where sid = (select distinct sid from v$mystat)

  4  and event = 'db file scattered read'

  5  /

TIME_WAITED

-----------

        535

SQL>

I counted the rows again. This time Oracle already had the blocks in memory so it did not have to go to disk:

SQL> set timing on

SQL> select count(*) from andrews_table

  2  /

  COUNT(*)

----------

     12308

Elapsed: 00:00:00.01

SQL> set timing off

SQL>

The elapsed time went down from 0.18 seconds to 0.01 seconds because Oracle already had the rows in memory. Finally I checked the time waited for the event again:

SQL> select time_waited

  2  from v$session_event

  3  where sid = (select distinct sid from v$mystat)

  4  and event = 'db file scattered read'

  5  /

TIME_WAITED

-----------

        535

SQL>

It had not increased, showing that the TIME_WAITED for this event only goes up during a full table scan if Oracle has to fetch blocks from disk.

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>