Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> optimizer parameters
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...
-- PhillipReceived on Tue Oct 07 2003 - 11:21:50 CDT