Re: db_file_multiblock_read_count and performance

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: Ethan.Post@xxxxxx, Oracle-L@xxxxxxxxxxxxx
  • Date: Tue, 07 Dec 2004 12:54:56 -0700

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 

Wolfgang Breitling
Centrex Consulting Corporation

Other related posts: