Oracle tuning optimizer index cost adj and optimizer index caching
- by Darryl Braaten
What is the correct way to set the optimizer index cost adj parameter for Oracle. As a developer I have observed huge performance improvements as this parameter is lowered. Common queries are reduced from 2 seconds to 200ms. There are lots of warnings on the net that lowering this value will cause dire issues with the database, but no detail is given on what will start going wrong.
I am currently only seeing only an upside, much improved application performance and no downside. I need to better understand the possible negative repercussions of adjusting these parameters.