Validating the Oracle SQL Engine

1
Validating the Oracle SQL Engine

• Allison Lee, Mohamed Zait, Thierry Cruanes, Rafi
  Ahmed

2
Agenda

• Background
• Motivations
• Query Structure Validation
• Query Processing Validation
• Conclusion

3
Background

• Optimizer operates on an internal representation
  of a query (created in early phase of SQL
  compilation)
• Generates alternative execution plans using
• Transformations (subquery unnesting, view
  merging, )
• Join orders (A 8 B, B 8 A)
• Join methods (hash, merge)
• Access paths (index lookup, full scan)
• Picks one plan with the lowest cost
• Internal query structures are modified
• Shape (view structure merged into its parent
  structure)
• Annotations added to operators

4
Background
Costed Plan
Chosen Plan
Not Visited
5
Motivation

• Optimizer decisions affected by
• User configuration of the DBMS
• Hardware characteristics
• Database statistics
• Integrity constraints
• Quality of testing is limited
• Only one plan is executed
• Search space is constrained
• Inconsistencies unnoticed
• DBMS users may encounter defects
• A non tested plan is picked
• A different search space is visited
• Different data sets

6
Motivation
Costed Plan
Chosen Plan
Not Visited
7
Solution

• Alter behavior during testing to
• Detect sleeper bugs (dormant until activated by
  a combination of events)
• Validate query structures
• Maximize leverage of existing tests
• Generate and execute several plans
• Visit wider search space
• Resolve sleeper bugs
• Query execution
• Query compilation

8
Solution
Costed Plan
Chosen Plan
9
Problem Query Structure Corruption

• Phases of compilation share a query
  representation
• Query is updated/transformed by various
  components
• Corruption by one phase can cause errors later

10
Solution Query Structure Validation

• Check the validity of query structures
• Find suspicious code that may cause failures
• Checking the validity at various points pinpoints
  component that may be at fault
• Types of failures prevented
• Crashes
• Wrong results
• Internal errors

11
Query Validation Framework (QVF)

• Framework checks invariants on query structures
• Experts on parts of structure can register
  invariants
• Invariants are registered declaratively
• Type of structure invariant applies to
• Callback to check invariant
• Phase of compilation where invariant applies
• At any point during compilation, framework can
  check all appropriate invariants on a structure
  (and its members)

12
Example Invariants

• ViewParentRule
• for every QueryBlock q, for every view j,
  q.viewsj.parent q
• ColumnReferencesRule
• for every Column c, c.from.referencedColumns
  contains c
• ColumnInQueryBlockRule
• for every Column c in QueryBlock q,
• if !c.correlated, then c.from appears in
  q.fromList

13
Extensibility of QVF

• Component owners can add calls to validation
• Structure experts can add new invariants
  declaratively
• Owners of non-core structures can add support
• Register structure type declaratively
• Provide iterators to visit their structures
  (typically already exist in the query compiler
  code)
• Write calls to these iterators depending on
  structure hierarchy
• Not declarative, but formulaic

14
Experiences with QVF

• Hardest part is agreeing on invariants!
• How we use it
• Enabled for Query Optimizer regression tests
• Also useful for finding culprit code for
  production bugs
• New rules often result in dozens of failures,
  caused by a handful of code defects
• In total, we have found around a dozen distinct
  defects
• Some appear to be benign
• A few were found to cause wrong results when a
  different plan is chosen
• A few cause crashes/errors depending on
  optimization path

15
Example Bug

• ColumnInQueryBlockRule fails, due to defect in
  query copy
• Structure of a query block are copied, but a
  predicate in terms of original structures is left
  behind
• Copied query refers to a column of a table
  structure that is not in the query
• This type of bug can lead to wrong results
• Defensive programming would find this and
  complain later
• But some code passes may assume every filter
  predicate matches a table, and not carry along
  these orphaned predicates
• This helps to identify ways in which we can be
  more defensive

16
Multiple Plan Generation Execution
Query
Generate QPEs
QPEs
MPGE Report
Compile Query
Compilation Error
Performance
Execution Plans
Execution Error
Execute Plans
Incorrect results
17
Result Comparison

• Result checksum computed during plan execution
• Incorrect results reported if two checksums
  differ
• Checksum computed as
• Plan checksum S checksums of tuples returned by
  query
• Tuple checksum hash(vn, (hash(vn-1, hash(...,
  hash(v1, 0))

18
Conclusion

• Two query validation methods to detect and
  resolve sleeper bugs