Re: db_file_multiblock_read_count and performance

Post, Ethan wrote:
> I would think ideal is "as fast as possible" and assuming you tell
> Oracle the truth about speed of multiblock reads verse single block with
> optimizer_index_cost_adj everything should work out fine, even if full
> scans are favored. Of course too many scans at the same time and
> optimizer_index_cost_adj is no longer valid because IO bandwidth may
> effect response times, so I guess this is where system stats has a
> supreme advantage.=20

While I strongly advocate to use system statistics rather than tinkering with optimizer_index_cost_adj, as far as the cost differential between single block IO and multi block IO becoming invalid with differing workload, that same issue exists with system statistics. Of course you can have different sets of system statistics for different workloads and   activating them as appropriate. One advantage of o_i_c_a over system statistics is that you can override it at the session level. There have been times where I was on a system without system statistics gathered where I would have liked to see what the CBO would do with system statistics but you can't do that on a per session basis. That leads into one of my wishes for a future release: That on a session basis I could direct the CBO to use the statistics stored under a particular statid in the user statistics table rather then those in the catalog.

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l