Re: direct read is much faster than scattered read, is it reasonable?
- From: "qihua wu" <staywithpin@xxxxxxxxx>
- To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 29 May 2008 13:52:21 +0800
Hi, Lewis,
Thanks very much for your detailed explanation. Direct read is supper fast
just because of the asynch fashion. My next question is: For scattered
read, is it possible to use async fashion? Scattered read will read data
into SGA which requires some latches to protect the memory. Does reading
into SGA/using latches prevent the async fashion?
Thanks,
Qihua
On Thu, May 29, 2008 at 4:00 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
>
> There are various reasons -
>
> some db file scattered reads may be very short and not trigger readahead,
> so the next scattered read may also be a real read. direct path reads
> are often long and contiguous, so they may be more likely to trigger
> read-ahead, and more likely to take advantage of the readahead
> that's been triggered.
>
> Also, direct path reads can be done asynchronously - which means
> that 4 requests may be dispatched before the first is checked. This
> means that the wait time of the first read is reduced by the time it took
> to prepare and dispatch the 2nd, 3rd and 4th.
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message ----- From: "qihua wu" <staywithpin@xxxxxxxxx>
> To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
> Sent: Wednesday, May 28, 2008 10:45 AM
> 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
>>
>>
>
Other related posts:
