MultiBlock Read Count

As everyone will be aware, this parameter controls how much data Oracle thinks it can retrieve from the disks in a single trip (during a table scan). The important part of this statement is the "how much Oracle thinks". It is relatively easy to manufacture a circumstance where Oracle thinks it can achieve a much higher read size than can actually be serviced by the operating system. Unfortunately, this discrepancy can have an impact on performance, since the cost optimizer evaluates costs based on what Oracle thinks, not what is actually achievable.

Luckily its easy to determine the differences. Each version of Oracle on each port, is shipped with a preset maximum of how much data can be transferred in a single read (which of course is equivalent to the db_file_multiblock_read_count since the block size is fixed). For 8i (on most platforms) this is 1Mb and is referred to as SSTIOMAX. To determine it for your port and Oracle version, simply set db_file_multiblock_read_count to a nonsensical value and Oracle will size it down for you:

SQL> alter session set db_file_multiblock_read_count = 1000;

Session altered.

SQL> select value from v$parameter where name = 'db_file_multiblock_read_count';

VALUE
------------------------------
128

So on this system (8.1.7 on Win2000, 8k blocksize), the maximum is 128 (or 1MB).

But... can my system actually deliver what Oracle thinks it can? This is also easy to determine, with some trace file diagnostics:

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select /*+ FULL(t) */ count(*) from sys.source$ t;

  COUNT(*)
----------
    787035

and then we have a look at the trace file created, and see just how good our table scan was:

PARSING IN CURSOR #1 len=50 dep=0 uid=0 oct=3 lid=0 tim=0 hv=3495837896 ad='425b410'
select /*+ FULL(t) */ count(*) from sys.source$ t
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1111838976 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=207 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=208 p3=1
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=931 p3=32
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1787 p3=32
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1851 p3=32
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1931 p3=32
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=2027 p3=32

and thus, under Win2000 on 8.1.7 on a basic FAT32 filesystem, we can't achieve more than 32. Interestingly, 32 is a very common result on many platforms (Unix included), because the default file system options limit the maximum read to 256k. Under raw devices, or more carefully built filesystems, you can get the full 128.

Hence my recommendations would be:

• Don't set db_file_multiblock_read_count to something it cannot possibly achieve. This is akin to not keeping the optimizer statistics up to date. If you give the optimizer incorrect information, you'll get poor access path selection.
• Ensure that your maxium read size is configured at ALL appropriate levels. This could mean in the kernel (for example, 'maxphys' for Solaris), in the logical volume manager (Veritas has similar settings), and in the filesystem (maxcontig is typically the important one). Only then can Oracle achieve what it is capable of.
• Any instance or session running the optimizer in RULE mode should have the db_file_multiblock_read_count set as high as possible, since a full scan will be last option chosen by the optimizer anyway. Under Rule, its so rare to do a table scan, that you'll be wanting to run it as fast as possible. A similar argument could be made for the FIRST_ROWS optimizer mode - full scans are rare, so they may as well be fast.
• Any instance or session running ALL_ROWS (either directly or via CHOOSE) will probably still want to have this parameter set to the highest value possible. However, you will probably want to look at other parameters such as optimizer_index_caching and optimizer_index_cost_adj to ensure that full scans don't dominate the system.

===================================================================

DB_FILE_MULTIBLOCK_READ_COUNT
is one of the parameters you can use to minimize I/O during table scans.
It specifies the maximum number of blocks read in one I/O operation during a sequential scan.
The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count,
and whether parallel execution is being utilized for the operation.

Let see example:

Set db_file_multiblock_read_count=1

1. alter system set db_file_multiblock_read_count=1
2. alter system flush buffer_cache

And now we watch sql_plan for following statment "select /*+ full(a) */count(*) from TABLE_1 a"

SQL> explain plan for
select /*+ full(a) */count(*) from TABLE_1 a
;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 635235748
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21893 (2)| 00:05:22 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TABLE_1 | 8567K| 21893 (2)| 00:05:22 |
--------------------------------------------------------------------------

Time of execution statement is 387 second

--------------------------------------------------------------------------------

Now change db_file_multiblock_read_count

1.alter system set db_file_multiblock_read_count=64
2. Run statement for collecting new system statistics:
begin
dbms_stats.gather_system_stats();
end;

alter system flush buffer_cache

SQL> explain plan for
2 select /*+ full(a) */count(*) from TABLE_1 a;

Explained

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 635235748
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23709 (3)| 00:03:57 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TABLE_1 | 8567K| 23709 (3)| 00:03:57 |
-------------------------------------------------------------------------- 

Time of execution statement is 69 second

Result told that in our case increasing of db_file_multiblock_read_count decrease runtime of statement but increase cost

The value of db_file_multiblock_read_count can have a significant impact on the overall database performance and it is not easy for the administrator to determine its most appropriate value.

Oracle Database 10g Release 2 automatically selects the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache.

Before 10g R2, DBA's used the db_file_multiblock_read_count initialization parameter to tell Oracle how many block to retrieve in the single I/O operation.

Before Oracle10g R2, the permitted values for db_file_multiblock_read_count were platform-dependent. The most common settings ranged from 4 to 64 blocks per single multi-block I/O execution.