Go to main content
1/24
Contents
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle Database Testing Guide
Changes in Oracle Database 12
c
Release 2 (12.2.0.1)
New Features in Oracle Database 12
c
Release 2 (12.2.0.1)
Other Changes in Oracle Database 12
c
Release 2 (12.2.0.1)
Changes in Oracle Database 12
c
Release 1 (12.1)
New Features
Other Changes
1
Introduction to Oracle Database Testing
1.1
SQL Performance Analyzer
1.2
Database Replay
Part I SQL Performance Analyzer
2
Introduction to SQL Performance Analyzer
2.1
Capturing the SQL Workload
2.2
Setting Up the Test System
2.3
Creating a SQL Performance Analyzer Task
2.4
Measuring the Pre-Change SQL Performance
2.5
Making a System Change
2.6
Measuring the Post-Change SQL Performance
2.7
Comparing Performance Measurements
2.8
Fixing Regressed SQL Statements
3
Creating an Analysis Task
3.1
Creating an Analysis Task Using Enterprise Manager
3.1.1
Using the Parameter Change Workflow
3.1.2
Using the Optimizer Statistics Workflow
3.1.3
Using the Exadata Simulation Workflow
3.1.4
Using the Guided Workflow
3.2
Creating an Analysis Task Using APIs
3.3
Configuring an Analysis Task Using APIs
3.3.1
Configuring the Execution Plan Comparison Method of an Analysis Task Using APIs
3.3.2
Configuring an Analysis Task for Exadata Simulation Using APIs
3.3.3
Remapping Multitenant Container Database Identifiers in an Analysis Task Using APIs
3.3.4
Configuring Trigger Execution in an Analysis Task
3.3.5
Configuring a Date to be Returned by Calls in an Analysis Task
3.3.6
Configuring the Number of Rows to Fetch for an Analysis Task
4
Creating a Pre-Change SQL Trial
4.1
Creating a Pre-Change SQL Trial Using Enterprise Manager
4.2
Creating a Pre-Change SQL Trial Using APIs
5
Creating a Post-Change SQL Trial
5.1
Creating a Post-Change SQL Trial Using Oracle Enterprise Manager
5.2
Creating a Post-Change SQL Trial Using APIs
6
Comparing SQL Trials
6.1
Comparing SQL Trials Using Oracle Enterprise Manager
6.1.1
Analyzing SQL Performance Using Oracle Enterprise Manager
6.1.2
Reviewing the SQL Performance Analyzer Report Using Oracle Enterprise Manager
6.1.2.1
Reviewing the SQL Performance Analyzer Report: General Information
6.1.2.2
Reviewing the SQL Performance Analyzer Report: Global Statistics
6.1.2.3
Reviewing the SQL Performance Analyzer Report: Global Statistics Details
6.1.2.4
About SQL Performance Analyzer Active Reports
6.1.3
Tuning Regressed SQL Statements Using Oracle Enterprise Manager
6.1.3.1
Creating SQL Plan Baselines
6.1.3.2
Running SQL Tuning Advisor
6.2
Comparing SQL Trials Using APIs
6.2.1
Analyzing SQL Performance Using APIs
6.2.2
Reviewing the SQL Performance Analyzer Report in Command-Line
6.2.2.1
General Information
6.2.2.2
Result Summary
6.2.2.2.1
Overall Performance Statistics
6.2.2.2.2
Performance Statistics of SQL Statements
6.2.2.2.3
Errors
6.2.2.3
Result Details
6.2.2.3.1
SQL Details
6.2.2.3.2
Execution Statistics
6.2.2.3.3
Execution Plans
6.2.3
Comparing SQL Tuning Sets Using APIs
6.2.4
Tuning Regressed SQL Statements Using APIs
6.2.5
Tuning Regressed SQL Statements From a Remote SQL Trial Using APIs
6.2.6
Creating SQL Plan Baselines Using APIs
6.2.7
Using SQL Performance Analyzer Views
7
Using SPA Quick Check
7.1
About Configuring SPA Quick Check
7.2
Specifying Default Values for SPA Quick Check
7.3
Validating the Impact of an Initialization Parameter Change
7.4
Validating the Impact of Pending Optimizer Statistics
7.5
Validating the Impact of Implementing Key SQL Profiles
7.6
Validating Statistics Findings from Automatic SQL Tuning Advisor
8
Testing a Database Upgrade
8.1
Upgrading from Oracle9
i
Database and Oracle Database 10
g
Release 1
8.1.1
Enabling SQL Trace on the Production System
8.1.2
Creating a Mapping Table
8.1.3
Building a SQL Tuning Set
8.1.4
Testing Database Upgrades from Oracle9
i
Database and Oracle Database 10
g
Release 1
8.1.4.1
Testing Database Upgrades from Releases 9.x and 10.1 Using Cloud Control
8.1.4.2
Testing Database Upgrades from Releases 9.x and 10.1 Using APIs
8.2
Upgrading from Oracle Database 10
g
Release 2 and Newer Releases
8.2.1
Testing Database Upgrades from Oracle Database 10
g
Release 2 and Newer Releases
8.2.1.1
Testing Database Upgrades from Releases 10.2 and Higher Using Cloud Control
8.2.1.2
Testing Database Upgrades from Releases 10.2 and Higher Using APIs
8.3
Tuning Regressed SQL Statements After Testing a Database Upgrade
Part II Database Replay
9
Introduction to Database Replay
9.1
Workload Capture
9.2
Workload Preprocessing
9.3
Workload Replay
9.4
Analysis and Reporting
10
Capturing a Database Workload
10.1
Prerequisites for Capturing a Database Workload
10.2
Setting Up the Capture Directory
10.3
Workload Capture Options
10.3.1
Restarting the Database
10.3.2
Using Filters with Workload Capture
10.4
Workload Capture Restrictions
10.5
Enabling and Disabling the Workload Capture Feature
10.6
Enterprise Manager Privileges and Roles
10.6.1
Database Replay Viewer Role
10.6.2
Database Replay Operator Role
10.7
Capturing a Database Workload Using Enterprise Manager
10.8
Capturing Workloads from Multiple Databases Concurrently
10.9
Monitoring a Workload Capture Using Enterprise Manager
10.9.1
Monitoring an Active Workload Capture
10.9.2
Stopping an Active Workload Capture
10.9.3
Viewing a Completed Workload Capture
10.10
Importing a Workload External to Enterprise Manager
10.11
Creating Subsets from an Existing Workload
10.12
Copying or Moving a Workload to a New Location
10.13
Capturing a Database Workload Using APIs
10.13.1
Defining Workload Capture Filters
10.13.2
Starting a Workload Capture
10.13.3
Stopping a Workload Capture
10.13.4
Exporting AWR Data for Workload Capture
10.13.5
Importing AWR Data for Workload Capture
10.14
Monitoring Workload Capture Using Views
11
Preprocessing a Database Workload
11.1
Preparing a Single Database Workload Using Enterprise Manager
11.1.1
Creating a Database Replay Task
11.1.2
Creating a Replay from a Replay Task
11.1.3
Preparing the Test Database
11.1.4
Preprocessing the Workload and Deploying the Replay Clients
11.2
Preprocessing a Database Workload Using APIs
11.2.1
Running the Workload Analyzer Command-Line Interface
12
Replaying a Database Workload
12.1
Steps for Replaying a Database Workload
12.1.1
Setting Up the Replay Directory
12.1.2
Restoring the Database
12.1.3
Resolving References to External Systems
12.1.4
Connection Remapping
12.1.5
User Remapping
12.1.6
Specifying Replay Options
12.1.6.1
Specifying the Synchronization Method
12.1.6.2
Controlling Session Connection Rate
12.1.6.3
Controlling Request Rate Within a Session
12.1.7
Using Filters with Workload Replay
12.1.8
Setting Up Replay Clients
12.1.8.1
Calibrating Replay Clients
12.1.8.2
Starting Replay Clients
12.1.8.3
Displaying Host Information
12.2
Replaying a Database Workload Using Enterprise Manager
12.3
Setting Up the Replay Schedule and Parameters Using Enterprise Manager
12.4
Monitoring Workload Replay Using Enterprise Manager
12.4.1
Monitoring an Active Workload Replay
12.4.2
Viewing a Completed Workload Replay
12.5
Importing a Replay External to Enterprise Manager
12.6
Replaying a Database Workload Using APIs
12.6.1
Initializing Replay Data
12.6.2
Remapping Connections
12.6.3
Remapping Users
12.6.4
Setting Workload Replay Options
12.6.5
Defining Workload Replay Filters and Replay Filter Sets
12.6.5.1
Adding Workload Replay Filters
12.6.5.2
Deleting Workload Replay Filters
12.6.5.3
Creating a Replay Filter Set
12.6.5.4
Using a Replay Filter Set
12.6.6
Setting the Replay Timeout Action
12.6.7
Starting a Workload Replay
12.6.8
Pausing a Workload Replay
12.6.9
Resuming a Workload Replay
12.6.10
Cancelling a Workload Replay
12.6.11
Retrieving Information About Workload Replays
12.6.12
Loading Divergence Data for Workload Replay
12.6.13
Deleting Information About Workload Replays
12.6.14
Exporting AWR Data for Workload Replay
12.6.15
Importing AWR Data for Workload Replay
12.7
Monitoring Workload Replay Using APIs
12.7.1
Retrieving Information About Diverged Calls
12.7.2
Monitoring Workload Replay Using Views
13
Analyzing Captured and Replayed Workloads
13.1
Using Workload Capture Reports
13.1.1
Accessing Workload Capture Reports Using Enterprise Manager
13.1.2
Generating Workload Capture Reports Using APIs
13.1.3
Reviewing Workload Capture Reports
13.2
Using Workload Replay Reports
13.2.1
Accessing Workload Replay Reports Using Enterprise Manager
13.2.2
Generating Workload Replay Reports Using APIs
13.2.3
Reviewing Workload Replay Reports
13.3
Using Replay Compare Period Reports
13.3.1
Generating Replay Compare Period Reports Using APIs
13.3.2
Reviewing Replay Compare Period Reports
13.3.2.1
General Information
13.3.2.2
Replay Divergence
13.3.2.3
Main Performance Statistics
13.3.2.4
Top SQL/Call
13.3.2.5
Hardware Usage Comparison
13.3.2.6
ADDM Comparison
13.3.2.7
ASH Data Comparison
13.3.2.7.1
Compare Summary
13.3.2.7.2
Top SQL
13.3.2.7.3
Long Running SQL
13.3.2.7.4
Common SQL
13.3.2.7.5
Top Objects
13.4
Using SQL Performance Analyzer Reports
13.4.1
Generating SQL Performance Analyzer Reports Using APIs
14
Using Workload Intelligence
14.1
Overview of Workload Intelligence
14.1.1
About Workload Intelligence
14.1.2
Use Case for Workload Intelligence
14.1.3
Requirements for Using Workload Intelligence
14.2
Analyzing Captured Workloads Using Workload Intelligence
14.2.1
Creating a Database User for Workload Intelligence
14.2.2
Creating a Workload Intelligence Job
14.2.3
Generating a Workload Model
14.2.4
Identifying Patterns in a Workload
14.2.5
Generating a Workload Intelligence Report
14.3
Example: Workload Intelligence Results
15
Using Consolidated Database Replay
15.1
Use Cases for Consolidated Database Replay
15.1.1
Database Consolidation Using Pluggable Databases
15.1.2
Stress Testing
15.1.3
Scale-Up Testing
15.2
Steps for Using Consolidated Database Replay
15.2.1
Capturing Database Workloads for Consolidated Database Replay
15.2.1.1
Supported Types of Workload Captures
15.2.1.2
Capture Subsets
15.2.2
Setting Up the Test System for Consolidated Database Replay
15.2.3
Preprocessing Database Workloads for Consolidated Database Replay
15.2.4
Replaying Database Workloads for Consolidated Database Replay
15.2.4.1
Defining Replay Schedules
15.2.4.1.1
Adding Workload Captures
15.2.4.1.2
Adding Schedule Orders
15.2.4.2
Remapping Connections for Consolidated Database Replay
15.2.4.3
Remapping Users for Consolidated Database Replay
15.2.4.4
Preparing for Consolidated Database Replay
15.2.4.5
Replaying Individual Workloads
15.2.5
Reporting and Analysis for Consolidated Database Replay
15.3
Using Consolidated Database Replay with Enterprise Manager
15.4
Using Consolidated Database Replay with APIs
15.4.1
Generating Capture Subsets Using APIs
15.4.2
Setting the Consolidated Replay Directory Using APIs
15.4.3
Defining Replay Schedules Using APIs
15.4.3.1
Creating Replay Schedules Using APIs
15.4.3.2
Adding Workload Captures to Replay Schedules Using APIs
15.4.3.3
Adding Schedule Orders to Replay Schedules Using APIs
15.4.3.4
Saving Replay Schedules Using APIs
15.4.4
Running Consolidated Database Replay Using APIs
15.4.4.1
Initializing Consolidated Database Replay Using APIs
15.4.4.2
Remapping Connection Using APIs
15.4.4.3
Remapping Users Using APIs
15.4.4.4
Preparing for Consolidated Database Replay Using APIs
15.4.4.5
Starting Consolidated Database Replay Using APIs
15.5
About Query-Only Database Replay
15.5.1
Use Cases for Query-Only Database Replay
15.5.2
Performing a Query-Only Database Replay
15.6
Example: Replaying a Consolidated Workload with APIs
16
Using Workload Scale-Up
16.1
Overview of Workload Scale-Up
16.1.1
About Time Shifting
16.1.2
About Workload Folding
16.1.3
About Schema Remapping
16.2
Using Time Shifting
16.3
Using Workload Folding
16.4
Using Schema Remapping
Index
Scripting on this page enhances content navigation, but does not change the content in any way.