MBRC and DB_FILE_MULTIBLOCK_READ_COUNT

Maybe you have this down but I found out today that I had understood this completely backwards. It was my understanding that DB_FILE_MULTIBLOCK_READ_COUNT (if set) was only used to COST the plan but MBRC (if set) would be used for each scattered read. This doesn’t appear to be the case, in fact it appears I have this exactly opposite.

I have on my test box collected workload system stats and MBRC is set to 8.


I tried it with DB_FILE_MULTIBLOCK_READ_COUNT set to 0, in which case the system sets it to 128. I expected to get scattered reads of 8. But I got my first read on the table at 128, and then next got what was left. (I created the table with 1024 blocks in the first extent.)


WAIT #10: nam='db file scattered read' ela= 16051 file#=4 block#=290066 blocks=128 obj#=77649 tim=891367221237

WAIT #10: nam='db file scattered read' ela= 1341 file#=4 block#=290194 blocks=24 obj#=77649 tim=891367282208


OK, not what I expected. Then I set DB_FILE_MULTIBLOCK_READ_COUNT to 16 and dag-nab-bit, I got a bunch of 16 block reads.


file scattered read' ela= 903 file#=4 block#=290082 blocks=16 obj#=77649 tim=891493998398

file scattered read' ela= 807 file#=4 block#=290098 blocks=16 obj#=77649 tim=891493999573

file scattered read' ela= 839 file#=4 block#=290114 blocks=16 obj#=77649 tim=891494000751

file scattered read' ela= 824 file#=4 block#=290130 blocks=16 obj#=77649 tim=891494001988

file scattered read' ela= 815 file#=4 block#=290146 blocks=16 obj#=77649 tim=891494003176

file scattered read' ela= 23783 file#=4 block#=290162 blocks=16 obj#=77649 tim=891494027343

...


Well then I looked at the COST of the plan, I get the same COST for a setting of 8, 16, and 32 for DB_FILE_MULTIBLOCK_READ_COUNT (it's kinda hard to read, the cost is 50 in the plan below):


SQL> get zz_test1
1 select *
2 from MBRC_TEST
3* where object_id between 1 and 4

SQL> @hxplan

Enter .sql file name (without extension): zz_test1

Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL] :

Plan hash value: 3059191348


-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 503 | 52312 | 50 (0)| 00:47:06 |
|* 1 | TABLE ACCESS FULL| MBRC_TEST | 503 | 52312 | 50 (0)| 00:47:06 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"<=4 AND "OBJECT_ID">=1)


So there you have it, the MBRC value is used to COST the plan but NOT for the scattered read event. Honestly reading the docs it was less then clear, hence my confusion. Nothing beats a test! (or more then one really, I did this several times just to make sure I was seeing it right.)