Ask the Expert

Queries, loads and sorts slow after upgrade

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: