RSS Feed

Method R Discussion Group

What is the best way to ensure I won’t have performance problems when we do our 10g upgrade this year?

The safest way is to create an exact duplicate of the production environment, upgrade it, and measure response times for realistic load as well as measure consumed capacity. This is almost never practicle given its cost.

The next best thing to do is to ensure there are no unacceptable differences in execution plans between your production 9i database and your test 10g database.

If we assume that you will have to assess execution plan differences and you have to rush the upgrade, then I recommend you upgrade the rdbms but set the compatible parameter to your production 9i database. Even though this is a much safer approach, you should still test it.

Therefore, my approach would be to perform the upgrade in two stages.

Stage 1
In your test database, set the compatible parameter to the value of your production database's version. Copy your production statistics to your test database.
Compare execution plans in your production database with the predicted executions plans in your test database. If you have zero differences or you have acceptable differences, then perform integration testing. Otherwise, contact someone to help explain the differences.
If integration testing results are acceptable, then upgrade production and set compatible to the version of your previous production rdbms version.

Stage 2
In your test database, unset the compatible parameter.
Compare execution plans in your production database with the predicted executions plans in your test database. If you have zero differences or you have acceptable differences, then perform integration testing. Otherwise, contact someone to help explain the differences.
If integration testing results are acceptable, then unset compatible in production.