- We have migrated one of
the critical production database from 10g to 11g(11.2.0) successfully and also
application team confirmed everyting working as expected. After a week time,
application team reported issue as few of the SQLs(huge select) are running very
slow (in 10g they completed in 2.5 mintues, but 11g those are not completed
after 10 mintues also).
- We started working on this issue and find the solution, "There is an oracle optimizer paramemter "OPTIMIZER_INDEX_COST_ADJ", which by default
has a value of 100 in 11g.
- This value means that the optimiser considers a full table scan to have the same cost as
using an index. Consequently queries were not using the indexes at all and getting slower
and slower as the tables became larger.
- We have tested by setting "OPTIMIZER_INDEX_COST_ADJ" with different values 20,15 and 10 and we observed massively improves performance with value 10, the query which is completed in 2.5 mintues in 10g, after setting this parameter value to 10 in 11g it just completed in 28 seconds.
Note: The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
- We started working on this issue and find the solution, "There is an oracle optimizer paramemter "OPTIMIZER_INDEX_COST_ADJ", which by default
has a value of 100 in 11g.
- This value means that the optimiser considers a full table scan to have the same cost as
using an index. Consequently queries were not using the indexes at all and getting slower
and slower as the tables became larger.
- We have tested by setting "OPTIMIZER_INDEX_COST_ADJ" with different values 20,15 and 10 and we observed massively improves performance with value 10, the query which is completed in 2.5 mintues in 10g, after setting this parameter value to 10 in 11g it just completed in 28 seconds.
Note: The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
No comments:
Post a Comment