Migrating to the Cost-Based Optimizer Oracle Tips by Burleson Consulting

As we know, the rule-based optimizer has remained essentially unchanged since Oracle6, while the cost-based optimizer is being constantly enhanced. Starting with Oracle8i, the CBO has been enhanced to the point where it provides better overall execution plans, and many shops are looking to migrate their SQL to the CBO after installing Oracle8i. This migration is very worthwhile, but there are pitfalls that must be considered.

Retraining the development staff

Seasoned Oracle developers who have been using the RBO for many years will find the CBO to be very foreign. Unlike the RBO, the CBO uses opposite set of rules for determining the driving table, the wealth of CBO hints can be intimidating, and the query transformation techniques employed by the CBO can be aggravating to the novice. To ensure a successful migration to the Oracle8i CBO, the Remote DBA and development staff should attend the appropriate training to ensure that they fully understand the CBO features.

Choose your Cost-based Optimizer Philosophy

Once your company has migrated to Oracle8i and made a decision to use the cost-based SQL optimizer, you must make a very important decision. Especially important in undertaking a migration to the CBO is documenting your companies’ philosophy regarding the relationship between the CBO statistics and the use of optimizer plan stability. Before undertaking a migration to the CBO, the Oracle Remote DBA must develop a philosophy regarding CBO statistics and their ability to dynamically change SQL execution plans. This choice is heavily dependent upon the nature of the Oracle database, and either choice may be optimal.

Remember, some Oracle professionals subscribe to the theory that for any SQL query, there exists one, and only one, optimal execution plan. Once this optimal execution plan is located, it should be made persistent with optimizer plan stability. In contrast, other shops want their SQL to change execution plans whenever there has been a significant change to the CBO statistics.

Stable shops where the table statistics rare change will want to employ optimizer plan stability to make execution plans persistent, while shops where the CBO statistics frequently change will tune their queries without optimizer plan stability so that run-time optimizer is free to choose the most appropriate execution plan, based on the CBO statistics.

The choice of SQL philosophy has a dramatic impact on the approach to SQL tuning and maintenance.

• Table and Index Statistics The dynamic philosophy relies heavily of the table and index statistics, and these statistics must be re-computed each time that a table has a significant change, while the static philosophy does not rely on statistics.

• Optimizer Plan Stability The dynamic shop does not use optimizer plan stability because they want the freedom for the execution plan to change whenever there is a major change to the data inside the tables. Conversely, the static shop relies on optimizer plan stability to make their tuning changes permanent and to improve SQL execution time by avoiding re-parsing of SQL statements.

• Cursor Sharing The dynamic shop often has SQL that is generated by ad-hoc query tools with hardcoded literal values embedded within the SQL. As we know, hardcoded literal values make the SQL statements non-reusable unless the Oracle8i cursor_sharing=force is set in the Oracle initialization file.  Shops that are plagued with non-reusable SQL can adopt either the persistent or the dynamic philosophy.  To use optimizer plan stability with non-reusable SQL, the Remote DBA will set cursor_sharing=force, and then extract the transformed SQL from the library cache and use optimizer plan stability to make the execution plan persistent.

Let’s take a closer look at these competing philosophies so that you can see which philosophy best fits your organization.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.