Queries, loads and sorts slow after upgrade
We just did an upgrade of our Oracle database from 8.1.7 to 220.127.116.11 (64 bit) and the OS upgrade from Solaris 5.8 to 5.9. We have a RAC environment. But queries, loads and sorts are now taking a longer time to run than before. Is there a parameter that you can think of that may help with this issue?
I ran into a similar issue once before and it turned out that the new optimizer in the database just didn't do as well for certain SQL statements as the old optimizer did. One test you may want to run is to set the OPTIMIZER_FEATURES_ENABLE back to the old release (8.1.7) and then run your application to see if the performance returns to "normal." Before trying this, you should probably extract a few queries from your application (pick on the ones that seem to be especially slower now) and time them on your current setup first. Then change the OPTIMIZER_FEATURES_ENABLE parameter (you'll have to restart the instances) and time the same queries to see if you notice a difference.
In our case, we looked at a lot of different variables (OS, storage, database statistics, wait times, etc) and ended up with the conclusion that our 20% drop in performance was due to SQL that ran 20% slower in the newly upgraded database.
In general, I would not recommend making more than one change at a time, though I know it does happen and I've been forced to do it too when timing or other factors dictate. Your job is harder now since the OS upgrade may be a factor and the database upgrade is almost certainly a factor, but it is nearly impossible to tell which one is having the most impact on your application at this point.
This was first published in April 2004