Re: optimizer parameters

I know about this paper.

Indeed, the optimizer_index_caching parameter can be calculated by looking at the ratio of the logical and physical reads. But these counters are totals of data AND index blocks. But we need the counters for the index blocks ONLY (to be more accurate).

Another thing is that in a DEVELOPMENT environment, these counters do not reflect the PRODUCTION environment. Many developers run 'test' (and most of the time 'bad') SQL that will make the results of these counters more or less useless. Maybe you should measure this ratio in the production environment, calculate the value of the parameter and copy it over to the development environment and do your tuning?

I will re-read the paper if have my other computer switched on (this one has no MSWORD installed).

Thanks for the reaction.

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:O8Tgb.141633$bo1.37865_at_news-server.bigpond.net.au...
> "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 - 17:11:41 CDT