Re: Inconsistent multi-block io

On Jul 7, 5:19 pm, mak..._at_gmail.com wrote:
> I'm on 10gR2 on AIX with ASM. have DB_FILE_MULTIBLOCK_READ_COUNT=16 in
> spfile. but still see different block size for "db file scattered
> read" ranging from 2 to 16. Can someone explain why its ranging from
> 2-16? Shouldn't DBRC always be followed if it's set in SPFILE?
>
> see below excerpts from 10046 trace file.
>
> WAIT #3: nam='db file scattered read' ela= 48396 file#=61 block#=416
> blocks=5 obj#=80135 tim=1526895870393
> WAIT #3: nam='db file scattered read' ela= 8210 file#=61 block#=33717
> blocks=4 obj#=80135 tim=1526895880210
> WAIT #3: nam='db file scattered read' ela= 7306 file#=61 block#=52614
> blocks=3 obj#=80135 tim=1526895897691
> WAIT #3: nam='db file scattered read' ela= 8508 file#=61 block#=53030
> blocks=16 obj#=80135 tim=1526896239141
> WAIT #3: nam='db file scattered read' ela= 15903 file#=61 block#=53046
> blocks=15 obj#=80135 tim=1526896258427
> WAIT #3: nam='db file scattered read' ela= 921 file#=61 block#=53379
> blocks=2 obj#=80135 tim=1526896507844
>
> Thanks

No, when Oracle finds blocks in memory it does not re-read those blocks from disk so if there were 16 blocks in the potential read but block 5 was already in memory you will see a read for blocks 1 - 4 and another read for blocks 6 - 16 assuming no other blocks in the extent were already in the buffer cache.

HTH -- Mark D Powell -- Received on Tue Jul 08 2008 - 09:09:19 CDT