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

Did you grep for only 'db file scattered read' ? I believe there should be sequential read in between scattered read.
What is your block size ?
Can you post #blocks for first few extents (10 extents)

Hi Reega

Dictionary or Local, same regardless.

Cheers

Richard Foote
http://richardfoote.wordpress.com/

Richard,

"A multiblock read is split if a block is already in memory (it doesn't read the block in memory again)."

Reg. above comment, so what does it gets from disk , just the blocks that are not in memory or gets blocks=mbrc from disk but reads only absent blocks into memory? if it issues the read request to disks for just absent blocks then if , in case, I have every alternate blocks in SGA, will my FTS become index-read(Single block I/O ) ?

"Also, a read must end on an extent boundary which can also prevent a "full" multiblock read."

I'm using LMT with auto exent management which I assume allocate extent in 4,8,64,128, 512 blocks. Right?. In that I can understand multiblock reads in even numbers but I still can't understand 3,5, 7, 15 blocks reads..

Thanks

Max,

I think Richard already provided a clear answer, but, to further elaborate:

Suppose you have db file multiblock read count set to 128, and your block size is 8k.
That's a 1MB read, and is a common max read size for many OSes. Now, suppose of those 128 blocks you want to read in, block numbers 6, 18, and 37 are in memory already. So, Oracle will break the single 128 block read into 4 reads:
read #1: blocks 1-5, block 6 is in memory, so that's it. size of read: 5 blocks
read #2: blocks 7-17, block 18 is in memory. size of read: 11 blocks
read #3: blocks 19-36, block 37 is in memory. size of read: 18 blocks
read #4: blocks 38-128, no more blocks in memory, so reads to end of mbrc. size of read: 91 blocks

So, it's easy to see, depending on what blocks are already in memory, how it may be possible to get reads of any arbitrary size, including odd numbers of blocks.

Hope that helps,

-Mark

That is one of the limitations -- the Optimizer doesn't know how many of the table's blocks are already currently present in the db_cache. Hopefully it will be fixed in some future release -- we might have an option to tell Oracle "for table XYZ, please monitor how many blocks {it can't go to the extent of which blocks} are currently in db_cache and use that in your fulltablescan cost estimation".

Even if you do an ALTER SYSTEM CHECKPOINT ; pre-existing blocks in the db_cache remain there.
I wonder if an ALTER SYSTEM FLUSH BUFFER_CACHE ; would help -- but there are very obvious consequences of that ! {may we should get a FLUSH BUFFER_CACHE FOR SEGMENT XYZ !}

The presence of OS filesystem cache and SAN cache can skew the expected time for a multiblock read further -- some of the 1M reads are already present in either of them !