We are using Oracle 9i with 8i optimizer. We want to upgrade it to Oracle 9i optimizer. The multitier application is built in VB, COM and Oracle. It uses SQL queries written in VB program and SPs, functions, triggers, packages, etc. in the backend. Once we upgraded to 9i optimizer and it was a disaster. All the batch jobs written in VB were taking a lot more time than expected. We reverted it back to 8i optimizer again. Now we again want to upgrade it to 9i optimizer. Do we need to change any parameters in the database for a smooth run? Could you please assist us by describing how 9i optimizer is upgraded when compared to 8i optimizer from the point of view which I have described above?
Ensuring that the Oracle optimizer chooses the most efficient execution path involves doing several things. One is to ensure that you collect the appropriate statistics so that the optimizer can choose the most efficient path. Second, you must explain SQL that is performing poorly to determine what path that the optimizer has chosen. It is possible that you may also have to change some initialization parameters in order for the SQL to run more efficiently. All of these things take time and extensive testing. I would recommend refreshing a development or test system from production, and testing this in a non-production environment prior to moving it to production. Even after testing this extensively in a non-production environment, you may still have to make adjustments in your production environment.
Dig Deeper on Oracle database installation, upgrades and patches
Can I use /var/opt/oracle/oratab to specify listener information?
We would like to migrate our database from Oracle 8.1.7 to Oracle 10g. We would like to know the impact for our application developed using Delphi ...
I have been trying to install Oracle 8.1.7 on SUSE Linux 9.0 and got the error:
"Error in invoking target install of makefile /opt/oracle/...../*.mk."
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.