Closing the Query Processing Loop in Oracle 11g

1
Closing the Query Processing Loop in Oracle 11g

• Allison Lee, Mohamed Zait

2
Agenda

• Motivation
• Solution Overview
• Cardinality Feedback
• Adaptive Cursor Sharing
• General Feedback Process
• Experimental Results
• Conclusions

3
Motivation
4
Motivation

• Two query optimization problems
• Estimating cardinality of intermediate results
• Optimizing queries containing query variables

5
Problem Cardinality Estimation

• Cardinalities of tables affect the optimizer cost
  estimate
• Cardinality estimates are often poor quality
• Missing statistics
• Correlation between columns
• Complex operators
• Bad cardinality estimate results in poor plan

6
Problem Query Variables

• select avg(e.salary), d.department_name
• from employees e, departments d
• where e.job_id job_v
• and e.employee_id d.employee_id
• group by d.department_name
• Different bind values result in different
  intermediate cardinalities
• Optimal plan depends on bind value
• Plan chosen based on initial value then reused

One plan not always appropriate for all bind
values
7
Solution
8
Solution Overview

• Two phase Solution
• Collect information during execution
• Use this information during subsequent
  optimization

9
Cursor Sharing Preliminaries

• A cursor stores the chosen plan in the cursor
  cache
• Caching avoids compilation overhead on every
  execution
• When a query is submitted
• Soft parse looks for matching cursor and uses
  it
• Hard parse no match found so query is compiled
• Prior to 11g
• Use bind variables plan is shared
• Use literals plan is not shared
• Optimizer peeks at bind value to estimate
  cardinalities

10
Cardinality Feedback
11
Cardinality Feedback

• Addresses cardinality misestimates due to
• Missing statistics
• Inaccurate statistics
• Complex predicates
• Optimize the query as usual noting low quality
  estimates
• After execution compare actual cardinalities to
  estimates
• If estimates and actuals differ significantly
• Store actual values for later
• Force a re-optimization on next execution
• During optimization, use actuals instead of
  estimates

12
Example

• select e.employee_id, d.department_name
• from employees e, departments d
• where d.department_id e.department_id
• and e.job_id 'ST_CLERK'
• and e.salary lt 3000
• Difficult to estimate cardinality for equality
  AND inequality predicates

13
Adaptive Cursor Sharing
14
Bind-Aware Cursor Sharing (BACS)

• Cursor sharing is aware of binds and
  selectivities
• Cursor stores a bind profile
• Which binds values may affect plan
• Range of selectivities for each bind-containing
  predicate
• If a new bind value is outside of range, new
  cursor is built
• Cursors with same plan are merged
• Allowable selectivity range grows as cursors are
  merged

15
Example
select avg(e.salary), d.department_name from
employees e, departments d where e.job_id
job and e.department_id d.department_id
group by d.department_name
16
Example (continued)

• State of cursor cache after two executions

• Third execution
• job clerk
• sel(e.job_id job) 25
• Use second cursor, selectivity is close enough

17
Adaptive Cursor Sharing

• Overhead of bind-aware cursor sharing
• Cursor matching is slower
• Additional re-optimizations consume CPU
• More cursors vying for space in cursor cache
• Use feedback to determine if BACS is warranted
• Adaptive cursor sharing (ACS)
• Monitor queries where BACS is applicable
• Identify queries with variable data volume
• Enable BACS for these queries

18
Example
select avg(e.salary), d.department_name from
employees e, departments d where e.job_id
job and e.department_id d.department_id
group d.department_name
Big variation in rows processed gt use BACS
19
General Feedback Process
20
Related Work

• Cardinality Feedback
• IBM Learning Optimizer (Stillger et al. VLDB
  2001)
• Oracle Automatic SQL Tuning (Dageville et al.
  VLDB 2004)
• Adaptive Cursor Sharing
• SQL Server Auto Parameterization
• DB2 REOPT options

21
Experimental Results
22
Performance studies

• Oracle E-Business Suite (EBS) test database
• 60GB test system
• 14,000 tables, from tens to millions of rows
• EBS workload
• Over 300,000 queries
• Varying complexity, up to 140 tables per query
• Metrics
• Buffer gets
• Number of re-optimizations
• Number of distinct plans

23
Cardinality Feedback

• Execute 40,000 EBS queries twice each

10 of queries re-optimized, 4 get new plan
24
Performance Impact
924 queries improved by 68 214 query degraded by
29 58 overall improvement
25
Adaptive Cursor Sharing

• Approximately 400 queries average of 2.4 binds
  per query
• Queries known to generate multiple plans with
  different literals
• Machine-generated binds average of 55 bind sets
  per query

18 improvement with ACS 14 further improvement
without cursor sharing
26
Results Number of Plans

• Approximately 60 queries show multiple plans with
  ACS

20 fewer cursors with ACS vs. no sharing 45
more cursors with ACS vs. always sharing
27
Results Impact of Feedback

• Compare ACS with and without feedback

5X fewer compilations with feedback 2X fewer
cursors with feedback
28
Conclusions

• Feedback useful for two query optimization
  problems
• Intermediate cardinality estimation
• Optimization of queries with bind variables
• Solutions shipped in production release, Oracle
  11g
• Performance improvement using a real SQL workload
• Future work
• Use framework for additional feedback-based
  optimization
• Persist feedback information

29
Questions Answers
30
The preceding is intended to outline our general
product direction. It is intended for information
purposes only, and may not be incorporated into
any contract. It is not a commitment to deliver
any material, code, or functionality, and should
not be relied upon in making purchasing
decisions.The development, release, and timing
of any features or functionality described for
Oracles products remains at the sole discretion
of Oracle.