Friday, November 7, 2008

OPTIMIZER_INDEX_CACHING

Property

Description

Parameter type

Integer

Default value

0

Modifiable

ALTER SESSION, ALTER SYSTEM

Range of values

0 to 100

OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.

The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.

You can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.

Note:

1. Introduced:             8.0.5

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘OPTIMIZER_INDEX_CACHING’;

NAME

VALUE

IS

DEFAULT

ISSES_

MODIFIABLE

ISSYS_

MODIFIABLE

ISINSTANCE_

MODIFIABLE

IS

DEPRECATED

DESCRIPTION

optimizer_index_caching

0

TRUE

TRUE

IMMEDIATE

TRUE

FALSE

optimizer percent index caching

Oracle initializatoin parameters

No comments:

Post a Comment