11G OCM Replay a captured workload

References

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:

A filter called SH_USER is created for the user, SH.

The directory is created as follows:

CREATE DIRECTORY "SH_REPLAY" AS '/u00/app/oracle/replay/sh';

A screenshot of the review of capture workload is as follows:

The test wrapper script was run three (3) times on three (3) different terminals.

Followed the procedure in Generating a Workload Capture Report Using Enterprise Manager .

The workload capture report is uploaded as workload_report_1329872485962.html .

Preprocess Captured Workload

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

A screenshot of the review of preprocess captured workload is as follows:

Replay Workload

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

The calibration says that only one (1) client needs to be used.

A screenshot of the review of replay workload is as follows:

Analyzing Replayed Workload

Followed the procedure in Generating a Workload Replay Report Using Enterprise Manager .

The Workload Replay report is uploaded as workload_report_1329885258683.html .