Monday, June 04, 2012

db file scattered read



The idea for this example came from the book advertised above. I tested it 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.

No comments: