Manage Learn to apply best practices and optimize your operations.

How to tune SQL UPDATE statements for an Oracle 10g upgrade

Are your SQL statements running slow after an Oracle upgrade? Expert Brian Peasland explains why and how to tune SQL UPDATE statements before an Oracle 10g upgrade.

We have a nightly batch job that refreshes the data warehouse tables. The PL/SQL and SQL code is very old ( from 2002). It runs OK in 9i, but when we test in 10g some of the UPDATE statements are extremely slow. The job runs forever, so we have to kill the job. How would you go about the problem? Are there any parameters that you would change?
It is not uncommon for an SQL statement in one major version to run slower when upgrading to a new major version. One would need to tune the SQL statements on the new version. This should be done in a test environment before touching production. I would start by examining the UPDATE statements that need to be tuned, and you may need to look at adjusting initialization parameters that affect the Cost Based Optimizer's behavior in Oracle 10g, or look toward Plan Stability.

Dig Deeper on Oracle database design and architecture

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.