Re: direct path read (temp) 31 blocks per event

"HansP" <hans-peter.sloot_at_atosorigin.com> wrote in message news:c737dfd3-d2e5-4693-8646-6ba48189011d_at_v13g2000pro.googlegroups.com...
> Version 11.1.0.6
> Platform: Linux
>
> During a performance test I noticed the that 'direct path read temp'
> where done with 31 blocks and sometimes with 1 block per wait.
> The 'db file scattered read' 's where done with 128 blocks per wait
> (db_file_multiblock_read_count was not set explicitly)
>
> Does someone know why the strange number of 31 is chosen for the
> direct path reads (at least for temp).
> I think that normal direct path reads were als done with 31 blocks but
> I am not completely sure about that.
>
> BTW I looked in v$sesion_wait P3 is the block count.
>
> Any suggestions
>
> Regards Hans-Peter

It depends on the operation that had caused the direct path writes. If it was a hash join then the (dynamic) hash_io_multiblock_count would have been responsible for writing clusters of 31 blocks, so you would have to read clusters of the same size. Similarly if the sort_multiblock_read_count had dynamically adjusted itself to optimize the balance between size of reads and number of sort runs to merge, then 31 could just have appeared.

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