12 Analyzing Replayed Workload

12.1.1 Generating Workload Capture Reports Using Enterprise Manager

This section describes how to generate a workload capture report using Oracle Enterprise Manager.

The primary tool for generating workload capture reports is Oracle Enterprise Manager. If for some reason Oracle Enterprise Manager is unavailable, you can generate workload capture reports using APIs, as described in "Generating Workload Capture Reports Using APIs".

To generate a workload capture report using Enterprise Manager:

1. On the Software and Support page, under Real Application Testing, click Database Replay.

   The Database Replay page appears.

2. Click View Workload Capture History.

   The View Workload Capture History page appears.

3. Select the workload capture for which you want to run a workload capture report and click View.

   The View Workload Capture page appears.

4. To view the workload capture report, click View Workload Capture Report.

   The Report window opens while the report is being generated.

5. Once the report is generated, you can save the report by clicking Save to File.

   For information about how to interpret the workload capture report, see "Reviewing Workload Capture Reports".

12.1.2 Generating Workload Capture Reports Using APIs

This section describes how to generate a workload capture report using the DBMS_WORKLOAD_CAPTURE package. You can also use Oracle Enterprise Manager to generate a workload capture report, as described in "Generating Workload Capture Reports Using Enterprise Manager".

To generate a report on the latest workload capture, use the DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO procedure and the DBMS_WORKLOAD_CAPTURE.REPORT function:

DECLARE
  cap_id         NUMBER;
  cap_rpt        CLOB;
BEGIN
  cap_id  := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => 'dec06');
  cap_rpt := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => cap_id,
                           format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT);
END;
/

In this example, the GET_CAPTURE_INFO procedure retrieves all information regarding the workload capture in the dec06 directory and returns the appropriate cap_id for the workload capture. The REPORT function generates a text report using the cap_id that was returned by the GET_CAPTURE_INFO procedure.

The GET_CAPTURE_INFO procedure uses the dir required parameter, which specifies the name of the workload capture directory object.

The REPORT function uses the following parameters:

• The capture_id required parameter relates to the directory that contains the workload capture for which the report will be generated. The directory should be a valid directory in the host system containing the workload capture. The value of this parameter should match the cap_id returned by the GET_CAPTURE_INFO procedure.

• The format required parameter specifies the report format. Valid values include DBMS_WORKLOAD_CAPTURE.TYPE_TEXT and DBMS_WORKLOAD_REPLAY.TYPE_HTML.

For information about how to interpret the workload capture report, see "Reviewing Workload Capture Reports".

12.1.3 Reviewing Workload Capture Reports

The workload capture report contains various types of information that can be used to assess the validity of the workload capture. Using the information provided in this report, you can determine if the captured workload:

• Represents the actual workload you want to replay

• Does not contain any workload you want to exclude

• Can be replayed

The information contained in the workload capture report are divided into the following categories:

• Details about the workload capture (such as the name of the workload capture, defined filters, date, time, and SCN of capture)

• Overall statistics about the workload capture (such as the total DB time captured, and the number of logins and transactions captured) and the corresponding percentages with respect to total system activity

• Profile of the captured workload

• Profile of the workload that was not captured due to version limitations

• Profile of the uncaptured workload that were excluded using defined filters

• Profile of the uncaptured workload that consists of background process or scheduled jobs

12.2.1 Generating Workload Replay Reports Using Enterprise Manager

This section describes how to generate a workload replay report using Oracle Enterprise Manager.

The primary tool for generating workload replay reports is Oracle Enterprise Manager. If for some reason Oracle Enterprise Manager is unavailable, you can generate workload replay reports using APIs, as described in "Generating Workload Replay Reports Using APIs"

To generate a workload replay report using Enterprise Manager:

1. On the Software and Support page, under Real Application Testing, click Database Replay.

   The Database Replay page appears.

2. In the Go to Task column, click the icon that corresponds to the Replay Workload task.

   The Replay Workload page appears.

3. In the Directory Object list, select a directory that contains the preprocessed workload that was used for the replay for which you want to generate a workload replay report.

   After a directory is selected, the Replay Workload page will be refreshed to display the Capture Summary and the Replay History sections.

4. Under Replay History, select the replay for which you want to generate a workload replay report and click View.

   
   Description of the illustration dbr_replay_history.gif
   
   

   The View Workload Replay page appears.

5. Click View Workload Replay Report.

   For information about how to interpret the workload replay report, see "Reviewing Workload Replay Reports".

12.2.2 Generating Workload Replay Reports Using APIs

This section describes how to generate a workload replay report using the DBMS_WORKLOAD_REPLAY package. You can also use Oracle Enterprise Manager to generate a workload replay report, as described in "Generating Workload Replay Reports Using Enterprise Manager".

To generate a report on the latest workload replay for a workload capture, use the DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO procedure and the DBMS_WORKLOAD_REPLAY.REPORT function:

DECLARE
  cap_id         NUMBER;
  rep_id         NUMBER;
  rep_rpt        CLOB;
BEGIN
  cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => 'dec06');
  /* Get the latest replay for that capture */
  SELECT max(id)
  INTO   rep_id
  FROM   dba_workload_replays
  WHERE  capture_id = cap_id;
 
  rep_rpt := DBMS_WORKLOAD_REPLAY.REPORT(replay_id => rep_id,
                           format => DBMS_WORKLOAD_REPLAY.TYPE_TEXT);
END;
/

In this example, the GET_REPLAY_INFO procedure retrieves all information regarding the workload capture in the dec06 directory and the history of all the workload replay attempts from this directory. The procedure first imports a row into DBA_WORKLOAD_CAPTURES, which contains information about the workload capture. It then imports a row for every replay attempt retrieved from the replay directory into the DBA_WORKLOAD_REPLAYS view. The SELECT statement returns the appropriate rep_id for the latest replay of the workload. The REPORT function generates a text report using the rep_id that was returned by the SELECT statement.

The GET_CAPTURE_INFO procedure uses the dir required parameter, which specifies the name of the workload replay directory object.

The REPORT function uses the following parameters:

• The replay_id required parameter relates to the directory that contains the workload replay for which the report will be generated. The directory should be a valid directory in the host system containing the workload replay. The value of this parameter should match the rep_id returned by the GET_CAPTURE_INFO procedure.

• The format parameter required parameter specifies the report format. Valid values include DBMS_WORKLOAD_REPLAY.TYPE_TEXT, DBMS_WORKLOAD_REPLAY.TYPE_HTML, and DBMS_WORKLOAD_REPLAY.TYPE_XML.

For information about how to interpret the workload replay report, see "Reviewing Workload Replay Reports".

12.2.3 Reviewing Workload Replay Reports

After the workload is replayed on a test system, there may be some divergence in what is replayed compared to what was captured. There are numerous factors that can cause replay divergence, which can be analyzed using the workload replay report. The information contained in the workload replay report consists of performance and replay divergence.

Performance divergence may result when new algorithms are introduced in the replay system that affect the overall performance of the database. For example, if the workload is replayed on a newer version of Oracle Database, a new algorithm may cause specific requests to run faster, and the divergence will appear as a faster execution. In this case, this is a desirable divergence.

Data divergence occurs when the results of DML or SQL queries do not match results that were originally captured in the workload. For example, a SQL statement may return fewer rows during replay than those returned during capture.

Error divergence occurs when a replayed database call:

• Encounters a new error that was not captured

• Does not encounter an error that was captured

• Encounters a different error from what was captured

The information contained in the workload replay report are divided into the following categories:

• Details about the workload replay and the workload capture, such as job name, status, database information, duration and time of each process, and the directory object and path

• Replay options selected for the workload replay and the number of replay clients that were started

• Overall statistics about the workload replay and the workload capture (such as the total DB time captured and replayed, and the number of logins and transactions captured and replay) and the corresponding percentages with respect to total system activity

• Profile of the replayed workload

• Replay divergence

• Error divergence

• DML and SQL query data divergence

12.3.1 Generating Compare Period Reports Using Enterprise Manager

This section describes how to generate compare period reports using Oracle Enterprise Manager.

The primary tool for generating compare period reports is Oracle Enterprise Manager. If for some reason Oracle Enterprise Manager is unavailable, you can generate compare period reports using APIs, as described in "Generating Compare Period Reports Using APIs".

To generate compare period reports using Enterprise Manager:

1. On the Software and Support page, under Real Application Testing, click Database Replay.

   The Database Replay page appears.

2. In the Go to Task column, click the icon that corresponds to the Replay Workload task.

   The Replay Workload page appears.

3. In the Directory Object list, select the directory that contains the replayed workload for which you want to generate a compare period report.

   After the directory is selected, the Replay Workload page will be refreshed to display the Capture Summary and the Replay History sections.

4. The Replay History section displays previous replays of the workload capture. Select the replay for which you want to generate a compare period report and click View.

   The View Workload Replay page appears.

5. Click the Report tab.

6. Under Compare Period Report, select the first and second workload captures or replays you want to compare.

   
   Description of the illustration dbr_comp_period_report.gif
   
   

7. To generate a:

   • Replay Compare Period report, click Run Replay Compare Period Report.

     Use the replay compare period report to perform a high-level comparison of one workload replay to its capture or to another replay of the same capture. Only workload replays that contain at least 5 minutes of database time can be compared using this report.

     For information about how to interpret the replay compare period report, see "Reviewing Replay Compare Period Reports".

   • AWR Compare Period report, click Run AWR Compare Period Report.

     Use the AWR compare period report to compare the AWR data from one workload replay to its capture or to another replay of the same capture.

     For information about how to interpret AWR compare period reports, see Oracle Database 2 Day + Performance Tuning Guide.

   • SQL Performance Analyzer report, click Run SQL Performance Analyzer Report.

     If this is the first time you are generating a SQL Performance Analyzer report for the selected workload capture and workload replay, then click Yes to submit a new scheduler job.

     The SQL Performance Analyzer report can be used to compare a SQL tuning set from a workload capture to another SQL tuning set from a workload replay, or two SQL tuning sets from two workload replays. Comparing SQL tuning sets with Database Replay provides more information than SQL Performance Analyzer test-execute because it considers and shows all execution plans for each SQL statement, while SQL Performance Analyzer test-execute generates only one execution plan per SQL statement for each SQL trial.

     For information about how to interpret the SQL Performance Analyzer report, see "Reviewing the SQL Performance Analyzer Report Using Oracle Enterprise Manager".

   The Report window opens while the report is being generated. Once the report is generated, you can save the report by clicking Save to File.

12.3.2 Generating Compare Period Reports Using APIs

This section describes how to generate compare period reports using the DBMS_WORKLOAD_REPLAY package. You can also use Oracle Enterprise Manager to generate compare period reports, as described in "Generating Compare Period Reports Using Enterprise Manager".

This section contains the following topics:

• Generating Replay Compare Period Reports Using APIs

• Generating SQL Performance Analyzer Reports Using APIs

BEGIN
  DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT (
                           replay_id1 => 12,
                           replay_id2 => 17,
                           format => 'DBMS_WORKLOAD_CAPTURE.TYPE_HTML',
                           result => :report_bind);
END;
/

BEGIN
  DBMS_WORKLOAD_REPLAY.COMPARE_SQLSET_REPORT (
                           replay_id1 => 12,
                           format => 'DBMS_WORKLOAD_CAPTURE.TYPE_HTML',
                           result => :report_bind);
END;
/

12.3.3 Reviewing Replay Compare Period Reports

You can use replay compare period reports to perform a high-level comparison of one workload replay to its capture or to another replay of the same capture. The replay compare period report contains a summary of the most important changes between the two runs. By reviewing this report, you can determine if any replay divergence occurred and whether there were any significant performance changes. You can then use this information to determine the appropriate action to take, such as reviewing Automatic Database Diagnostic Monitor (ADDM) reports to diagnose a new concurrency issue, or running SQL Tuning Advisor to fix a new SQL performance problem.

The replay compare period report uses the following structure:

• General Information

  This section contains metadata about the two runs being compared in the report. Any init.ora parameter changes between the two runs are also shown here.

• Replay Divergence

  This section contains the divergence analysis of the second run relative to the first.

• Main Performance Statistics

  This section contains a high-level performance statistic comparison across the two runs (such as change in database time).

• Top SQL by Change in DB Time

  This section compares the performance change of top SQL statements (ordered by total changed in database time) from one run to the next.

• Hardware Usage Comparison

  This section compares the hardware resource usage across the two runs.

• ADDM Comparison

  This section contains an ADDM report comparison across the two runs.