Oracle DBMS_WORKLOAD_REPLAY

Once the DBA has captured and copied the workload files from source to target, a method is needed for managing the replay of that workload. That is the job of the package DBMS_WORKLOAD_REPLAY. While replays are most often run against a different database, e.g. test vs. production, they can also be replayed on the source if so desired. Furthermore, the source and target can be different database platforms and/or different database versions. This permits a wide range of "what-if" testing scenarios.

The next step, and content of this section, is the target database workload replay process provided by the package DBMS_WORKLOAD_REPLAY.

Calibrate is a DBMS_WORKLOAD_REPLAY function that examines a processed workload capture directory of files and estimates/suggests the number of hosts and workload clients required to faithfully reproduce the sample workload characteristics. It returns a report for that advice as a CLOB.

Cancel_replay is a DBMS_WORKLOAD_REPLAY procedure that terminates a workload replay already in progress. All the workload clients are signaled to stop issuing further captured workload and to exit. A prior call to INITIALIZE_REPLAY, PREPARE_REPLAY or START_REPLAY is a prerequisite.

Delete_replay_info is a DBMS_WORKLOAD_REPLAY procedure that clears the data dictionary views containing metadata for a given workload replay ID. This does not delete the captured workload files, merely the metadata definition.

Export_awr is a DBMS_WORKLOAD_REPLAY procedure that exports all the AWR snapshots for a given replay ID. Of course, the AWR snapshots must still exist at the time of this call for the procedure to function properly.

Get_replay_info is a DBMS_WORKLOAD_REPLAY function that imports workload capture meta-data information from a directory and populates that data in the appropriate data dictionary metadata tables. It returns a NUMBER that represents either the DBA_WORKLOAD_REPLAYS row inserted or found already existing.

Import_awr is a DBMS_WORKLOAD_REPLAY function that simply imports all the AWR snapshots for a given capture ID into a specified schema. The import will fail if the schema contains any tables with the same names as any of the AWR tables. It returns a NUMBER for the database ID.

Initialize_replay is a DBMS_WORKLOAD_REPLAY procedure that puts the database into the proper state, i.e. INIT FOR REPLAY mode, and loads all the requisite data required for proper replay. The next logical step is prepare_replay.

Prepare_replay is a DBMS_WORKLOAD_REPLAY procedure that puts the database into the proper state, i.e. PREPARE FOR REPLAY mode, after the requisite data has been loaded via initialize_replay. The next logical step is to instantiate one or more external replay clients. The connect time and scale time parameters specify a percentage value to potentially increase or decrease the number of concurrent users.

Process_capture is a DBMS_WORKLOAD_REPLAY procedure that analyzes the workload capture files found in a directory and produces the metadata control files required to replay that workload capture for the current environment. A prime example of when and why this step is necessary is when the source and target databases are different versions (e.g. 10g -> 11g). It creates all new files and does not affect the original captured workload files.

Remap_connection is a DBMS_WORKLOAD_REPLAY procedure that reassigns captured connection information from the source to user specified connection criteria on the target. By default, NULL means to inherit that information from the replay clients' runtime environment. The REPLAY_CONNECTION string can be of any valid format for specifying database connection identifiers.

Report is a DBMS_WORKLOAD_REPLAY function that generates and returns a report for the specified workload replay. The valid report formats are 'HTML', 'TEXT' and 'XML'. It returns that report as a CLOB.

Start_replay is a DBMS_WORKLOAD_REPLAY procedure that initiates the workload replay. All external workload replay clients will be signaled to issue captured workload requests. This procedure has no parameters.

Although PL/SQL provides this wonderful programmatic interface, most people will probably simply use the Oracle Enterprise Manager (OEM) screens for capturing and replaying of RAT workloads. Figure 7.4 below shows an example of a verified 20% runtime performance improvement for modifying a single init.ora parameter where the captured workload was run with target and source the same but with just a single configuration change.

Get the Complete Oracle Utility Information  The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.