Q
Manage Learn to apply best practices and optimize your operations.

Package slow after upgrade

Our database was recently upgraded from Oracle 8.0.6 to 9.2.0.7, just before I started to work here. They have a package with a maximum of 32 input report parameters that was working very well under the 8.0.6 version and that is now extremely slow, and in some cases never returns at all.

Our database was recently upgraded from Oracle 8.0.6 to 9.2.0.7, just before I started to work here. They have a package with a maximum of 32 input report parameters that was working very well under the 8.0.6 version and that is now extremely slow, and in some cases never returns at all, under 9.2.0.7 -- is there anything we should be aware of or investigate regarding the 9.2.0.7 setup? Incidentally, I know of several avenues we can pursue, but hate to waste the time if we can be pointed properly. Thank you!
I'd suggest tracing the execution of the package -- the difference in performance most likely is attributable to SQL in the package body, and a DBMS_SUPPORT trace will give you details of how Oracle is spending time processing SQL. Sometimes version upgrades wreak havoc on optimizer plans; the causes can be subtle, so the first thing you need is details on what's going on. Ideally, run a trace on an 8.0.6 instance as well, so you can compare the two. Once you know how SQL is being processed, you can start to determine the reasons, focusing your investigation on time-consuming operations.

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close