New System Testing Tools

As a DBA, one of the most bedeviling problems that I've regularly faced is to be able to predict accurately how the next set of changes to the database's application code, database patch set, or hardware configuration will affect that database's performance. That usually meant purchasing a relatively expensive third-party package (e.g., Mercury Interactive's  Load Runner) to generate a sample workload against the database using the next version of the application code, and then comparing the results against baseline performance for the current application code version.

Fortunately, Oracle Database 11g has come to the res-cue with two new utilities that offer monumental strides forward in system testing-

Database Replay

System changes such as a database upgrade require substantial testing and validation before you can actually migrate the changes to a production system. The trick is to simulate a real production workload on a test system. The Database Replay feature enables you to perform real-life testing of major changes by letting you capture the actual database workload on the production system and replay it on a test system. Thus, you essentially re-create the production workload effortlessly on a test system. Database Replay performs a sophisticated replay of the production workload by adhering to the original concurrency and timing characteristics. Once you complete the testing, you can analyze and review the reports produced by Database Replay to see if there was a performance divergence between the two runs and also if there were any errors. Finally, you can choose to implement the recommendations made by Database Replay to fix any problems it encountered during the replay of the production workload.

You can employ Database Replay to test significant system changes such as the

following:

#Operating system and database upgrades and migrations.

# Configuration changes such as moving to an oracle RAC environment.

# Storage changes.

Database Replay doesn’t capture the following types of client requests:

# SQL*Loader direct path load of data

# Oracle Streams

# Data Pump Import and Export

# Advanced replication streams

# Non–PL/SQL-based Advanced Queuing (AQ)

# Flashback Database and Flashback queries

# Distributed transactions and remote describe/commit operations

# Shared server

# Non–SQL-based object access

The following data dictionary views help you manage the Database Replay feature:

# DBA_WORKLOAD_CAPTURES - It shows all workload captures you performed in a database.

# DBA_WORKLOAD_FILTERS – It shows all workload filters you defined in a database.

# DBA_WORKLOAD_REPLAYS – It shows all workload replays you performed in a database.

# DBA_WORKLOAD_REPLAY_DIVERGENCE – It helps monitor workload divergence.

# DBA_WORKLOAD_THREAD – It helps monitor the status of external replay clients.

# DBA_WORKLOAD_CONNECTION_MAP – It shows all connection strings used by workload replays.

SQL Performance Analyzer

SQL Performance Analyzer, which, along with the Database Replay constitutes the Total Replay

feature, lets you test the impact of potential changes such as a server or database upgrade on SQL workload response time. The SQL Performance Analyzer focuses on comparing the performance of a specific SQL workload before and after a major system change. The analyzer does this by building two versions of the SQL workload performance, which includes both the SQL execution plans as well as their execution statistics. After analyzing SQL performance both before and after you make a major

change, the SQL Performance Analyzer provides suggestions to prevent potential performance degradation of SQL statements. This is especially handy when you’re planning an upgrade of your database to a newer release of the Oracle database. The SQL Performance Analyzer, by enabling you to compare SQL performance on two systems running on different versions of the Oracle database, lets you know ahead of the upgrade which of the SQL statements may show a deterioration in performance. Thus, you can reengineer those statements prior to the actual upgrade.

You can use the SQL Performance Analyzer to predict performance changes resulting from the following system changes:

# Database and application upgrades

# Hardware upgrades

# Operating system upgrades

# Initialization parameter changes

# SQL tuning actions such as the creation of SQL profiles

# Statistics gathering

# Schema changes