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.