Transcript Analytic Extensions to SQL in Oracle9i

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
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 Optimization
RBO 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 Optimization
CBO 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
 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
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 RBO
Reasons 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 thirdparty applications
–
–
70-80% of applications using CBO today (per user surveys)
Adoption growing as more customers migrate to Oracle9i
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
Peer pressure
 Major applications use the CBO:
–
–
–
SAP
Oracle eBusiness Suite
Peoplesoft
 User-group surveys show CBO is used in 7080% 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
 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
Oracle 10g:
Zero-effort query optimization
 Automatic statistics management
 Enhanced query optimization
 Automatic SQL Tuning
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
Automatic Statistics Gathering
in 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
Comprehensive SQL Tuning
Automatic Tuning Optimizer
Statistics
Analysis
SQL
Profiling
SQL Tuning
Advisor
Detect Missing or
Stale Statistics
Build a SQL
Profile
DBA
Access Path
Analysis
Detect Missing
Indexes
SQL Structure
Analysis
Detect Poor SQL
Constructs
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
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
 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
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 CostBased Optimizer”
 Documentation
 White-paper