1 / 30

RBO RIP

Session id: 40178. RBO RIP. George Lumpkin Director Product Management Oracle Corporation . What, why, and how. What changes are made to the RBO in 10g Why migrate to the CBO CBO is proven CBO provides all features necessary to simplify management

starbuck
Download Presentation

RBO RIP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session id: 40178 RBO RIP George Lumpkin Director Product Management Oracle Corporation

  2. What, why, and how • What changes are made to the RBO in 10g • Why migrate to the CBO • CBO is proven • CBO provides all features necessary to simplify management • CBO enables many, many other database features • How to migrate to the CBO

  3. Background: Query Optimization • One sentence definition: Find the most efficient mechanism for executing any SQL statement • A query optimizer is designed simplify SQL development • A query optimizer shields the application developer from the details of query execution • Two main components: • Query Transformations • Access Path Selection

  4. Background: Query OptimizationRBO vs. CBO • Oracle provides two query optimizers: • Rule-based optimizer (RBO) • Chooses an execution strategy based upon heuristics • Entirely deterministic based upon the schema and SQL statement • Cost-based optimizer (CBO) • Chooses an execution strategy based upon an estimated cost • Execution plans depends not only on the SQL and schema, but also the characteristics of the database objects and the amount of available resources

  5. Background: Query OptimizationCBO Statistics • CBO’s cost is based upon statistics • Database object statistics • Statistics which describe the database objects involved in the query, e.g., the number of rows in a table, the number of distinct values in a column, and the number of leaf blocks of an index. • CPU Statistics • Statistics on the relative performance of the hardware platform. • Buffer-cache statistics • Statistics that describe whether a given table or database object is typically cached or not.

  6. What, why, and how • What changes are made to the RBO in 10g • Why migrate to the CBO • CBO is proven • CBO provides all features necessary to simplify management • CBO enables many, many other database features • How to migrate to the CBO

  7. What is happening to the RBO • In Oracle Database 10g, the rule-based optimizer is no longer supported • The RBO is not ‘gone’ (at least not yet); it is simply not supported • No bugfixes will be provided to RBO for 10g • Almost no regression testing of RBO for 10g • In future releases, the RBO may be removed altogether • See support note 189702.1: “Rule Based Optimizer is to be Desupported in Oracle10i” (May 2002)

  8. What is happening to the RBOReasons for de-supporting the RBO • The existence of the RBO prevents Oracle from making key enhancements to its query-processing engine • The removal of the RBO will permit Oracle to improve performance and reliability of the query-processing components of the database engine. • The use of the RBO prevents applications from leveraging many of the key features and enhancements introduced since Oracle7. • CBO is widely used today, by home-grown and third-party applications • 70-80% of applications using CBO today (per user surveys) • Adoption growing as more customers migrate to Oracle9i

  9. What, why, and how • What changes are made to the RBO in 10g • Why migrate to the CBO • CBO is proven • CBO provides all features necessary to simplify management • CBO enables many, many other database features • How to migrate to the CBO

  10. Peer pressure • Major applications use the CBO: • SAP • Oracle eBusiness Suite • Peoplesoft • User-group surveys show CBO is used in 70-80% of all applications • CBO adoption will continue to rise as more applications migrate to Oracle9i

  11. Oracle11i E-Business Suite uses Cost-Based Optimizer • Huge optimizer workload: • 479,000 SQL statements • 24,000 tables • 40,000 indexes • 20,000 views • 30,000 packages • Queries referencing > 30 tables • .25% of SQL statements (~1200 statements) required tuning/modification

  12. What, why, and how • What changes are made to the RBO in 10g • Why migrate to the CBO • CBO is proven • CBO provides all features necessary to simplify management • CBO enables many, many other database features • How to migrate to the CBO

  13. Oracle 10g:Zero-effort query optimization • Automatic statistics management • Enhanced query optimization • Automatic SQL Tuning

  14. Gathering Optimizer Statistics Accurate optimizer statistics are crucial for good performance • Oracle8i: Good • Oracle provides robust DBMS_STATS package • DBA determines how to gather statistics • DBA determine when to gather statistics • Oracle9i: Better • Oracle determines how to gather statistics • Statistics can be gathered using a single command: execute DBMS_STATS.GATHER_DATABASE_STATS (OPTIONS=>’GATHER AUTO’); • DBA determines when to analyze statistics • In Oracle 10g, statistics are fully automated

  15. Automatic Statistics Gatheringin Oracle 10g • How it works: • Init.ora setup: STATISTICS_LEVEL = TYPICAL (or higher) • TYPICAL is the default setting • Statistics gathered as a predefined job (GATHER_STATS_JOB) scheduled by the unified scheduler • Statistics gathered using DBMS_STATISTICS package • Oracle implicitly determines: • The database objects which have missing or stale statistics • The appropriate sampling percentage necessary to gather good statistics on those objects • The appropriate columns which require histograms and the size for those histograms • The degree of parallelism for statistics-gathering

  16. Complete statistics management • Statistics are automatically saved and can be restored • Old statistics can be viewed in the ALL/DBA/USER_OPT_STATS_HISTORY • Statistics are stored in the workload repository • Statistics can be locked • Auto-gathering processes will not modify locked statistics • Statistics can be manually specified by DBA • Using DBMS_STATS.SET_TABLE/INDEX_STATISTICS • Manual statistics gathering may still be required for: • Bulk loads (e.g. in data warehouse environments) • Volatile tables

  17. Enhanced Query Optimization • Sophisticated cost model extensions • Broad cost model includes CPU and cache information • Graceful behavior with missing/incomplete statistics • ‘Dynamic statistics’ enabled by default

  18. Automatic SQL Tuning Overview Comprehensive SQL Tuning Automatic Tuning Optimizer SQL Tuning Advisor Detect Missing or Stale Statistics Statistics Analysis See #40173: The Self-managing Database: Guided Application and SQL Tuning SQL Profiling Build a SQL Profile DBA Detect Missing Indexes Access Path Analysis SQL Structure Analysis Detect Poor SQL Constructs

  19. What, why, and how • What changes are made to the RBO in 10g • Why migrate to the CBO • CBO is proven • CBO provides all features necessary to simplify management • CBO enables many, many other database features • How to migrate to the CBO

  20. Features not supported by RBO • Data structures • Partitioning • Index-organized tables • Function-based indexes • Bitmap indexes • Access techniques • Parallel Execution • Full outer joins • Query transformations • Materialized views • Dozens more … (need to list optim features)

  21. What, why, and how • What changes are made to the RBO in 10g • Why migrate to the CBO • CBO is proven • CBO provides all features necessary to simplify management • CBO enables many, many other database features • How to migrate to the CBO

  22. Migration methodology • Create a test environment • Gather statistics • Determine init.ora settings • Validate performance • Migrate end-users

  23. Create a test environment • Key technique #1: • If you have a test/dev system, you can export statistics from the production system to the test/dev system • Key technique #2: • If you do not have a suitable test system, you can test the CBO behavior on the production system • Set OPTIMIZER_MODE = RULE in init.ora • Gather optimizer statistics • In your test session, ALTER SESSION SET OPTMIZER_MODE = CHOOSE (or other appropriate setting)

  24. Gather Statistics • ‘Bad’ statistics is the single most common cause of poor query optimization • Gather statistics on all database objects before trying the CBO

  25. Determine appropriate init.ora settings • The key parameter is OPTIMIZER_MODE • Hint: FIRST_ROWS_n provides the most similar to RBO • Always start simple • Do not use other optimizer-related parameters until all choices of OPTIMIZER_MODE are considered

  26. Validate performance • The most difficult step in the migration • Need to identify key SQL statements and compare performance • ‘Bad’ queries can be corrected using a variety of techniques: • Stored outlines • Hints • SQL modifications • ‘Bad’ queries should be rare • Note experience of Oracle eBusiness Suite • When testing using Oracle 10g, use Automatic SQL Tuning

  27. Migrate end-users • End-users can be migrated one-by-one • Login trigger can set OPTIMIZER_MODE for each end-user

  28. More info • <Note:189702.1>: “Rule Based Optimizer is to be Desupported in Oracle10i” • <Note:222627.1>: “Migrating to the Cost-Based Optimizer” • Documentation • White-paper

More Related