Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> optimizer parameters

optimizer parameters

From: Phillip Tien <tienp_at_wholefoods.com>
Date: Tue, 07 Oct 2003 11:21:50 -0500
Message-ID: <3F82E81E.98A92384@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 - 11:21:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US