Re: optimizer parameters

sorry, I totally forgot to provide other relevant info. We're running 8.1.7.4.0 (64-bit) on Solaris 8 on a Sun V880 with 8CPUS and 16 gigs of RAM. I was hoping for someone with experience with tweaking these two parameters. Thanks.

Daniel Morgan wrote:

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

--
Phillip