Oracle Performance and Engineering

What is?

It is the initialization parameter that tells Oracle how many data blocks can be sequentially read during full-table scans or index fast-full scans (which are grouped under 'db_file_scattered_reads, though the I/Os are really sequential).

Thus querying v$parameter will give the present value for this which can be changed dynamically in a session or throughout the database.

What should it be set to?

Generally, it should be set to the highest possible depending on the operating system. However, value of (DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE) tells how much I/O can the database handle in one single read. This is restricted on two counts.

(a) "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 and above (on most platforms) this is 1Mb and is referred to as SSTIOMAX"

Thus, for a block size of 8KB or 16KB, the max useful value of db_file_multiblock_read_count is 128 or 64, respectively. So, if the change is not reflected in spite of you setting this parameter too high - now you should know why.

(b) It also depends on the OS. Since the OS also has a max_allowable_IO_size (like, maxphys in /etc/system dictates how much the max. physical I/O would be on Solaris).

Steve Adams has a nifty script to find out the maximum working value for this parameter your particular system.

The script does the following,

(a) set the parameter to an exorbitantly high value.
(b) trace a 'forced' (hint : FULL(tablename)) from a fairly large table to capture the wait events
(c) read the trace file for the maximum value of "p3" for db_file_scattered_read wait event.

How optimizer decision is impacted by this

Well, this parameter tells the optimizer something about the 'cost' of a full-table scan or index fast-full scan (which does sequential I/O unlike other types of index scan). Thus if the optimizer sees a high value for this parameter, it implies lesser effort to read the entire table than it would with a lower value of this (since lower I/O chunks == more number of trips to read the full).

Thus varying this parameter could influence the way optimizer behaves.

Here follows a very simple example --

"USERS" is a fairly large table with over 300K records.

-- db_file_multiblock_read_count = 16 (the usual for the system)

select count(*) from users


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.28 592 595 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.10 0.31 592 595 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=595 pr=592 pw=0 time=288781 us)
300634 INDEX FAST FULL SCAN USERS_N9 (cr=595 pr=592 pw=0 time=26493966 us)(object id 323598)

-- setting the parameter extremely low

alter session set db_file_multiblock_read_count = 1;

-- same SQL now does a FULL SCAN (not a FAST-FULL scan since the
-- db_file_multiblock_read_count is too low. Optimizer decides fast full scan or full table scans --would be very costly here

select count(*) total from users a;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.08 0.07 0 590 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.08 0.08 0 590 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=590 pr=0 pw=0 time=73816 us)
300634 INDEX FULL SCAN USERS_N9 (cr=590 pr=0 pw=0 time=601310 us)(object id 323598)

-- set it back to very high. FAST FULL SCAN is back!

alter session set db_file_multiblock_read_count = 32768;

select count(*) total from users b;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.07 0.06 0 595 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.07 0.06 0 595 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=595 pr=0 pw=0 time=63094 us)
300634 INDEX FAST FULL SCAN USERS_N9 (cr=595 pr=0 pw=0 time=601334 us)(object id 323598)

In next instalment we'll see how (possibly) to use "optimizer_index_cost_adj" to counter the effect of "db_file_multiblock_read_count" and some other advanced notes on this parameter.