Add the following settings in init.ora
cursor_sharing = FORCE
optimizer_mode = CHOOSE
optimizer_index_cost_adj = 5
optimizer_index_caching = 95
cursor_sharing = FORCE
This parameter allows the optimizer to recognize similar statements and reduce parsing, even when bind variables are not being used. It
provides best benefit in large load environments. Note, however, that in Oracle 8i cursor_sharing=FORCE could lead to some sub-optimal query execution plans.
optimizer_mode = CHOOSE
This defines the behaviour of the optimizer. When set to CHOOSE, the optimizer will use cost-based optimization if statistics are available, and rule-based when statistics are not available. As certain applications and/or releases provide better performance in rule vs. cost-based optimization, this value provides the greatest flexibility as statistics need only be updated or deleted to switch optimization modes, without having to restart the instance.
optimizer_index_cost_adj = 5
The parameter tells Oracle's optimizer to always make indexes cheaper than full table scans. The default for this parameter is 100, and by changing it to 5, indexes look a lot cheaper to Oracle, and nested loop operations will be favoured.
optimizer_index_caching = 95
The parameter tells Oracle's optimizer to assume that it can expect to find a certain percentage of index blocks in the buffer cache. The default for this parameter is 0, and by changing it to 95, indexes look a lot cheaper to Oracle, and nested loop operations will be favoured.
cursor_sharing = FORCE
optimizer_mode = CHOOSE
optimizer_index_cost_adj = 5
optimizer_index_caching = 95
cursor_sharing = FORCE
This parameter allows the optimizer to recognize similar statements and reduce parsing, even when bind variables are not being used. It
provides best benefit in large load environments. Note, however, that in Oracle 8i cursor_sharing=FORCE could lead to some sub-optimal query execution plans.
optimizer_mode = CHOOSE
This defines the behaviour of the optimizer. When set to CHOOSE, the optimizer will use cost-based optimization if statistics are available, and rule-based when statistics are not available. As certain applications and/or releases provide better performance in rule vs. cost-based optimization, this value provides the greatest flexibility as statistics need only be updated or deleted to switch optimization modes, without having to restart the instance.
optimizer_index_cost_adj = 5
The parameter tells Oracle's optimizer to always make indexes cheaper than full table scans. The default for this parameter is 100, and by changing it to 5, indexes look a lot cheaper to Oracle, and nested loop operations will be favoured.
optimizer_index_caching = 95
The parameter tells Oracle's optimizer to assume that it can expect to find a certain percentage of index blocks in the buffer cache. The default for this parameter is 0, and by changing it to 95, indexes look a lot cheaper to Oracle, and nested loop operations will be favoured.
Courtesy: powerlink.emc.com
No comments:
Post a Comment