11G OCM Replay a captured workload

References

Oracle® Database Real Application Testing User's Guide 11g Release 1 (11.1)

Chapter 2, "Introduction to Database Replay"
Chapter 3, "Capturing a Database Workload"
- Workload Capture Options
  - Defining the Workload Filters
  - Setting Up the Capture Directory
- Capturing a Database Workload Using Enterprise Manager
Chapter 4, "Preprocessing a Database Workload"
- Preprocessing a Database Workload Using Enterprise Manager
Chapter 5, "Replaying a Database Workload"
- Replaying a Database Workload Using Enterprise Manager
Chapter 6, "Analyzing Replayed Workload"
- Generating a Workload Capture Report Using Enterprise Manager
- Generating a Workload Replay Report Using Enterprise Manager

Oracle® Database SQL Language Reference 11g Release 1 (11.1)

Overview

The objective from the 11G OCM Upgrade Exam is:

Replay a captured workload

This involves the Database Replay feature:

Database Replay enables realistic testing of system changes by essentially re-creating the production workload environment on a test system. It does this by capturing a workload on the production system and then replaying it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This makes possible complete assessment of the impact of the change including undesired results, new contention points, and performance regressions. Extensive analysis and reporting is provided to help identify any potential problems, such as new errors encountered and performance divergences.

This objective is also related to the following objective:

Convert the standby to a snapshot standby

Reading Notes

Workload Capture

Workload Capture is defined as:

When workload capture is enabled, all external client requests directed to Oracle Database are tracked and stored in binary files—called capture files—on the file system. You can specify the location where the capture files will be stored.

Workload Preprocessing

Workload Preprocessing is defined as:

Preprocessing transforms the captured data into replay files and creates all necessary metadata needed for replaying the workload. This must be done once for every captured workload before they can be replayed.

Workload Replay

Workload Replay is defined as:

During the workload replay phase, Oracle Database performs the actions recorded during the workload capture phase on the test system by re-creating all captured external client requests with the same timing, concurrency, and transaction dependencies of the production system.

Analysis and Reporting

Analysis and Reporting is defined as:

The report summary provides basic information about the workload capture and replay, such as errors encountered during replay and data divergence in rows returned by DML or SQL queries. A comparison of several statistics—such as database time, average active sessions, and user calls—between the workload capture and the workload replay is also provided

Defining the Workload Filters

Defining the Workload Filters:

By default, all user sessions are recorded during workload capture. You can use workload filters to specify which user sessions to include in or exclude from the workload. There are two types of workload filters: inclusion filters and exclusion filters. You can use either inclusion filters or exclusion filters in a workload capture, but not both.

Setting Up the Capture Directory

Setting Up the Capture Directory:

Determine the location and set up a directory where the captured workload will be stored. Before starting the workload capture, ensure that the directory is empty and has ample disk space to store the workload.

Procedure

I am going to set up a read-only workload to make my life easier as there is no need for backups and restores. I will use the sample schema, SH.

Test Query

The test query is as follows:

SELECT products.prod_category, products.prod_subcategory, times.calendar_year, SUM( sales.quantity_sold ) AS total_quantity_sold, SUM( sales.amount_sold ) AS total_amount_sold FROM sales INNER JOIN products USING( prod_id ) INNER JOIN customers USING( cust_id ) INNER JOIN countries USING( country_id ) INNER JOIN channels USING( channel_id ) INNER JOIN times USING( time_id ) WHERE products.prod_valid = 'A' AND channels.channel_class = 'Direct' AND customers.cust_valid = 'A' AND countries.country_name = 'Australia' GROUP BY products.prod_category, products.prod_subcategory, times.calendar_year ORDER BY products.prod_category, products.prod_subcategory, times.calendar_year ;

This query is about a yearly summary of product categories and sub-categories sold directly to active customers in Australia.

This query is saved in a file called sh_sales.sql.

Simulating Think Time

I am going to simulate think time by using the DBMS_LOCK.SLEEP procedure. The user, SH, needs EXECUTE privilege on this procedure. This is achieved through the GRANT command on the package, DBMS_LOCK, as follows:

GRANT EXECUTE ON dbms_lock TO sh;

Test Wrapper Script

The test wrapper script executes the query three (3) times with a think time of five (5) seconds between each execution:

@@sh_sales exec dbms_lock.sleep( 5 ) @@sh_sales exec dbms_lock.sleep( 5 ) @@sh_sales

Workload Capture

Followed the procedure in Capturing a Database Workload Using Enterprise Manager.

Running with filter mode of inclusion: