1/308

Contents

Expand All · Collapse All

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database Testing Guide

• Changes in Oracle Database 12c Release 1 (12.1)
  • New Features
  • Other Changes

1 Introduction to Oracle Database Testing

• SQL Performance Analyzer
• Database Replay
• Test Data Management

Part I SQL Performance Analyzer

2 Introduction to SQL Performance Analyzer

• Capturing the SQL Workload
• Setting Up the Test System
• Creating a SQL Performance Analyzer Task
• Measuring the Pre-Change SQL Performance
• Making a System Change
• Measuring the Post-Change SQL Performance
• Comparing Performance Measurements
• Fixing Regressed SQL Statements

3 Creating an Analysis Task

• Creating an Analysis Task Using Enterprise Manager
  • Using the Parameter Change Workflow
  • Using the Optimizer Statistics Workflow
  • Using the Exadata Simulation Workflow
  • Using the Guided Workflow
• Creating an Analysis Task Using APIs
• Configuring an Analysis Task Using APIs
  • Configuring the Execution Plan Comparison Method of an Analysis Task Using APIs
  • Configuring an Analysis Task for Exadata Simulation Using APIs
  • Remapping Multitenant Container Database Identifiers in an Analysis Task Using APIs

4 Creating a Pre-Change SQL Trial

• Creating a Pre-Change SQL Trial Using Enterprise Manager
• Creating a Pre-Change SQL Trial Using APIs

5 Creating a Post-Change SQL Trial

• Creating a Post-Change SQL Trial Using Oracle Enterprise Manager
• Creating a Post-Change SQL Trial Using APIs

6 Comparing SQL Trials

• Comparing SQL Trials Using Oracle Enterprise Manager
  • Analyzing SQL Performance Using Oracle Enterprise Manager
  • Reviewing the SQL Performance Analyzer Report Using Oracle Enterprise Manager
  • Tuning Regressed SQL Statements Using Oracle Enterprise Manager
• Comparing SQL Trials Using APIs
  • Analyzing SQL Performance Using APIs
  • Reviewing the SQL Performance Analyzer Report in Command-Line
  • Comparing SQL Tuning Sets Using APIs
  • Tuning Regressed SQL Statements Using APIs
  • Tuning Regressed SQL Statements From a Remote SQL Trial Using APIs
  • Creating SQL Plan Baselines Using APIs
  • Using SQL Performance Analyzer Views

7 Using SPA Quick Check

• About Configuring SPA Quick Check
• Specifying Default Values for SPA Quick Check
• Validating the Impact of an Initialization Parameter Change
• Validating the Impact of Pending Optimizer Statistics
• Validating the Impact of Implementing Key SQL Profiles
• Validating Statistics Findings from Automatic SQL Tuning Advisor

8 Testing a Database Upgrade

• Upgrading from Oracle9i Database and Oracle Database 10g Release 1
  • Enabling SQL Trace on the Production System
  • Creating a Mapping Table
  • Building a SQL Tuning Set
  • Testing Database Upgrades from Oracle9i Database and Oracle Database 10g Release 1
• Upgrading from Oracle Database 10g Release 2 and Newer Releases
  • Testing Database Upgrades from Oracle Database 10g Release 2 and Newer Releases
• Tuning Regressed SQL Statements After Testing a Database Upgrade

Part II Database Replay

9 Introduction to Database Replay

• Workload Capture
• Workload Preprocessing
• Workload Replay
• Analysis and Reporting

10 Capturing a Database Workload

• Prerequisites for Capturing a Database Workload
• Setting Up the Capture Directory
• Workload Capture Options
  • Restarting the Database
  • Using Filters with Workload Capture
• Workload Capture Restrictions
• Enabling and Disabling the Workload Capture Feature
• Enterprise Manager Privileges and Roles
  • Database Replay Viewer Role
  • Database Replay Operator Role
• Capturing a Database Workload Using Enterprise Manager
• Capturing Workloads from Multiple Databases Concurrently
• Monitoring a Workload Capture Using Enterprise Manager
  • Monitoring an Active Workload Capture
  • Stopping an Active Workload Capture
  • Viewing a Completed Workload Capture
• Importing a Workload External to Enterprise Manager
• Creating Subsets from an Existing Workload
• Copying or Moving a Workload to a New Location
• Capturing a Database Workload Using APIs
  • Defining Workload Capture Filters
  • Starting a Workload Capture
  • Stopping a Workload Capture
  • Exporting AWR Data for Workload Capture
  • Importing AWR Data for Workload Capture
• Monitoring Workload Capture Using Views

11 Preprocessing a Database Workload

• Preparing a Single Database Workload Using Enterprise Manager
  • Creating a Database Replay Task
  • Creating a Replay from a Replay Task
  • Preparing the Test Database
  • Preprocessing the Workload and Deploying the Replay Clients
• Preprocessing a Database Workload Using APIs
  • Running the Workload Analyzer Command-Line Interface

12 Replaying a Database Workload

• Steps for Replaying a Database Workload
  • Setting Up the Replay Directory
  • Restoring the Database
  • Resolving References to External Systems
  • Connection Remapping
  • User Remapping
  • Specifying Replay Options
  • Using Filters with Workload Replay
  • Setting Up Replay Clients
• Replaying a Database Workload Using Enterprise Manager
• Setting Up the Replay Schedule and Parameters Using Enterprise Manager
• Monitoring Workload Replay Using Enterprise Manager
  • Monitoring an Active Workload Replay
  • Viewing a Completed Workload Replay
• Importing a Replay External to Enterprise Manager
• Replaying a Database Workload Using APIs
  • Initializing Replay Data
  • Remapping Connections
  • Remapping Users
  • Setting Workload Replay Options
  • Defining Workload Replay Filters and Replay Filter Sets
  • Setting the Replay Timeout Action
  • Starting a Workload Replay
  • Pausing a Workload Replay
  • Resuming a Workload Replay
  • Cancelling a Workload Replay
  • Retrieving Information About Workload Replays
  • Loading Divergence Data for Workload Replay
  • Deleting Information About Workload Replays
  • Exporting AWR Data for Workload Replay
  • Importing AWR Data for Workload Replay
• Monitoring Workload Replay Using APIs
  • Retrieving Information About Diverged Calls
  • Monitoring Workload Replay Using Views

13 Analyzing Captured and Replayed Workloads

• Using Workload Capture Reports
  • Accessing Workload Capture Reports Using Enterprise Manager
  • Generating Workload Capture Reports Using APIs
  • Reviewing Workload Capture Reports
• Using Workload Replay Reports
  • Accessing Workload Replay Reports Using Enterprise Manager
  • Generating Workload Replay Reports Using APIs
  • Reviewing Workload Replay Reports
• Using Replay Compare Period Reports
  • Generating Replay Compare Period Reports Using APIs
  • Reviewing Replay Compare Period Reports
• Using SQL Performance Analyzer Reports
  • Generating SQL Performance Analyzer Reports Using APIs

14 Using Workload Intelligence

• Overview of Workload Intelligence
  • About Workload Intelligence
  • Use Case for Workload Intelligence
  • Requirements for Using Workload Intelligence
• Analyzing Captured Workloads Using Workload Intelligence
  • Creating a Database User for Workload Intelligence
  • Creating a Workload Intelligence Job
  • Generating a Workload Model
  • Identifying Patterns in a Workload
  • Generating a Workload Intelligence Report
• Example: Workload Intelligence Results

15 Using Consolidated Database Replay

• Use Cases for Consolidated Database Replay
  • Database Consolidation Using Pluggable Databases
  • Stress Testing
  • Scale-Up Testing
• Steps for Using Consolidated Database Replay
  • Capturing Database Workloads for Consolidated Database Replay
  • Setting Up the Test System for Consolidated Database Replay
  • Preprocessing Database Workloads for Consolidated Database Replay
  • Replaying Database Workloads for Consolidated Database Replay
  • Reporting and Analysis for Consolidated Database Replay
• Using Consolidated Database Replay with Enterprise Manager
• Using Consolidated Database Replay with APIs
  • Generating Capture Subsets Using APIs
  • Setting the Consolidated Replay Directory Using APIs
  • Defining Replay Schedules Using APIs
  • Running Consolidated Database Replay Using APIs
• About Query-Only Database Replay
  • Use Cases for Query-Only Database Replay
  • Performing a Query-Only Database Replay
• Example: Replaying a Consolidated Workload with APIs

16 Using Workload Scale-Up

• Overview of Workload Scale-Up
  • About Time Shifting
  • About Workload Folding
  • About Schema Remapping
• Using Time Shifting
• Using Workload Folding
• Using Schema Remapping

Part III Test Data Management

17 Application Data Models

• Creating an Application Data Model
• Managing Sensitive Column Types
• Associating a Database to an Application Data Model
• Importing and Exporting an Application Data Model
  • Importing an ADM
  • Exporting an ADM
• Verifying or Upgrading a Source Database
• Using Self Update to Download the Latest Data Masking and Test Data Management Templates
• Test Data Management and Access Rights
• Granting Privileges on an Application Data Model

18 Data Subsetting

• Creating a Data Subset Definition
  • Generating a Subset Script
  • Saving a Subset Script
• Importing and Exporting Subset Templates and Dumps
  • Importing a Subset Definition
  • Exporting a Subset Definition
• Creating a Subset Version of a Target Database
• Synchronizing a Subset Definition with an Application Data Model
• Granting Privileges on a Subset Definition
• About Inline Masking and Subsetting
• Inline Masking and Subsetting Scenarios

19 Masking Sensitive Data

• Overview of Oracle Data Masking
  • Data Masking Concepts
  • Security and Regulatory Compliance
  • Roles of Data Masking Users
  • Related Oracle Security Offerings
  • Agent Compatibility for Data Masking
  • Supported Data Types
• Format Libraries and Masking Definitions
• Recommended Data Masking Workflow
• Data Masking Task Sequence
• Defining Masking Formats
  • Creating New Masking Formats
  • Using Oracle-supplied Predefined Masking Formats
  • Providing a Masking Format to Define a Column
  • Deterministic Masking Using the Substitute Format
• Masking with an Application Data Model and Workloads
  • Adding Columns for Masking
  • Selecting Data Masking Advanced Options
  • Scheduling a Script Generation Job
  • Scheduling a Data Masking Job
  • Estimating Space Requirements for Masking Operations
  • Adding Dependent Columns
  • Masking Dependent Columns for Packaged Applications
  • Cloning the Production Database
  • Importing a Data Masking Template
• Masking a Test System to Evaluate Performance
  • Using Only Masking for Evaluation
  • Using Cloning and Masking for Evaluation
• Upgrade Considerations
• Using the Shuffle Format
• Using Group Shuffle
• Using Conditional Masking
• Using Data Masking with LONG Columns

Index