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

You can take a look at v$event_histogram to see how distribution goes

don't forget that direct path reads are done by 1M chunks (_db_file_direct_io_count) while scattered read are driven by db_file_multiblock_read_count -- it might as well be that your SAN is able to do better prefetching in case of 1M chunks, you can find out by altering dbfmrc at the session level to a different values and see how it will affect the results

On Wed, May 28, 2008 at 5:45 AM, qihua wu <staywithpin_at_gmail.com> wrote:

> 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
>

-- 
Alex Fatkulin,
The Pythian Group,
http://www.pythian.com/blogs/author/alexf

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