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

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_at_jlcomp.demon.co.uk> 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_at_gmail.com>
> To: "oracle-l" <oracle-l_at_freelists.org>
> 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
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l