why do you want an opinion? wouldn't you rather have fact?
Depending on the OS - the true multi-block read count varies. Many times, the largest IO we can do is 1MB - meaning with an 8k block size - the multiblock read count max is really 128 blocks.
In most all cases, it will be larger than 64 - by setting really high, you've basically maxed out your multiblock read count.
It does not "confuse" the optimizer to change it - it changes the way the optimizer will cost the full scan when developing a query plan (it could well be that by you setting it higher, Oracle stopped using an inefficient index access and started full scanning to get better performance).
The optimizer does not get confused here, it simply uses a reduced cost and the execution of the SQL will use this larger IO read size if possible. That is the net effect of all of this.
here is some more information from my book Effective Oracle by Design:
<quote>
The DB_FILE_MULTIBLOCK_READ_COUNT parameter controls the number of blocks Oracle will read in a single I/O while doing a full scan or an index fast-full scan. The higher this value, the lower the cost of a full scan.
Consider the following example:
big_table@ORA920> show parameter db_file_multi
NAME TYPE VALUE
-------------------------------- ----------- --------------------
db_file_multiblock_read_count integer 16
big_table@ORA920> set autotrace traceonly explain
big_table@ORA920> select * from big_table;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=2136 Card=1833857 Bytes=154043988)
1 0 TABLE ACCESS (FULL) OF 'BIG_TABLE'
(Cost=2136 Card=1833857 Bytes=154043988)
big_table@ORA920> alter session set db_file_multiblock_read_count = 32;
Session altered.
big_table@ORA920> select * from big_table;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=1354 Card=1833857 Bytes=154043988)
1 0 TABLE ACCESS (FULL) OF 'BIG_TABLE'
(Cost=1354 Card=1833857 Bytes=154043988)
big_table@ORA920> alter session set db_file_multiblock_read_count = 64;
Session altered.
big_table@ORA920> select * from big_table;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=858 Card=1833857 Bytes=154043988)
1 0 TABLE ACCESS (FULL) OF 'BIG_TABLE'
(Cost=858 Card=1833857 Bytes=154043988)As you can see, the higher DB_FILE_MULTIBLOCK_READ_COUNT is set, the lower the cost of a full scan. A full scan will become more appealing than an index access. This will bias a full scan over indexes as it increases.
The valid range of values for DB_FILE_MULTIBLOCK_READ_COUNT is highly operating system-dependent. Each operating system will have its true maximum on the size of an I/O. In this example, when we set our read count to 64, that was 64 ¿ 8KB, or 0.5MB per I/O that we were requesting. Is our operating system able to accommodate that? We can consult our operating system tuning guide to find the maximum read size, or we can trick Oracle into giving us that information. To get the information from Oracle, we enable tracing like this (to capture wait events into the trace file) and set our DB_FILE_MULTIBLOCK_READ_COUNT very high:
big_table@ORA920> alter session set events
2 '10046 trace name context forever, level 12';
Session altered.
big_table@ORA920> alter session set db_file_multiblock_read_count = 5000000;
Session altered.
Then we run a query that does a full scan, such as select /*+ noparallel(big_table) full(big_table) */ * from big_table. We¿ll undoubtedly see wait events on I/O, namely db file scattered read wait events. Using the Unix utility grep to scan the trace file we generated, we find this:
$ grep scattered ora920_ora_31266.trc | more
WAIT #3: nam='db file scattered read' ela= 61870 p1=9 p2=33035 p3=126
WAIT #3: nam='db file scattered read' ela= 48985 p1=9 p2=33547 p3=126
WAIT #3: nam='db file scattered read' ela= 55994 p1=9 p2=33931 p3=126
WAIT #3: nam='db file scattered read' ela= 44090 p1=9 p2=34187 p3=126
WAIT #3: nam='db file scattered read' ela= 46638 p1=9 p2=34699 p3=126
WAIT #3: nam='db file scattered read' ela= 43463 p1=9 p2=35339 p3=126
WAIT #3: nam='db file scattered read' ela= 98053 p1=9 p2=35851 p3=126
WAIT #3: nam='db file scattered read' ela= 46462 p1=9 p2=36363 p3=126
The Oracle9i Database Reference Guide explains what the p1, p2, and p3 values represent, and it tells us p3 is the number of blocks we are requesting to read at one time. Apparently, on this operating system (Red Hat Linux), we can read just short of 1MB of data at a time. (On a Sun Solaris system, my maximum I/O size was 64 blocks, or 0.5MB, at a time.)
On a DSS or data warehouse system, you would want this set to the maximum I/O size your system permits. Not only does it affect query-plan generation, affecting the cost of a full-scan operation, but it also directly affects the performance of the query. The fewer I/O calls to the operating system, the faster a full scan will perform.
On a transactional system, where you do not anticipate doing full scans frequently, a smaller setting may be appropriate, to make full scans less appealing in those systems. You can always use the ALTER SESSION command in any batch jobs to permit them to use a larger multi-block read count.
In Oracle9i, with support for multiple block size databases, it is interesting to note that this parameter is actually used to set the IO size, not the number of blocks read! What that means is that Oracle will take your db_file_multiblock_read_count and multiply that by your default blocksize (the blocksize of your SYSTEM tablespace). That is what Oracle will use as the IO size on your system. If your default block size is 8k and you set this parameter to 16, you will only read 8 blocks at a time in a 16k blocksize tablespace. This fact is not documented and was learned only by testing. In response to a recent AskTom question:
I have a 32K blocksize tablespace on a database where the "default" block size is 8K. Does the db_file_multiblock_read_count apply as a multiple of the 8K for all datafiles, or is it tablespace dependant -- ie. (32K*dfmrc) for 32K tablespace?, and (8K*dmfrc) for 8K tablespaces's on the same instance?
It was an interesting question so I setup a simulation to test with. I was using an 8k blocksize database by default and simply created a 16k tablespace to test with. In order to do this, I had to setup my 16k cache size first, I did that by sizing down my db_cache_size and then setting the db_16k_cache_size and creating a tablespace:
ops$tkyte@ORA920> show parameter db_cache_size;
NAME TYPE VALUE
db_cache_size big integer 67108864
ops$tkyte@ORA920> alter system set db_cache_size=32m;
System altered.
ops$tkyte@ORA920> alter system set db_16k_cache_size=32m;
System altered.
ops$tkyte@ORA920> create tablespace sixteen_k
2 blocksize 16k
3 extent management local
4 uniform size 2m
5 /
Tablespace created.
Then, I simply copied the BIG_TABLE into that tablespace:
ops$tkyte@ORA920> create table big_table_copy_16k
2 TABLESPACE sixteen_k
3 as
4 select * from big_table.big_table;
Table created.
ops$tkyte@ORA920> analyze table big_table_copy_16k
2 compute statistics for table;
Table analyzed.
Using the same technique outlined above, setting the 10046 EVENT and inspecting the trace file after executing:
ops$tkyte@ORA920> select /*+ noparallel(b) full(b) */ count(*)
2 from big_table_copy_16k b;
COUNT(*)
----------
1833792
ops$tkyte@ORA920> select /*+ noparallel(b) full(b) */ count(*)
2 from big_table.big_table b;
COUNT(*)
----------
1833792
and upon inspecting the trace file, we discover:
select /*+ noparallel(b) full(b) */ count(*)
from big_table_copy_16k b
END OF STMT
PARSE #1:c=1953,e=1728,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=4,
BINDS #1:
EXEC #1:c=0,e=132,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,
WAIT #1: nam='SQL*Net message to client' ela= 6 p1=1650815232 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 88910 p1=13 p2=6 p3=8
WAIT #1: nam='db file scattered read' ela= 662 p1=13 p2=14 p3=8
WAIT #1: nam='db file scattered read' ela= 869 p1=13 p2=22 p3=8
(repeated continuously)
select /*+ noparallel(b) full(b) */ count(*)
from big_table.big_table b
END OF STMT
PARSE #1:c=1953,e=1350,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,
BINDS #1:
EXEC #1:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,
WAIT #1: nam='db file scattered read' ela= 500 p1=9 p2=33035 p3=16
WAIT #1: nam='db file scattered read' ela= 501 p1=9 p2=33051 p3=16
WAIT #1: nam='db file scattered read' ela= 566 p1=9 p2=33067 p3=16
Now, my multi-block read count in this case was 16. When we queried the original BIG_TABLE that was in my default block size tablespace ¿ we achieve a multi-block read count of 16 blocks at a time. The BIG_TABLE_COPY in the 16k blocksize tablespace was read 8 blocks at a time ¿ same size reads, just different block counts. Further, if you re-run the example but use a 4k blocksize tablespace ¿ you will discover:
select /*+ noparallel(b) full(b) */ count(*)
from big_table_copy_4k b
END OF STMT
PARSE #1:c=1953,e=1781,p=1,cr=1,cu=0,mis=1,r=0,dep=0,og=4,
BINDS #1:
EXEC #1:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,
WAIT #1: nam='db file scattered read' ela= 210816 p1=13 p2=18 p3=32
WAIT #1: nam='db file scattered read' ela= 26353 p1=13 p2=50 p3=32
WAIT #1: nam='db file scattered read' ela= 1145 p1=13 p2=82 p3=32
The 4k blocksize tablespace was read 32 blocks at a time ¿ showing that Oracle in this case will read 16 (multi-block read count) * 8k = 128k at a time. This has an interesting side effect on the costing of full table scans that you may want to be aware of. Given that we have 3 tables with the same exact data, using an IO size that is identical, and sizes that are very near to each other (each table was within a few megabytes of each other table in size) -- you would expect the cost of a full scan against each to be the same ¿ but in fact:
ops$tkyte@ORA920> set autotrace traceonly explain
ops$tkyte@ORA920> select /*+ noparallel(b) full(b) */ count(*)
2 from big_table_copy_4k b;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3273 Card=1)
ops$tkyte@ORA920> select /*+ noparallel(b) full(b) */ count(*)
2 from big_table.big_table b;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2313 Card=1)
ops$tkyte@ORA920> select /*+ noparallel(b) full(b) */ count(*)
2 from big_table_copy_16k b;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1857 Card=1)
Oracle is costing each query as if it would read radically differing amounts of data! See how the cost apparently decreases as the blocksize increased. This is something to be aware of when using tablespaces with different sized blocks in the same database.
</quote>