Remove "old" parameters and events from your init.ora

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:

2010_03_09_Mel000.jpg

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 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!!

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers