10 Preprocessing a Database Workload

After a workload is captured and setup of the test system is complete, the captured data must be preprocessed. Preprocessing a captured workload creates all necessary metadata for replaying the workload. This must be done once for every captured workload before they can be replayed. After the captured workload is preprocessed, it can be replayed repeatedly on a replay system.

To preprocess a captured workload, you will first need to move all captured data files from the directory where they are stored on the capture system to a directory on the instance where the preprocessing will be performed. Preprocessing is resource intensive and should be performed on a system that is:

Separate from the production system
Running the same version of Oracle Database as the replay system

For Oracle Real Application Clusters (Oracle RAC), select one database instance of the replay system for the preprocessing. This instance must have access to the captured data files that require preprocessing, which can be stored on a local or shared file system. If the capture directory path on the capture system resolves to separate physical directories in each instance, you will need to move all the capture files created in each of these directories into a single directory on which preprocessing will be performed.

Typically, you will preprocess the captured workload on the replay system. If you plan to preprocess the captured workload on a system that is separate from the replay system, you will also need to move all preprocessed data files from the directory where they are stored on the preprocessing system to a directory on the replay system after preprocessing is complete.

This chapter contains the following sections:

Preprocessing a Database Workload Using Enterprise Manager
Preprocessing a Database Workload Using APIs

Tip:

Before you can preprocess a captured workload, you need to capture the workload on the production system, as described in Chapter 9, "Capturing a Database Workload".

10.1 Preprocessing a Database Workload Using Enterprise Manager

This section describes how to preprocess a captured workload using Enterprise Manager.

The primary tool for preprocessing workload captures is Oracle Enterprise Manager. If for some reason Oracle Enterprise Manager is unavailable, you can preprocess workload captures using the APIs, as described in "Preprocessing a Database Workload Using APIs".

To preprocess a captured workload 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 Preprocess Captured Workload task.

The Preprocess Captured Workload page appears.

Description of the illustration dbr_process_empty.gif
In the Directory Object list, select a directory that contains the captured workload that you want to preprocess.

After a directory is selected, the Preprocess Captured Workload page will be refreshed to display the Capture Summary section, which contains information about the captured workload in the selected directory.

Description of the illustration dbr_process.gif

To view additional details about the captured workload, expand Capture Details. The expanded Capture Details section displays the workload profile and details for the captured workload.
Click Preprocess Workload.

The Preprocess Captured Workload: Database Version page appears.

Description of the illustration dbr_process_dbversion.gif
Ensure that the current database version displayed matches the database version on the intended replay system. Preprocessing must be performed on a system that is running the same version of Oracle Database as the replay system.

Under Advanced, select whether to run the Workload Analyzer. Workload Analyzer analyzes a workload capture directory and identifies parts of a captured workload that may not replay accurately due to insufficient data, errors that occurred during workload capture, or usage features that are not supported by Database Replay. The results of the workload analysis are saved to an HTML report named wcr_cap_analysis.html located in the capture directory that is being analyzed. If an error can be prevented, the workload analysis report displays available preventive actions that can be implemented before replay. If an error cannot be corrected, the workload analysis report provides a description of the error so it can be accounted for during replay. This option is enabled by default if the Workload Analyzer has not been run previously. It is strongly recommended that you run Workload Analyzer as part of the workload preprocessing.

Click Next.

The Preprocess Captured Workload: Schedule page appears.

Description of the illustration dbr_process_schedule.gif
Define the parameters for the preprocessing job.
- Under Job Parameters, enter a name and a description for the job.
- Under Start, select whether the job will run immediately by selecting Immediately, or at a later time by selecting Later and specifying the desired time using the Date and Time fields.
- Under Host Credentials, enter the user name and password information for the database host that will used for the preprocessing.
After defining the job parameters, click Next.

The Preprocess Captured Workload: Review page appears.

Description of the illustration dbr_process_review.gif
Review the selected options for the preprocessing job.

To preprocess the captured workload, click Submit. To make changes, click Back. To cancel preprocessing without saving changes, click Cancel.

Tip:

After preprocessing a captured workload, you can replay it on the test system, as described in Chapter 11, "Replaying a Database Workload".

10.2 Preprocessing a Database Workload Using APIs

This section describes how to preprocess a captured workload using the DBMS_WORKLOAD_REPLAY package. You can also use Oracle Enterprise Manager to preprocess a captured workload, as described in "Preprocessing a Database Workload Using Enterprise Manager".

To preprocess a captured workload, use the PROCESS_CAPTURE procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'dec06');
END;
/

In this example, the captured workload stored in the dec06 directory will be preprocessed.

The PROCESS_CAPTURE procedure in this example uses the capture_dir required parameter, which specifies the directory that contains the captured workload to be preprocessed.

Tip:

After preprocessing a captured workload, you can replay it on the test system, as described in Chapter 11, "Replaying a Database Workload".

10.2.1 Running the Workload Analyzer Command-Line Interface

The Workload Analyzer is a Java program that analyzes a workload capture directory and identifies parts of a captured workload that may not replay accurately due to insufficient data, errors that occurred during workload capture, or usage features that are not supported by Database Replay. The results of the workload analysis are saved to an HTML report named wcr_cap_analysis.html located in the capture directory that is being analyzed. If an error can be prevented, the workload analysis report displays available preventive actions that can be implemented before replay. If an error cannot be corrected, the workload analysis report provides a description of the error so it can be accounted for during replay. Running Workload Analyzer is the default option and is strongly recommended.

Note:

If you are preprocessing a workload capture using Oracle Enterprise Manager, then you do not need to run Workload Analyzer in the command-line interface. Oracle Enterprise Manager enables you to run Workload Analyzer as part of the workload preprocessing.

Workload Analyzer is composed of two JAR files, dbranalyzer.jar and dbrparser.jar, located in the $ORACLE_HOME/rdbms/jlib/ directory of a system running Oracle Database Enterprise Edition Release 11.2.0.2 or higher. Workload Analyzer requires Java 1.5 or higher and the ojdbc5.jar file located in the $ORACLE_HOME/jdbc/lib/ directory. To run Workload Analyzer in the command-line interface, run the following java command on a single line:

java -classpath $ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/rdbms/jlib/dbrparser.jar: $ORACLE_HOME/rdbms/jlib/dbranalyzer.jar: oracle.dbreplay.workload.checker.CaptureChecker <capture_directory> <connection_string>

For the capture_directory parameter, input the operating system path of the capture directory. This directory should also contain the exported AWR data for the workload capture. For the connection_string parameter, input the connection string of an Oracle database that is release 11.1 or higher.

The Workload Analyzer program will then prompt you to input the username and password of a database user with EXECUTE privileges for the DBMS_WORKLOAD_CAPTURE package and the SELECT_CATALOG role on the target database.

An example of this command may be:

java -classpath $ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/rdbms/jlib/dbrparser.jar: $ORACLE_HOME/rdbms/jlib/dbranalyzer.jar: oracle.dbreplay.workload.checker.CaptureChecker /scratch/capture jdbc:oracle:thin:@myhost.mycompany.com:1521:orcl