Reviewing Parameter Files

One of the headline recommendations during the Real World Performance groups presentations at Openworld 2007 was to review your parameter files as part of your upgrade process. It sounds like simple common sense, but they suggested that inappropriate parameter settings that had been implemented during version 7 days for long forgotten reasons were often the root cause of performance problems on 9i or 10g databases that they were asked to investigate. One of their first actions was to review the parameters carefully and question them.

I'm sure you'd find most Oracle consultants would agree. To give one example, I've heard Jonathan Lewis banging on constantly about the need to review whether optimizer_index_cost_adj should still be in your 9/10/11 parameter files. I happen to work at a site where they are very married to their OICA setting to the extent that it's in the standard build process! Because of past problems, they're very reluctant to change it, but surely it has to be removed eventually if it's inappropriate?

So thanks to Dominic Delmolino for raising the issue over on his blog.