Re: Inconsistent multi-block io

On Jul 7, 2: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

Interesting answer in
http://forums.oracle.com/forums/thread.jspa?messageID=2633398&#2633398 for anyone who hadn't noticed.

Oversimplified, Oracle only asks for blocks not already in memory, so may be any size in the range.

I'm not sure the worst case of every other block in memory is as far off for the optimizer as posited, since it would be so strange to be in such a situation - you'd have to perhaps be updating every other block with sequential read, followed by a full table scan - not inconceivable, say if you just happened to have loaded and updated with a complex key where every other block has an identical part of the key (like a particular division of a company), and then report on the whole thing - and/or the rows are large. It should be possible to construct a test case to demo it.

But something from the docs to note: "Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as 'db file sequential read'."

jg

--
@home.com is bogus.
http://bedposted.com/