Integrating EBusiness Certification into the Oracle DBMS Testing

1
Integrating E-Business Certification into the
Oracle DBMS Testing

• Mohamed Zait, Allison Lee, Khaled Yagoub, Ravi
  Sahani, Holly Casaletto, Lokesh Kumar

2
Agenda

• Background
• Motivation
• Goals
• Framework
• Conclusion

3
Background

• Every DBMS release must be certified on Oracle
  E-Business Suite (EBS)
• EBS Suite of business applications, e.g. HR
• Certification was the last phase of DBMS release
  cycle features complete, build are stable, etc.
• Process
• Upgrade EBS DB
• Run the workload
• Exit criteria no regression from prior DBMS
  release

4
Release Cycle
Feature complete
Certification starts
Start
End
Build1
Build2



Defect introduced
Defect resolved
Feature
Build
5
Motivation

• Certification is
• Important
• Valuable
• But has limitations
• Late Hard to identify a defect root cause
• Time consuming Large number of statements
• Inefficient Repeat test

6
Goal

• Push certification upstream
• Release milestones (feature, build, alpha, ...)
• Any time
• Extensible Test Framework
• Detailed metrics to track effect of code changes
• Automate bug creation

7
Enhanced Release Cycle
Feature complete
Certification starts
Start
End
Build1
Build2


Defect introduced
Defect resolved
Feature
Build
8
Test Framework

• Architecture
• Process Flow
• Test Repository
• Metrics
• Reports

9
Architecture
Process SQL Workload
Upgraded DB
1
2
3
Workload
Generate Test Cases
Test Repository
6
C stats
4
5
E Stats
Report Defects
10
Process Flow
Compile all statements in the SQL workload
Compare plans against the previous build
Store the Compilation Stats and Result Hash Value
in Test Repository
Execute the statements with different plans
Generate Test Case for regressions
Store the Execution Stats in Test Repository
11
Test Repository

• Workloads stored as SQL Set
• Test Results
• Compilation statistics
• Execution statistics
• Source data for
• Reports
• Defects

12
Test Repository Schema
13
Metrics

• Compilation
• Elapse time
• Memory allocated in various heaps
• Optimizer search space (e.g, transformations
  tried)
• Execution
• CPU time
• Elapsed time,
• IOs (logical, physical)
• Result checksum

14
Tracking Code Defects

• Performance
• Compilation
• Execution
• Failures
• Logical (rule violation)
• Physical (memory violation)
• Incorrect results
• Defects are filed automatically when
• Performance from using a new plan is worse
• Compilation or execution of a statement fails
• Statement returns different results
• Self-contained test case provided

15
Reports

• Developer
• Validate a new feature
• Resolve defects before committing the feature
• Release Manager
• Test every build
• Defects filed automatically
• EBS representative
• DBMS health check at a point in time
• Compare to previous release
• File defects to track undesired behavior changes
• Trend over a given time period (e.g, week)

16
Reports
17
Reports
3/1/09
3/3/09
3/5/09
3/7/09
3/9/09
4/2/09
4/4/09
4/6/09
4/8/09
SQLs gt
2/13/09
2/15/09
2/17/09
2/19/09
2/21/09
2/23/09
2/25/09
2/27/09
3/11/09
3/13/09
3/15/09
3/17/09
3/19/09
3/21/09
3/23/09
3/25/09
3/27/09
3/29/09
3/31/09
Build gt
Improved Execution Plans
Unchanged Execution Plans
Regressed Execution Plans
Passed Execution
SQLs with Compilation / Execution Failures
1000000
100000
10000
1000
100
SQLs Logarithmic scale gt
10
1
4/3/09
4/6/09
4/9/09
5/3/09
5/6/09
5/9/09
6/2/09
6/5/09
6/8/09
3/31/09
4/12/09
4/15/09
4/18/09
4/21/09
4/24/09
4/27/09
4/30/09
5/12/09
5/15/09
5/18/09
5/21/09
5/24/09
5/27/09
5/30/09
6/11/09
6/14/09
Build gt
Total Compiled
Failed Compilation
Failed Execution
18
Conclusion and Future Work

• Conclusion
• Improved the EBS certification process
• Data warehouse for DBMS developers and RM
• Future
• Support certification of other business
  applications
• Customer workloads