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

On Nov 4, 10:27 am, HansP <hans-peter.sl..._at_atosorigin.com> wrote:
> 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

Maybe 31 is the answer to life, parallelism and everything: http://oracledoug.com/serendipity/index.php?/archives/1321-11g-and-direct-path-reads.html#c5162

jg

--
@home.com is bogus.
http://www.chestertontribune.com/Town%20of%20Chesterton/driver_hits_nipsco_pole_surge_fr.htm