Q

Queries, loads and sorts slow after upgrade

This Content Component encountered an error

We just did an upgrade of our Oracle database from 8.1.7 to 9.2.0.4 (64 bit) and the OS upgrade from Solaris 5.8 to 5.9. We have a RAC environment. But queries, loads and sorts are now taking a longer time to run than before. Is there a parameter that you can think of that may help with this issue? I ran into a similar issue once before and it turned out that the new optimizer in the database just didn't do as well for certain SQL statements...

as the old optimizer did. One test you may want to run is to set the OPTIMIZER_FEATURES_ENABLE back to the old release (8.1.7) and then run your application to see if the performance returns to "normal." Before trying this, you should probably extract a few queries from your application (pick on the ones that seem to be especially slower now) and time them on your current setup first. Then change the OPTIMIZER_FEATURES_ENABLE parameter (you'll have to restart the instances) and time the same queries to see if you notice a difference.

In our case, we looked at a lot of different variables (OS, storage, database statistics, wait times, etc) and ended up with the conclusion that our 20% drop in performance was due to SQL that ran 20% slower in the newly upgraded database.

In general, I would not recommend making more than one change at a time, though I know it does happen and I've been forced to do it too when timing or other factors dictate. Your job is harder now since the OS upgrade may be a factor and the database upgrade is almost certainly a factor, but it is nearly impossible to tell which one is having the most impact on your application at this point.

Good luck!

This was first published in April 2004

Dig deeper on Oracle database installation, upgrades and patches

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close