It’s cleanup time!!
When upgrading to a new database release, especially Oracle Database 11g Release 1 or Release 2, you should always remove old init.ora parameters from the init.ora/spfile when you upgrade. For instance we’ve had a lot of useful (and necessary) tuning parameters in Oracle 9i environments such as _always_anti_join=off, star_transformation=false or several events.
Make sure you remove all of them when you upgrade as keeping them will definitely slow down the database performance in the new release. You don’t believe this? Then see this result of a customer case having a packaged OLTP workload for the upgrade testing. It shows the comparision of this workload with all “old” 9i parameters to a small and cleaned up init.ora:
In this case we’ve removed just all “old” 9i parameters from the init.ora and started with a very basic init.ora in Oracle Database 11g with only the required parameters such as diagnostic_dest and the indentical memory parameters taken from the 9i environment.
And this has speed up the processing in Oracle Database 11g by 14,5%!!!
You could always do a ‘create pfile=’/tmp/init.ora’ from spfile;’ and modify it. Use the modified init.ora when starting up your database in upgrade mode by typing ‘startup upgrade pfile=’/tmp/init.ora’ and then don’t forget to do a ‘create spfile from pfile;’ once the database is started up.
By the way, the pre-upgrade check script utlu112.sql downloadable from within MOS Note:884522.1 will give you all the parameters to change in the new release. And it can be run anytime before the upgrade to check your current database release.
Give this a try and remove or comment out all the “old” stuff!!
