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

On Nov 4, 8:26 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Nov 4, 8:05 am, HansP <hans-peter.sl..._at_atosorigin.com> wrote:
>
>
>
>
>
> > 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
>
> What kind of memory management is in use?
> What is the database block size?
>
> This is only a guess since I do not have an 11g system to test with,
> but I suspect that the size chosen may have something to do with the
> size of the available in memory sort area.
>
> Depending on the memory management in use and the number of concurrent
> working connections you had or can create you might be able to run
> some tests and see if the number varies with load.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

PS -

I forgot to ask what type of extent management was used on your temporary tablespace?

If you are using 8k block sizes then the 128 block multiblock read count indicates that you are doing 1M multi-block read counts though keep in mind that if some of the blocks that would be in a request are already in memory Oracle will break the single request into multiple requests to fetch those blocks before and after the block(s) already in the buffer cache.
That is if block 50 is in the buffer pool you will see a 'db file scattered read' for blocks 1 - 49 and another for 51 - 128 rather than a single request for 1 - 128.

HTH -- Mark D Powell -- Received on Tue Nov 04 2008 - 07:35:35 CST