Re: optimizer parameters

Phillip Tien wrote:

>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
>
>

What you are asking is not something that can be predicted in advance. Follow the vendor's advice and see
what happens. Worst case ... you change back to the original settings.

BTW: Not providing version information and other relevant facts makes it rather hard for anyone to help you.
Something you might wish to consider in future posts.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)