This chapter describes how to generate and analyze Database Replay reports. There are two types of reports for Database Replay: the workload capture report and the workload replay report. The workload capture report contains captured workload statistics, information about the top session activities that were captured, and any workload filters used during the capture process. The workload replay report contains information that can be used to measure performance differences between the capture system and the replay system.
This section contains the following topics:
Generating a Workload Capture Report Using Enterprise Manager
Generating a Workload Replay Report Using Enterprise Manager
Note:
After the replay analysis is complete, you can restore the database to its original state at the time of workload capture and repeat workload replay to test other changes to the system once the workload directory object is backed up to another physical location.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 a Workload Capture Report Using APIs".
To generate a workload capture report using Enterprise Manager:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
Click View Workload Capture History.
The View Workload Capture History page appears.
Select the workload capture for which you want to run a workload capture report and click View.
The View Workload Capture page appears.
To view the workload capture report, click View Workload Capture Report.
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.
For information about how to use a workload capture report, see "Using a Workload Capture Report".
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 a Workload Capture Report 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
parameter 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 use a workload capture report, see "Using a Workload Capture Report".
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about theDBMS_WORKLOAD_CAPTURE
packageThe 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
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 a Workload Replay Report Using APIs"
To generate a workload replay report using Enterprise Manager:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
In the Go to Task column, click the icon that corresponds to the Replay Workload task.
The Replay Workload page appears.
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.
Under Replay History, select the replay for which you want to generate a workload replay report and click View.
The View Workload Replay page appears.
Click View Workload Replay Report.
For information about using the workload replay report, see "Using a Workload Replay Report".
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 a Workload Replay Report 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:
To generate a workload replay report, use the 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 using the workload replay report, see "Using a Workload Replay Report".
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about theDBMS_WORKLOAD_REPLAY
packageAfter 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 data 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