Performance issues when upgrading from 8i to 9i

[Be Gentle]

When upgrading the Database from 8i to 9i all users complained about everything being so slow. Due to the number of simultaneous users (50 - 60) in the system it was more or less impossible to track what caused this. Also, when running on 9i in a test environment with only a few users (3 - 5), no one complains...

Does anyone know of any changes which could cause such behaviour?

We had no time to perform any real tests since we had to go back to 8i a.s.a.p. in order not to slow down production.

[Dilippatel]

Can you find your Execution path changed in 9i compared to 8i. You can take test on 9i.

Use statspack to monitor the 9i when test is on. This should help to point out for possible bottlenack.

I think Test actually is better than build openion from anybody else's experices.

Cheers,

[edsperez]

Is there a way you can compare the execution plan to an identical database? I had the same problem when I moved one of our database from 8.1.7.2 to 9.2.0.1. 9i took a different execution plan. What you can do is to use hints in 'all of your queries', if this is not possible you can try setting this parameter to use the old 8i execution plan. This worked for me.

optimizer_features_enable = 8.1.7

[marist89]

Originally posted by edsperez
What you can do is to use hints in 'all of your queries'

This is a recipe for disaster. Understand what changes were made and deal with them appropriately.

[bolero]

Mate,
Change this parameters and bounce the database.

optimizer_index_caching=99
optimizer_index_cost_adj=25

& see the magic.

[OracleDoc]

Originally posted by edsperez
Is there a way you can compare the execution plan to an identical database? I had the same problem when I moved one of our database from 8.1.7.2 to 9.2.0.1. 9i took a different execution plan. What you can do is to use hints in 'all of your queries', if this is not possible you can try setting this parameter to use the old 8i execution plan. This worked for me.

optimizer_features_enable = 8.1.7

HOLY Crap man you're basically defeating the purpose of ugrading. Next thing you know you'll be wanting to convert from LMT's to dictionary?!

[SANJAY_G]

First thing I would do is 'Analyze all tables/indexes'
Next run staspack and see what is the bottleneck.
If some particular SQL statements are slow then tune them.