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: