I've been experimenting with this quite a bit over the last few weeks and
you have to be careful when setting db_file_multiblock_read_count -
especially if you have different hardware configurations from development to
test to production. In one of my environments I was able to set it to 128
and it worked very well while in a different environment with significantly
older hardware I could only see benefits up to a value of 32.
Don't just say ...
Don't set db_file_multiblock_read_count to a higher value!
Indexes won't be used then caused by a cheaper access to full- table
scans!
... because that's not necessarily the case :-)
Depending on how your data is structured, how much data are in the tables,
how the queries are issued, how your indexes are setup, how current your
stats are, etc etc etc having a high value will not preclude the optimizer
from using the indexes. In my testing with various types of queries certain
indexes, if force used via hints or otherwise, actually perform worse than
full table scans - even though, on paper, the query plan involving the
indexes 'looked' better - an index doesn't necessarily automatically mean
better performance.
Best suggestion I can give is to perform testing in a carefully controlled
test database/server and try various settings to see what works the best for
the types of queries you want. Be sure to setup detailed tracing so you can
analyze what's going on.
For example, here are the settings that I've been working with that seem to
work for my particular test environment (Sun V240, 2GB RAM, 2 CPUs, Solaris
8, Oracle9iR2 9.2.0.5, not sure what the HD's are but I think they are all
10K RPM 72GB SCSI)
optimizer_index_cost_adj = 100
optimizer_index_caching = 90
db_file_multiblock_read_count = 128
There's no 'blanket' answer to this issue obviously :-)
Sorry if I've muddied the waters but I hope this helps.
MarkH
-----Original Message-----
From: ddc
[mailto:oracledba-ezmlmshield-x94618855.[Email address protected]
Sent: Monday, February 28, 2005 4:03 AM
To: LazyDBA Discussion
Subject: FW: what's db_file_multiblock_read_count ?
Forwarding the mail to the grp...
==========
Don't set db_file_multiblock_read_count to a higher value!
Indexes won't be used then caused by a cheaper access to full- table scans!
If you'd like to change optimize-behaviour try setting
Optimizer_idex_costs_adj
Optimizer_index_caching
RH
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page