We just upgraded our SAP ERP systems. The upgrade involved AIX upgrade to 5.2 and Oracle upgrade from 8.1.7 to 9.2. We also added memory to all application servers and the database server. Since the upgrade, the system performance is slower than before. We believe we are on the latest patch level and need some help with what to check next.
First, I have to say that I don't know what the wisdom is behind performing two (or more) major upgrades at the same time. If you're like most sites I've seen, you probably also lack a load testing environment so you weren't able to perform an upgrade and test it under load.
If you have statspack information from before the upgrade, then I'd compare reports from before and after the upgrade. If you don't have statspack reports from before the upgrade, shame on you -- you'll have to get your hands dirty.
I would guess for upgrades of this magnitude, you've left out quite a bit of information about the upgrade. Here are some things I'd look into before giving up and downgrading everything:
How was the ugprade done: is export/import or upgrade in place? If it was an export/import, then you may have storage contention issues due to all the blocks becoming more compressed than they were before.
Was the SGA resized? If it was, then put it back to its original size. If it wasn't, then why did you add more memory to the database server?
Is the application server running under higher CPU load than it was before? Do you have OS-related stats from before the upgrade (vmstat, iostat, sar, etc.) to compare the runq lengths? Compare the overall I/O volume from the current system to the I/O volume from prior to the upgrade to see if there's generally more I/O going on now.
Can you, on a separate system, restore the 8.1.7 database and generate explain plans for a few choice queries that may impact the application overall? If you can, compare them with the same plans from the production system and see how they match up.
Of course, you should update whatever optimizer statistics you had gathered prior to the upgrade with the most recent data now that you've upgraded. The optimizer and the statistics gathering algorithms both underwent some big changes and leaving the old statistics around may not make the new optimizer happy.
If all else fails, check out the init.ora parameter optimizer_features_enable and try setting it to 8.1.7 in your new database. If that restores your performance to "normal," then you should look to your application vendor for assistance on why their SQL doesn't work well under 9.2 when it ran okay under 8.1.7.
Dig Deeper on Oracle database installation, upgrades and patches