Re: optimizer parameters

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.

Tom Kyte has a (small) section about these two parameters in his new book: http://asktom.oracle.com

"Phillip Tien" <tienp_at_wholefoods.com> wrote in message news:3F82E81E.98A92384_at_wholefoods.com...
> Hello,
>
> could someone give me their thoughts on these two parameters:
> optimizer_index_caching and optimizer_index_cost_adj. Currently, are
> values are:
>
> optimizer_index_caching = 50
> optimizer_index_cost_adj = 50
>
> Our vendor would like us to change these values to:
>
> optimizer_index_caching = 0
> optimizer_index_cost_adj = 100
>
> What I'd like to know is the impact these changes may have to my
> system. Currently I have a 8 CPU, 16G RAM server running our production
> environment with our SGA sized accordingly:
>
> Total System Global Area 1896406716 bytes
> Fixed Size 102076 bytes
> Variable Size 322400256 bytes
> Database Buffers 1572864000 bytes
> Redo Buffers 1040384 bytes
>
>
> Here are some quick blurbs on what these parameters affect:
>
> OPTIMIZER_INDEX_CACHING
> Description : Adjusts the cost-based optimizer's assumptions for what
> percentage of index blocks are expected to be in the buffer cache for
> nested loops joins. This affects the cost of executing a nested loops
> join where an index is used. Setting this parameter to a higher value
> makes nested loops join look less expensive to the optimizer.
> Range of Values: 0 - 100 percent.
>
> OPTIMIZER_INDEX_COST_ADJUST
> Description : Used to tune optimizer performance when too few or too
> many index access paths are considered. A lower value makes the
> optimizer more likely to select an index. That is, setting it to 50
> percent, will make the index access path look half as expensive as
> normal. Range of Values: 1 - 10000
> Default Value : 100 (The regular cost for an index access path)
>
> Thanks in advance...
>
> --
> Phillip
>
Received on Tue Oct 07 2003 - 17:20:33 CDT