Re: optimizer parameters

"Luc Gyselinck" <Luc.Gyselinck_at_nospampandora.be> wrote in message news:R_Ggb.64122$2E4.3125361_at_phobos.telenet-ops.be...
> In an OLTP environment, it is more common that many index blocks are
cached
> into the buffer cache. Users work only on small subsets of the data, so
> nested loops are the fastest way to retrieve the data.
>
> optimizer_index_caching = 70 (70% of the index blocks are cached)
> optimizer_index_cost_adj = 25 (reduce the cost on index lookups = nested
> loops to 25% percent of normal)
>
> In a DW environment, where full table scans and hash joins are more
common,
> we set them just the way around.
>
> optimizer_index_caching = 10 (small, no caching)
> optimizer_index_cost_adj = 100 (make index lookups = nested loops as
> expensive as possible; You can even go beyond the 100 percent)
>
> (tested on 8.1.6)
>
> SQL> alter session set optimizer_index_cost_adj = 120;
>
> Session altered.
>
> SQL> alter session set optimizer_index_cost_adj = 10001;
> ERROR:
> ORA-00068: invalid value 10001 for parameter optimizer_index_cost_adj,
must
> be
> between 1 and 10000
>
> The other one makes only sense in the range 0 to 100:
>
> SQL> alter session set optimizer_index_caching = 101;
> ERROR:
> ORA-00068: invalid value 101 for parameter optimizer_index_caching, must
be
> between 0 and 100
>
> But I can not really find much information on the net about these two
> parameters. It is most of the time 'try it'!
> Both can be changed at session level. Check what happens with your
execution
> plans.
>

Hi Luc,

If I may, I would recommend the paper "The Search For Intelligent Life In The CBO" by Tim Gorman at www.evdbt.com as an excellent introduction on how to possibly tune these parameters. It provides some sensible techniques on how to determine appropriate values rather than the broad generalisations (which may or may not be suitable) offered by others in this thread.

Good Luck

Richard Received on Wed Oct 08 2003 - 07:10:22 CDT