Go to main content
1/309
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 1 (12.1)
New Features
Other Changes
1
Introduction to Oracle Database Testing
1.1
SQL Performance Analyzer
1.2
Database Replay
1.3
Test Data Management
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
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
Preserving COMMIT Order
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
Part III Test Data Management
17
Application Data Models
17.1
Creating an Application Data Model
17.2
Managing Sensitive Column Types
17.3
Associating a Database to an Application Data Model
17.4
Importing and Exporting an Application Data Model
17.4.1
Importing an ADM
17.4.2
Exporting an ADM
17.5
Verifying or Upgrading a Source Database
17.6
Using Self Update to Download the Latest Data Masking and Test Data Management Templates
17.7
Test Data Management and Access Rights
17.8
Granting Privileges on an Application Data Model
18
Data Subsetting
18.1
Creating a Data Subset Definition
18.1.1
Generating a Subset Script
18.1.2
Saving a Subset Script
18.2
Importing and Exporting Subset Templates and Dumps
18.2.1
Importing a Subset Definition
18.2.2
Exporting a Subset Definition
18.3
Creating a Subset Version of a Target Database
18.4
Synchronizing a Subset Definition with an Application Data Model
18.5
Granting Privileges on a Subset Definition
18.6
About Inline Masking and Subsetting
18.7
Inline Masking and Subsetting Scenarios
19
Masking Sensitive Data
19.1
Overview of Oracle Data Masking
19.1.1
Data Masking Concepts
19.1.2
Security and Regulatory Compliance
19.1.3
Roles of Data Masking Users
19.1.4
Related Oracle Security Offerings
19.1.5
Agent Compatibility for Data Masking
19.1.6
Supported Data Types
19.2
Format Libraries and Masking Definitions
19.3
Recommended Data Masking Workflow
19.4
Data Masking Task Sequence
19.5
Defining Masking Formats
19.5.1
Creating New Masking Formats
19.5.1.1
Providing User-defined and Post-processing Functions
19.5.1.2
Using Masking Format Templates
19.5.2
Using Oracle-supplied Predefined Masking Formats
19.5.2.1
Patterns of Format Definitions
19.5.2.2
Category Definitions
19.5.2.2.1
Credit Card Numbers
19.5.2.2.2
United States Social Security Numbers
19.5.2.2.3
ISBN Numbers
19.5.2.2.4
UPC Numbers
19.5.2.2.5
Canadian Social Insurance Numbers
19.5.2.2.6
North American Phone Numbers
19.5.2.2.7
UK National Insurance Numbers
19.5.2.2.8
Auto Mask
19.5.2.3
Installing the DM_FMTLIB Package
19.5.3
Providing a Masking Format to Define a Column
19.5.4
Deterministic Masking Using the Substitute Format
19.6
Masking with an Application Data Model and Workloads
19.6.1
Adding Columns for Masking
19.6.2
Selecting Data Masking Advanced Options
19.6.2.1
Data Masking Options
19.6.2.2
Random Number Generation
19.6.2.3
Pre- and Post-mask Scripts
19.6.3
Scheduling a Script Generation Job
19.6.4
Scheduling a Data Masking Job
19.6.5
Estimating Space Requirements for Masking Operations
19.6.6
Adding Dependent Columns
19.6.7
Masking Dependent Columns for Packaged Applications
19.6.8
Cloning the Production Database
19.6.9
Importing a Data Masking Template
19.7
Masking a Test System to Evaluate Performance
19.7.1
Using Only Masking for Evaluation
19.7.2
Using Cloning and Masking for Evaluation
19.8
Upgrade Considerations
19.9
Using the Shuffle Format
19.10
Using Group Shuffle
19.11
Using Conditional Masking
19.12
Using Data Masking with LONG Columns
Index
Scripting on this page enhances content navigation, but does not change the content in any way.