Our current application is using Oracle 9.2.0.6 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?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top