CBO and 8.0.5 fix

Great news from the Oracle 8.0.5 readme file:

The problem of the Cost Based Optimiser wading enthusiastically into tablescans has finally been addressed by two new init.ora parameters:

optimizer_index_cost_adj

	Allows you to declare that indexed paths are inherently
	cheaper across the board by telling the optimiser to multiple
	the cost of using indexes by this percentage.  (default value is

optimizer_index_caching

        This is the more subtle, and desirable one (I think). It lets you declare

        the fraction of index blocks that the optimiser should assume to be in

        the db_block_buffers when working out paths that include nested loop

        index accesses. (default value is 0%).

I haven't tested these yet, but my feeling is that the first could be left at default
in most cases, and the second should be set to a value similar to your
target 'cache buffer hit ratio' - usually defined as something like

                1 - (physical reads / logical reads)
expressed as a percentage.

Anyone who has started using these parameters is invited, please, to share their observations.

--
Jonathan Lewis Received on Sun Nov 22 1998 - 04:11:12 CST