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

On 4 nov, 14:35, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

I forgot to mention

- blocksize - 8k
- memory management was the 11g automatic memory management
- extent managment on TEMP was locally managed 1M uniform extent size.

I don't think that your scenario applies for the direct path reads. Direct path reads temp were consitently done with 31 blocks and 1 block at a time.

Your scenario is not correct as far as I know. If there is a block in the cache already you will see 128 db file sequential reads and not 3 reads.

Regards Hans-Peter Received on Tue Nov 04 2008 - 12:27:47 CST