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:
Post a Comment