Beware of Worse Oracle performance after Migration!!

Author: Don Burleson

Page: 1 2

Database Migration:

Every database administrator has to migrate his database now or then. We mostly hear a common complain for those migrating from Oracle8 or Oracle8i to Oracle10g that the performance gets worse. It is recommended to migrate the database directly to Oracle10g because the automated statistics collection and optimization can help a lot. Especially if you are not familiar with Oracle tuning then it is strongly recommended that you migrate your database directly to Oracle10g.

Sub-optimal SQL Execution:

If your well-optimized database performs poorly after a migration to a new release then most possibly it is due to sub-optimal SQL execution.

Sub-optimal PL/SQL:

Sub-optimal PL/SQL is rarely the cause of poor database performance after migration to some other release.

Poor Database Performance after Migration:

If your Oracle database performs poor after database migration then follow these steps.

Evaluating Execution Plan:

• Compare an execution plan from the pre-migration to a post-migration execution plan
• Find bad-performing SQL and dive-in to see exactly why the SQL is executing in a sub-optimal fashion. 
• Use the set autotrace traceonly explain command in SQL<*Plus to get the execution plan, and compare the execution plan to the same SQL in your old database. 

dbms_stats utility:

• Ensure that you have re-analyzed your schema using the dbms_stats utility.
• High-quality metadata can make all the difference. 

Page: 1 2

 More Database Articles
   Database Security: Step by step guideline
   Great Tips for Protecting Oracle Listener!!
   Oracle: Achieving performance goal - Part II
   Great Tips for Successful Oracle Block Recovery!
   The Power of Oracle Virtual Column!!