Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

1 Introduction to Oracle Real Application 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.2.1 Running the Exadata Simulation 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 Using APIs
    • 6.2.2.1 General Information
    • 6.2.2.2 Result Summary
    • 6.2.2.3 Result Details
  • 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 Testing a Database Upgrade

• 7.1 Upgrading from Oracle9i Database and Oracle Database 10g Release 1
  • 7.1.1 Enabling SQL Trace on the Production System
  • 7.1.2 Creating a Mapping Table
  • 7.1.3 Building a SQL Tuning Set
  • 7.1.4 Testing Database Upgrades from Oracle9i Database and Oracle Database 10g Release 1
    • 7.1.4.1 Testing Database Upgrades from Oracle9i Database and Oracle Database 10g Release 1 Using Enterprise Manager
    • 7.1.4.2 Testing Database Upgrades from Oracle9i Database and Oracle Database 10g Release 1 Using APIs
• 7.2 Upgrading from Oracle Database 10g Release 2 and Newer Releases
  • 7.2.1 Testing Database Upgrades from Oracle Database 10g Release 2 and Newer Releases
    • 7.2.1.1 Testing Database Upgrades from Oracle Database 10g Release 2 and Newer Releases Using Enterprise Manager
    • 7.2.1.2 Testing Database Upgrades from Oracle Database 10g Release 2 and Newer Releases Using APIs
• 7.3 Tuning Regressed SQL Statements After Testing a Database Upgrade

Part II Database Replay

8 Introduction to Database Replay

• 8.1 Workload Capture
• 8.2 Workload Preprocessing
• 8.3 Workload Replay
• 8.4 Analysis and Reporting

9 Capturing a Database Workload

• 9.1 Prerequisites for Capturing a Database Workload
• 9.2 Workload Capture Options
  • 9.2.1 Restarting the Database
  • 9.2.2 Using Filters with Workload Capture
  • 9.2.3 Setting Up the Capture Directory
• 9.3 Workload Capture Restrictions
• 9.4 Enabling and Disabling the Workload Capture Feature
• 9.5 Capturing a Database Workload Using Enterprise Manager
• 9.6 Monitoring Workload Capture Using Enterprise Manager
  • 9.6.1 Monitoring an Active Workload Capture
  • 9.6.2 Stopping an Active Workload Capture
  • 9.6.3 Managing a Completed Workload Capture
• 9.7 Capturing a Database Workload Using APIs
  • 9.7.1 Defining Workload Capture Filters
  • 9.7.2 Starting a Workload Capture
  • 9.7.3 Stopping a Workload Capture
  • 9.7.4 Exporting AWR Data for Workload Capture
• 9.8 Monitoring Workload Capture Using Views

10 Preprocessing a Database Workload

• 10.1 Preprocessing a Database Workload Using Enterprise Manager
• 10.2 Preprocessing a Database Workload Using APIs
  • 10.2.1 Running the Workload Analyzer Command-Line Interface

11 Replaying a Database Workload

• 11.1 Steps for Replaying a Database Workload
  • 11.1.1 Setting Up the Replay Directory
  • 11.1.2 Restoring the Database
  • 11.1.3 Resolving References to External Systems
  • 11.1.4 Remapping Connections
  • 11.1.5 User Remapping
  • 11.1.6 Specifying Replay Options
    • 11.1.6.1 Preserving COMMIT Order
    • 11.1.6.2 Controlling Session Connection Rate
    • 11.1.6.3 Controlling Request Rate Within a Session
  • 11.1.7 Using Filters with Workload Replay
  • 11.1.8 Setting Up Replay Clients
    • 11.1.8.1 Calibrating Replay Clients
    • 11.1.8.2 Starting Replay Clients
    • 11.1.8.3 Displaying Host Information
• 11.2 Replaying a Database Workload Using Enterprise Manager
• 11.3 Monitoring Workload Replay Using Enterprise Manager
  • 11.3.1 Monitoring an Active Workload Replay
  • 11.3.2 Viewing a Completed Workload Replay
• 11.4 Replaying a Database Workload Using APIs
  • 11.4.1 Initializing Replay Data
  • 11.4.2 Connection Remapping
  • 11.4.3 Setting Workload Replay Options
  • 11.4.4 Defining Workload Replay Filters and Replay Filter Sets
    • 11.4.4.1 Adding Workload Replay Filters
    • 11.4.4.2 Deleting Workload Replay Filters
    • 11.4.4.3 Creating a Replay Filter Set
    • 11.4.4.4 Using a Replay Filter Set
  • 11.4.5 Setting the Replay Timeout Action
  • 11.4.6 Starting a Workload Replay
  • 11.4.7 Pausing a Workload Replay
  • 11.4.8 Resuming a Workload Replay
  • 11.4.9 Cancelling a Workload Replay
  • 11.4.10 Exporting AWR Data for Workload Replay
• 11.5 Monitoring Workload Replay Using APIs
  • 11.5.1 Retrieving Information About Diverged Calls
  • 11.5.2 Monitoring Workload Replay Using Views

12 Analyzing Captured and Replayed Workloads

• 12.1 Using Workload Capture Reports
  • 12.1.1 Generating Workload Capture Reports Using Enterprise Manager
  • 12.1.2 Generating Workload Capture Reports Using APIs
  • 12.1.3 Reviewing Workload Capture Reports
• 12.2 Using Workload Replay Reports
  • 12.2.1 Generating Workload Replay Reports Using Enterprise Manager
  • 12.2.2 Generating Workload Replay Reports Using APIs
  • 12.2.3 Reviewing Workload Replay Reports
• 12.3 Using Compare Period Reports
  • 12.3.1 Generating Compare Period Reports Using Enterprise Manager
  • 12.3.2 Generating Compare Period Reports Using APIs
    • 12.3.2.1 Generating Replay Compare Period Reports Using APIs
    • 12.3.2.2 Generating SQL Performance Analyzer Reports Using APIs
  • 12.3.3 Reviewing Replay Compare Period Reports
    • 12.3.3.1 General Information
    • 12.3.3.2 Replay Divergence
    • 12.3.3.3 Main Performance Statistics
    • 12.3.3.4 Top SQL/Call
    • 12.3.3.5 Hardware Usage Comparison
    • 12.3.3.6 ADDM Comparison
    • 12.3.3.7 ASH Data Comparison

13 Using Consolidated Database Replay

• 13.1 Overview of Consolidated Database Replay
  • 13.1.1 Use Cases for Consolidated Database Replay
    • 13.1.1.1 Stress Testing
    • 13.1.1.2 Scale-Up Testing
  • 13.1.2 Workload Scale-Up Techniques
    • 13.1.2.1 About Time Shifting
    • 13.1.2.2 About Workload Folding
    • 13.1.2.3 About Schema Remapping
• 13.2 Steps for Using Consolidated Database Replay
  • 13.2.1 Capturing Database Workloads for Consolidated Database Replay
    • 13.2.1.1 Supported Types of Workload Captures
    • 13.2.1.2 Capture Subsets
  • 13.2.2 Setting Up the Test System for Consolidated Database Replay
  • 13.2.3 Preprocessing Database Workloads for Consolidated Database Replay
  • 13.2.4 Replaying Database Workloads for Consolidated Database Replay
    • 13.2.4.1 Defining Replay Schedules
    • 13.2.4.2 Remapping Connections for Consolidated Database Replay
    • 13.2.4.3 Remapping Users for Consolidated Database Replay
    • 13.2.4.4 Preparing for Consolidated Database Replay
    • 13.2.4.5 Replaying Individual Workloads
  • 13.2.5 Reporting and Analysis for Consolidated Database Replay
• 13.3 Using Consolidated Database Replay with APIs
  • 13.3.1 Generating Capture Subsets Using APIs
  • 13.3.2 Setting the Replay Directory Using APIs
  • 13.3.3 Defining Replay Schedules Using APIs
    • 13.3.3.1 Creating Replay Schedules Using APIs
    • 13.3.3.2 Adding Workload Captures to Replay Schedules Using APIs
    • 13.3.3.3 Adding Schedule Orders to Replay Schedules Using APIs
    • 13.3.3.4 Saving Replay Schedules Using APIs
  • 13.3.4 Running Consolidated Database Replay Using APIs
    • 13.3.4.1 Initializing Consolidated Database Replay Using APIs
    • 13.3.4.2 Remapping Connection Using APIs
    • 13.3.4.3 Remapping Users Using APIs
    • 13.3.4.4 Preparing for Consolidated Database Replay Using APIs
    • 13.3.4.5 Starting Consolidated Database Replay Using APIs
• 13.4 Examples: Using Consolidated Database Replay
  • 13.4.1 Using Time Shifting
  • 13.4.2 Using Workload Folding
  • 13.4.3 Using Schema Remapping

Part III Test Data Management

14 Data Discovery and Modeling

• 14.1 Creating an Application Data Model
• 14.2 Managing Sensitive Column Types
• 14.3 Associating a Database to an Application Data Model
• 14.4 Importing and Exporting an Application Data Model
• 14.5 Verifying or Upgrading a Source Database

15 Data Subsetting

• 15.1 Creating a Data Subset Definition
• 15.2 Importing Exported Dumps
• 15.3 Importing and Exporting Subset Templates
• 15.4 Creating a Subset Version of a Target Database

16 Masking Sensitive Data

• 16.1 Overview of Oracle Data Masking
  • 16.1.1 Data Masking Concepts
  • 16.1.2 Security and Regulatory Compliance
  • 16.1.3 Roles of Data Masking Users
  • 16.1.4 Related Oracle Security Offerings
  • 16.1.5 Agent Compatibility for Data Masking
  • 16.1.6 Supported Data Types
• 16.2 Format Libraries and Masking Definitions
• 16.3 Recommended Data Masking Workflow
• 16.4 Data Masking Task Sequence
• 16.5 Defining Masking Formats
  • 16.5.1 Creating New Masking Formats
    • 16.5.1.1 Providing User-defined and Post-processing Functions
    • 16.5.1.2 Using Masking Format Templates
  • 16.5.2 Using Oracle-supplied Predefined Masking Formats
    • 16.5.2.1 Patterns of Format Definitions
    • 16.5.2.2 Category Definitions
    • 16.5.2.3 Installing the DM_FMTLIB Package
  • 16.5.3 Providing a Masking Format to Define a Column
  • 16.5.4 Deterministic Masking Using the Substitute Format
• 16.6 Masking with an Application Data Model and Workloads
  • 16.6.1 Adding Dependent Columns
  • 16.6.2 Masking Dependent Columns for Packaged Applications
  • 16.6.3 Selecting Data Masking Advanced Options
    • 16.6.3.1 Data Masking Options
    • 16.6.3.2 Random Number Generation
    • 16.6.3.3 Pre- and Post-mask Scripts
  • 16.6.4 Cloning the Production Database
  • 16.6.5 Importing a Data Masking Template
• 16.7 Masking a Test System to Evaluate Performance
  • 16.7.1 Using Only Masking for Evaluation
  • 16.7.2 Using Cloning and Masking for Evaluation
• 16.8 Upgrade Considerations
• 16.9 Using the Shuffle Format
• 16.10 Using Data Masking with LONG Columns

Index