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
catalog.
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
