Analytic%20Extensions%20to%20SQL%20in%20Oracle9i

1
(No Transcript)
2
RBO RIP
Session id 40178

• George Lumpkin
• Director Product Management
• Oracle Corporation

3
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

4
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

5
Background Query OptimizationQuery
Transformations

• Change fundamental structure of query to enable
  more possible execution strategies
• Include view and subquery transformations,
  materialized view rewrites, star transformations,
  etc.
• Example
• select from dept
• where deptno in
• (select deptno from emp where job CLERK)
• select d. from dept d,
• (select distinct deptno from emp where job
  CLERK) e
• where d.deptno e.deptno

6
Background Query OptimizationAccess Path
Selection

• Construct the actual execution strategy to be
  used for a given query
• For each table, choose the access path (table
  scan, index scan, etc)
• For each join, choose the join method
  (nested-loop, sort-merge, hash, etc)
• Choose the join order for the tables
• Can be viewed using Oracles EXPLAIN PLAN facility

7
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

8
Background Query OptimizationCBO Statistics

• CBOs 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.

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
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)

11
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

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
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

14
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 gt 30 tables
• .25 of SQL statements (1200 statements)
  required tuning/modification

15
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

16
Oracle 10gZero-effort query optimization

• Automatic statistics management
• Enhanced query optimization
• Automatic SQL Tuning

17
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
• (OPTIONSgtGATHER AUTO)
• DBA determines when to analyze statistics
• In Oracle 10g, statistics are fully automated

18
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

19
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

20
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

21
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
22
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

23
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)

24
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

25
Migration methodology

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

26
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)

27
Gather Statistics

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

28
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

29
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

30
Migrate end-users

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

31
More info

• ltNote189702.1gt Rule Based Optimizer is to be
  Desupported in Oracle10i
• ltNote222627.1gt Migrating to the Cost-Based
  Optimizer
• Documentation
• White-paper

32
(No Transcript)