How To Test For A Test On A Test Server

Real Application Testing

Dave Foster

The following is intended to outline our general

product direction. It is intended for information

purposes only, and may not be incorporated into any

contract. It is not a commitment to deliver any

material, code, or functionality, and should not be

relied upon in making purchasing decisions.

relied upon in making purchasing decisions.

The development, release, and timing of any

features or functionality described for Oracle’s

products remains at the sole discretion of Oracle.

Why Consider Oracle 11g for Upgrades?

• Oracle Database 9i Release 2 Premium Support ended Jul 30, 2007

• 1 yr free Extended Support offer ends Jul 30, 2008

Today R2 July 2010 July 2013 August 2012 August 2015 Jul 2011 t 2 0 0 8 2 0 0 3 2 0 0 4 2 0 0 5 2 0 0 6 2 0 0 7 2 0 0 2 2 0 0 9 2 0 1 0 2 0 1 1 2 0 1 2 2 0 1 3 2 0 1 4 Sustaining Support Premier Support R2 July 2007 Extended Support July 2010 January 2009 January 2012 R2 http://www.oracle.com/support/library/data-sheet/oracle-lifetime-support-policy-datasheet.pdf 2 0 1 5 2 0 1 6 Jul 2008 Jul 2011

Load Testing and Load Testing and Accelerators for Accelerators for Packaged Apps & Packaged Apps &

Web Services Web Services

Oracle Enterprise Manager

Oracle Enterprise Manager

Application Application Operations Operations Management Management

Top

Top--Down Application Management

Down Application Management

Application Application Quality Quality Management Management Application Real User Real User Experience Monitoring Experience Monitoring Service Level Service Level Management Management

Functional Testing and Functional Testing and

Accelerators for Accelerators for Packaged Apps & Packaged Apps &

Web Services Web Services Test Management Test Management

Real Application Testing Real Application Testing

Data Masking Data Masking Application Testing Suite Packaged Application Packaged Application Management Management Oracle E

Oracle E--Business SuiteBusiness Suite Change Management Change Management Configuration Mgmt for Configuration Mgmt for Applications Applications

SOA Application Management SOA Application Management

Diagnostic and Tuning Diagnostic and Tuning Provisioning, Configuration Provisioning, Configuration Management Management Change Tracking Change Tracking and Synchronization and Synchronization

Real Application Testing: Tools of the Trade

SQL Performance Analyzer Database Replay

What is it? • PredictsSQL performance deviations

beforeend-users can be impacted

• Replays real database workload on test system

What Purpose? • Assess impact of change on SQL

response time

• Assess impact of change on

workload throughput

How it works? • Executes each SQL, stored in SQL Tuning Set, in isolationusing

• Captures workloads and replays it with production characteristics Tuning Set, in isolationusing

production contextand then

compares before and after execution plans and run-time statistics

with production characteristics including concurrency,

synchronization & dependencies

When to use? • Unit testing of SQL with the goal to identify the set of SQL statements with improved/regressed performance

• Comprehensive testing of all sub-systems of the database server using real production workload

Real Application Testing

Load and SQL impact testing solution providing

highest quality testing for the database tier

Test Test Test Test Deploy Deploy

•

Value

•

Rapid technology adoption

•

Higher testing quality

•

Business Benefit

•

Lower cost

Change

Remediate Remediate

•

Lower cost

•

Lower risk

•

Features

•

Database Replay

•

SQL Performance Analyzer (SPA)

Business Agility through Superior

Testing

Testing Today

Production – 1,000s of Real Online Users

Application

PRODUCTION

Servers

Database Servers

Testing Today

Test – 1-2 testers trying to be 1,000s of users

Application

TEST

PRODUCTION

Database Replay

Real workload for 1,000s of online users captured

Application Capture Workload

PRODUCTION

Database Replay

Real workload for 1,000s of online users replayed

Application Capture Replay

PRODUCTION

TEST

Database Replay

Test your system changes at production levels

Application Application Capture 9.2.0.8, 10.2.0.2+, 11g

PRODUCTION

TEST

Real Application Testing with

Database Replay

•

Replay production workload in test environment

•

Identify, analyze and fix potential instabilities before making

changes to production

•

Capture Workload in Production

• Capture full production workload with real load, timing & concurrency characteristics

• Move the captured workload to test system

• Move the captured workload to test system

•

Replay Workload in Test

• Make the desired changes in test system

• Replay workload with full production characteristics

• Honor commit ordering

•

Analyze & Report

• Errors

• Data divergence

• Performance divergence

To:

From:

Why DB Replay?

Complete workflows

Partial workflows

Production workloads

Artificial workloads

Complete workflows

Partial workflows

Low risk

High risk

Automated

Manual intensive

Days of development

Months of development

150

Days

10

Days

Replay Options

•

Synchronized Replay (Default)

• Workload is replayed in full synchronized mode

• Same concurrency and timing as production workload

• Transaction commit order is honored

• Ensures minimal data divergence

•

Synchronization controls

• Workload can be replayed in unsynchronized mode

• Workload can be replayed in unsynchronized mode

• Useful for load/stress testing

• High data divergence

• Parameters for controlling synchronization

• _{Commit order synchronization: SYNCHRONIZATION} • _{Think time synchronization: THINK_TIME_SCALE}

• _{Connect (logon) time synchronization: CONNECT_TIME_SCALE} • _{Request rate preservation: THINK_TIME_AUTO_CORRECT}

Analysis & Reporting

•

Error Divergence

: For each call error divergence is reported

• New: Error encountered during replay not seen during capture

• Not Found: Error encountered during capture not seen during replay

• Mutated: Different error produced in replay than during capture

•

Data Divergence

• Replay: Number of rows returned by each call are compared and

• Replay: Number of rows returned by each call are compared and

divergences reported

• User: Application level validation scripts

•

Performance Reporting

• Capture and Replay Report: Provides high-level performance information

• ADDM Report: Provides in-depth performance analysis

…

…

Database Replay Workflow

Production

(9.2.0.8, 10gR2, 11g)

Test

Mid-Tier

Replay Driver Clients

Capture Replay Analysis &

Reporting Process

Step 1: Workload Capture

Production System

…

• All external client requests captured in binary files

• System background and internal activity excluded

• Minimal overhead

• Avoids function call when possible • Buffered I/O File 1 File 2 File n

…

• Independent of client protocol

• Can capture on 9.2.0.8, 10gR2, 11g and replay on 11g

• Capture load for interesting time period, e.g., peak workload, month-end processing, etc.

Step 2: Process Workload Files

File 1

File 2

…

Test System

• Application data should be same as production system as of

capture start time

• Use RMAN, Snapshot Standby, imp/exp, Data Pump, etc. to create test system

• Make change: upgrade db and/or OS, change storage, migrate platforms, etc. File 1 File 2 File n

…

OS, change storage, migrate platforms, etc.

• Processing transforms captured data into replayable format

• Once processed, workload can be replayed many times

• For RAC copy all capture files to single location for processing or use shared file system

File n

…

Step 3: Replay Workload

• Replay Client is a special program that consumes processed workload and sends requests to the replay system

Test System

Replay Clients

File 1 replay system

• Clients interpret captured calls into sequence of OCI calls and submit to

database

• For high concurrency workloads, it may be

necessary to start multiple clients File 1 File 2 File n

…

Supported Changes

with Real Application Testing

Changes Unsupported Changes Supported Application Servers Changes Supported

•Database Upgrades, Patches

•Schema, Parameters

•RAC nodes, Interconnect

•OS Platforms, OS Upgrades •CPU, Memory •Storage •Etc. Recording of External Client Requests Database Servers

Client Client

…

Client

• Test impact of change on SQL query performance

• Capture SQL workload in production including statistics & bind variables

• Re-execute SQL queries in test environment

• Tune regressed SQL and seed SQL plans for production

Production Test

Real Application Testing with

SQL Performance Analyzer (SPA)

… …

…

Why SQL Performance Analyzer (SPA)?

•

Businesses want systems that are performant and

meet SLA’s

•

SQL performance regressions are #1 cause of poor

system performance

•

Solution for proactively detecting

all

SQL regressions

resulting from changes not available

resulting from changes not available

•

DBA’s use ineffective and time-consuming manual

scripts to identify problems

SPA identifies all changes in SQL performance before

impacting users

Transport SQL

SQL Performance Analyzer: Overview

… …

…

Middle Tier Re-execute SQL

Production Test

SQL SQL

Storage Oracle DB

* No middle & application tier setup required Make Changes /

Tuning Regressions

• If adequate spare cycles available, optionally execute SQL here

SQL Performance Analyzer

Restrictions

•

SQL Performance Analyzer does not support the

following features in the current release

•

Shared server (Oracle MTS)

•

9i Parallel Query (SQL Trace Capture)

SQL Performance Analyzer

for Earlier Releases

• Help customers smoothly transition to Oracle Database 10g and 11g

• SQL Test Execution feature of SPA backported to 10gR2

• For more details:

• Metalink Note: 560977.1 – “Real Application Testing for Earlier Releases”

• Metalink Note: 562899.1 – “Testing Performance Impact of an Oracle 9i to

Oracle Database 10g Release 2 Upgrade with SQL Performance Analyzer”

Upgrade From Upgrade To Release / Patches needed Comments 10gR2 10gR2 or 11g • 11.1.0.6 + one-off patch or 11.1.0.7 • 10gR2 + one-off patch

• Uses STS to capture SQL stats

9i 10gR1 10gR2 or 11g • 11.1.0.6 + one-off patch or 11.1.0.7 • 10gR2 + one-off patch

Use Real Application Testing to Manage

Change with Confidence and …

• LOWER

change risk

• LOWER

testing time without compromising quality

• LOWER

unplanned outages

• IMPROVE

application reliability

• IMPROVE

application reliability

• IMPROVE

system performance

• IMPROVE

end-user experience

• IMPROVE

quality of service