BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter