Our current application is using Oracle 184.108.40.206 with RBO. We are in the process of strategizing the move to CBO. Can you advise on some good articles and things to be wary of?
Moving from the rule-based optimizer to the cost-based optimizer will involve extensive testing of your stored procedures and SQL statements. You will have to ensure that statistics gathered on your tables and indexes are appropriate as this information will assist the Oracle optimizer with choosing the most efficient execution path for the SQL executed. A good reference is the Oracle9i Release 2 (9.2) Database Performance Tuning Guide and Reference
This documentation explains the cost-based optimizer and how it works. It also explains how the optimizer performs certain operations and transforms SQL statements. My approach would be to refresh a development or test database from production, make the switch to CBO, collect statistics and start testing different functions of your application. For those jobs or tasks where you notice performance degradation, explain the SQL and determine where improvements can be made.
This was first published in September 2006