I recently published a blog on restricting Linux block storage IO sizes using Kernel settings, but what if you don’t have root access, or want a more dynamic approach for your Oracle Database.
In this blog post I am going to explore the use of multiblock read count (MBRC) on an Oracle 19c Database.
Oracle Multi Block Read
The Oracle db_file_multiblock_read_count (MBRC) parameter specifies the maximum number of blocks read in one I/O operation during a sequential scan.
From the Oracle 19c Database documentation:
The default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.
SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- --------------------
db_block_size integer 8192
If db_file_multiblock_count is not set, or set to 0, then the maximum value is the operating system’s maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE).
For example, for a database with a db_block_size of 8,192 (8K) we will typically see a default value db_file_multiblock_read_count of 128, as the default Linux size is 1MB, for example.
1,048,576 / 8,192 = 128 (1MB / 8K = 128)
SQL> alter system reset db_file_multiblock_read_count scope=both;
System altered.
SQL> show parameter db_file_multiblock_read_count;
NAME TYPE VALUE
------------------------------------ ----------- --------------------
db_file_multiblock_read_count integer 128
Ok, let’s perform a Full Table Scan (FTS) and check physical block IO size.
SQL> select count(*) from order_items where condition = 'New';
COUNT(*)
----------
185862799
Elapsed: 00:00:18.02
SQL>
From the FlashArray UI we can see an Read IO Size ~1MB.
OK, knowing that my database is using a Pure FlashArray that supports 4MB IOs, let’s increase db_file_multiblock_read_count to 512 and see what happens.
4,194,304 / 8,192 = 512 (4MB / 8K = 512)
Let’s repeat the Full Table Scan (FTS) and check Read IO block size.
SQL> alter session set db_file_multiblock_read_count = 512;
Session altered.
SQL> show parameter db_file_multiblock_read_count;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 512
SQL> select count(*) from order_items where condition = 'New';
COUNT(*)
----------
185862799
Elapsed: 00:00:15.64
SQL>
From the FlashArray UI we can now see a Read IO Size ~4MB.
OK, let’s see the impact of reducing db_file_multiblock_read_count to 4.
32,768 / 8,192 = 4 (32KB / 8K = 4)
SQL> alter session set db_file_multiblock_read_count = 4;
Session altered.
SQL> show parameter db_file_multiblock_read_count;
NAME TYPE VALUE
------------------------------------ ----------- --------------------
db_file_multiblock_read_count integer 4
SQL> select count(*) from order_items where condition = 'New';
COUNT(*)
----------
185862799
Elapsed: 00:00:51.69
SQL>
From the FlashArray we can now see a Read IO Size has been reduced to 32KB.
Output from iostat -tkx 1 -d /dev/dm-18 confirms the average request size (avgrq-sz) for my FlashArray volume now never exceeds 64 sectors or 32KB (64*512B) as expected.
[oracle@z-oracle ~]$ iostat -mx 1 -d /dev/dm-18
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
dm-18 0.00 0.00 13446.00 0.00 420.17 0.00 64.00 7.63 0.57 0.57 0.00 0.0740 99.50
dm-18 0.00 0.00 13554.00 2.00 423.48 0.02 63.98 7.70 0.57 0.57 1.00 0.0734 99.50
dm-18 0.00 0.00 13948.00 0.00 435.86 0.00 64.00 7.81 0.56 0.56 0.00 0.0711 99.20
dm-18 0.00 0.00 13640.00 0.00 426.23 0.00 64.00 7.73 0.57 0.57 0.00 0.0727 99.20
dm-18 0.00 0.00 13856.00 2.00 432.80 0.02 63.96 7.71 0.56 0.56 0.50 0.0719 99.70
dm-18 0.00 0.00 12783.00 0.00 399.45 0.00 64.00 7.69 0.60 0.60 0.00 0.0775 99.10
dm-18 0.00 0.00 12521.00 0.00 391.27 0.00 64.00 7.84 0.63 0.63 0.00 0.0788 98.70
...
Summary
In this blog I have shared how the dynamic, system and session level db_file_multiblock_count (MBRC) parameter can be used to influence physical storage IOs for full table scans (FTSs) operations.
The unsupported, undocumented, underscore parameter _asm_maxio may offer alternative approach to control ASM maximum IO sizes for non-FTSs but that’s the topic for another post.