RE: direct read is much faster than scattered read, is it reasonable?

As direct path reads can be done in asynch fashion, these wait event timings might not be accurate in expected sense.  

In asynch read cases combined with readahead, the wait event does not measure the time it took to service the IO request from its submission to completion.  

In asynch IO case you do following:  

1. submit IO request
2. continue doing other work
3. check if IO request submitted in step 1 is completed and use that data / or wait for IO completion

In async direct path read case the wait timing is done in 3 only. You might see different results depending on your db version, how asynch IO is used and probably more factors...  

--

Regards,
Tanel Poder
 <http://blog.tanelpoder.com/> http://blog.tanelpoder.com  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of qihua wu
Sent: Wednesday, May 28, 2008 17:46
To: oracle-l
Subject: direct read is much faster than scattered read, is it reasonable?

I executed these sql on my database:

SQL> select time_waited_micro/total_waits/1000000 from v$system_event where event_id in (select event_id from v$event_name where name='direct path read');

TIME_WAITED_MICRO/TOTAL_WAITS/1000000

                           .000503342

SQL> select time_waited_micro/total_waits/1000000 from v$system_event where event_id in (select event_id from v$event_name where name='db file scattered read');

TIME_WAITED_MICRO/TOTAL_WAITS/1000000

                           .001818803

We could see: to access one block using scattererd read, it will take 1.8 ms, but when access one block using "direct path read", it only takes 0.5ms. How could direct path read much much quicker than scatterer read? Isn't the scattered read the fastest method to access data block? Our system uses SAN storage, and SAN could pre-fetch to speed-up the block access. But I think it pre-fetch will help both scattered read and direct path read, am I right?

Thanks,
Qihua

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 28 2008 - 20:19:18 CDT