rbo rip george lumpkin director product management oracle corporation session id: 40178

RBO RIP

George Lumpkin

Director Product Management

Oracle Corporation

Session id: 40178

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

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

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

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.

What, why, and how




features


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)

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

What, why, and how




features


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

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

What, why, and how




features


Oracle 10g:Zero-effort query optimization

Automatic statistics management Enhanced query optimization Automatic SQL Tuning

Gathering Optimizer StatisticsAccurate 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

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

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

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

Automatic SQL Tuning Overview

Detect Missing Indexes

Detect Poor SQL Constructs

Build a SQL Profile

Automatic Tuning Optimizer

SQL Structure Analysis

Access Path Analysis

SQL Profiling

Statistics Analysis

Detect Missing or Stale Statistics

DBA

Comprehensive SQL Tuning

SQL Tuning Advisor

See #40173: The Self-managing Database:

Guided Application and SQL Tuning

What, why, and how




features


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)

What, why, and how




features


Migration methodology

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

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

1. Set OPTIMIZER_MODE = RULE in init.ora

2. Gather optimizer statistics

3. In your test session, ALTER SESSION SET OPTMIZER_MODE = CHOOSE (or other appropriate setting)

Gather Statistics

‘Bad’ statistics is the single most common cause of poor query optimization

Gather statistics on all database objects before trying the CBO

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

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

Migrate end-users

End-users can be migrated one-by-one– Login trigger can set OPTIMIZER_MODE for

each end-user

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

rbo rip george lumpkin director product management oracle corporation session id: 40178

Documents