The DBMS_WORKLOAD_REPLAY package provides an interface to replay a workload capture that was originally created by way of the DBMS_WORKLOAD_CAPTURE package. Typically, the DBMS_WORKLOAD_CAPTURE package will be used in the production system to capture a production workload, and the DBMS_WORKLOAD_REPLAY package will be subsequently used in a test system to replay the captured production workload for testing purposes.
See Also:
Oracle Database Real Application Testing User's Guide for more information about "Database Replay"This chapter contains the following topics:
The following code describes the minimal set of privileges required to
Create directory objects
Operate the interface provided by the DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY packages
Act as a replay client user (wrc someuser/somepassword or wrc USER=someuser PASSWORD=somepassword)
DROP USER rom1 CASCADE; CREATE USER rom1 IDENTIFIED BY rom1; GRANT EXECUTE ON DBMS_WORKLOAD_CAPTURE TO rom1; GRANT EXECUTE ON DBMS_WORKLOAD_REPLAY TO rom1; GRANT CREATE SESSION TO rom1; GRANT CREATE ANY DIRECTORY TO rom1; GRANT SELECT_CATALOG_ROLE TO rom1; GRANT BECOME USER TO rom1;
Appropriate OS permissions are required to access and manipulate files and directories on both the capture and replay system. This means that the Oracle process(es) and the OS user performing the capture or replay must be able to access and manipulate at least one common directory accessible from the host where the instance is running. Additionally, the OS user performing the replay should be able to execute wrc on hosts that will be used for the replay clients and be able to access the file system appropriately to be able to copy the capture to the replay clients' hosts if required.
This table list the package subprograms in alphabetical order.
Table 145-1 DBMS_WORKLOAD_REPLAY Package Subprograms
| Subprogram | Description |
|---|---|
|
Operates on a processed workload capture directory to estimate the number of hosts and workload replay clients needed to faithfully replay the given workload |
|
|
Cancels the workload replay in progress |
|
|
Deletes the rows in |
|
|
Exports the AWR snapshots associated with a given replay ID |
|
|
Retrieves information about the workload capture and the history of all the workload replay attempts from the related directory |
|
|
Imports the AWR snapshots associated with a given replay ID |
|
|
Initializes replay, and loads specific data produced during processing into the database |
|
|
Pauses the in-progress workload replay |
|
|
Puts the RDBMS in a special "Prepare" mode |
|
|
Processes the workload capture found in |
|
|
Remaps the captured connection to a new one so that the user sessions can connect to the database in a desired way during workload replay. |
|
|
Generates a report on the given workload replay |
|
|
Resumes a paused workload replay |
|
|
Starts the workload replay |
This function operates on a processed workload capture directory to estimate the number of hosts and workload replay clients needed to faithfully replay the given workload. This function returns the results as an XML CLOB.
DBMS_WORKLOAD_REPLAY.CALIBRATE ( capture_dir IN VARCHAR2, process_per_cpu IN BINARY_INTEGER DEFAULT 4, threads_per_process IN BINARY_INTEGER DEFAULT 50) RETURN CLOB;
Table 145-2 CALIBRATE Function Parameters
| Parameter | Description |
|---|---|
|
|
Name of the directory object that points to the (case sensitive) OS directory that contains processed capture data |
|
|
Maximum number of process allowed per CPU (default is 4) |
|
|
Maximum number of threads allowed per process (default is 50) |
Returns a CLOB formatted as XML, that contains:
Information about the capture
Current database version
Input parameters to this function
Number of CPUs and replay clients needed to replay the given workload
Information about the sessions captured (total number and maximum concurrency)
Prerequisite: The input workload capture was already processed using the PROCESS_CAPTURE Procedure in the same database version.
This procedure will return the same results as the workload replay client in calibrate mode, which can be run as follows.
$ wrc mode=calibrate replaydir=
This procedure cancels workload replay in progress. All the external replay clients (WRC) will automatically be notified to stop issuing the captured workload and exit.
DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ( error_msg IN VARCHAR2 DEFAULT NULL);
Table 145-3 CANCEL_REPLAY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
An optional reason for cancelling the replay can be passed which will be recorded into |
Prerequisite: A call to the INITIALIZE_REPLAY Procedure, or PAUSE_REPLAY Procedure, or START_REPLAY Procedure was already issued.
This procedure deletes the rows in DBA_WORKLOAD_REPLAYS that corresponds to the given workload replay ID.
DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO ( replay_id IN NUMBER);
Table 145-4 DELETE_REPLAY_INFO Procedure Parameters
| Parameter | Description |
|---|---|
|
|
ID of the workload replay that needs to be deleted. Corresponds To |
This procedure exports the AWR snapshots associated with a stipulated replay ID.
DBMS_WORKLOAD_REPLAY.EXPORT_AWR ( replay_id IN NUMBER);
Table 145-5 EXPORT_AWR Function Parameters
| Parameter | Description |
|---|---|
|
|
ID of the replay whose AWR snapshots should be exported. (Mandatory) |
At the end of each replay, the corresponding AWR snapshots are automatically exported. Consequently, there is no need to do this manually after a workload replay is complete, unless the automatic EXPORT_AWR invocation failed.
This procedure will work only if the corresponding workload replay was performed in the current database (meaning that the corresponding row in DBA_WORKLOAD_REPLAYS was not created by calling the GET_REPLAY_INFO Function) and the AWR snapshots that correspond to that replay time period are still available.
This function retrieves information about the workload capture and the history of all the workload replay attempts from the stipulated directory.
DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO ( dir IN VARCHAR2) RETURN NUMBER;
Table 145-6 GET_REPLAY_INFO Function Parameters
| Parameter | Description |
|---|---|
|
|
Name of the workload replay directory object (case sensitive). (Mandatory) |
The procedure returns the CAPTURE_ID which can be associated with both DBA_WORKLOAD_CAPTURE_ID and DBA_WORKLOAD_REPLAYS.CAPTURE.ID to access the imported information.
The procedure first imports a row into DBA_WORKLOAD_CAPTURES which will contain information about the capture. It then, imports a row for every replay attempt retrieved from the given replay directory into DBA_WORKLOAD_REPLAYS.
The procedure will not insert new rows to DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_REPLAYS if these views already contain rows describing the capture and replay history present in the given directory.
This procedure imports the AWR snapshots from a given replay.
DBMS_WORKLOAD_REPLAY.IMPORT_AWR ( replay_id IN NUMBER, staging_schema IN VARCHAR2) RETURN NUMBER;
Table 145-7 IMPORT_AWR Function Parameters
| Parameter | Description |
|---|---|
|
|
ID of the replay whose AWR snapshots should be exported. (Mandatory) |
|
|
Name of a valid schema in the current database which can be used as a staging area while importing the AWR snapshots from the replay directory to the |
Returns the new randomly generated database ID that was used to import the AWR snapshots. The same value can be found in the AWR_DBID column in the DBA_WORKLOAD_REPLAYS view.
This procedure will work provided those AWR snapshots were exported earlier from the original replay system using the EXPORT_AWR Procedure.
IMPORT_AWR will fail if the staging_schema provided as input contains any tables with the same name as any of the AWR tables, such as WRM$_SNAPSHOT or WRH$_PARAMETER. Please drop any such tables in the staging_schema before invoking IMPORT_AWR.
This procedure puts the database state in INIT for REPLAY mode, and loads data into the replay system that is required before preparing for the replay (by executing the PAUSE_REPLAY Procedure).
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY ( replay_name IN VARCHAR2, replay_dir IN VARCHAR2);
Table 145-8 INITIALIZE_REPLAY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the workload replay. Every replay of a processed workload capture can be given a name. (Mandatory) |
|
|
Name of the directory object that points to the OS directory (case sensitive) that contains processed capture data |
Prerequisites:
Workload capture was already processed using the PROCESS_CAPTURE Procedure in the same database version.
Database state has been logically restored to what it was at the beginning of the original workload capture.
The subprogram loads data into the replay system that is required before preparing for the replay by calling the PAUSE_REPLAY Procedure.
For instance, during capture we may record the connection string each session used to connect to the server. The INITIALIZE_REPLAY Procedure loads this data and allows the user to re-map the recorded connection string to new connection strings or service points.
Elaborating on the example described in the PROCESS_CAPTURE Procedure, one could invoke the following:
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY('replay foo #1', 'rec_dir');
This command will load up the connection map and by default will set all replay time connection strings to be equal to NULL. A NULL replay time connection string means that the workload replay clients (WRCs) will connect to the default host as determined by the replay client's runtime environment settings. The user can change a particular connection string to a new one (or a new service point) for replay by using the REMAP_CONNECTION Procedure.
This procedure pauses the in-progress workload replay. All subsequent user calls from the replay clients will be stalled until either a call to the RESUME_REPLAY Procedure is issued or the replay is cancelled.
DBMS_WORKLOAD_REPLAY.PAUSE_REPLAY;
Prerequisite: A call to the START_REPLAY Procedure must have already ben issued.
User calls that were already in-progress when PAUSE_REPLAY was issued will be allowed to run to completion. Only subsequent user calls, when issued, will be paused.
This procedure puts the database state in PREPARE FOR REPLAY mode.
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY ( synchronization IN BOOLEAN DEFAULT TRUE, connect_time_scale IN NUMBER DEFAULT 100, think_time_scale IN NUMBER DEFAULT 100, think_time_auto_correct IN BOOLEAN DEFAULT TRUE);
Table 145-9 PREPARE_REPLAY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Turns synchronization |
|
|
Scales the time elapsed between the instant the workload capture was started and session connects with the given value. The input is interpreted as a % value. Can potentially be used to increase or decrease the number of concurrent users during the workload replay. |
|
|
Scales the time elapsed between two successive user calls from the same session. The input is interpreted as a % value. Can potentially be used to increase or decrease the number of concurrent users during the workload replay. |
|
|
Auto corrects the think time between calls appropriately when user calls takes longer time to complete during replay than how long the same user call took to complete during the original capture. |
Prerequisites:
The database has been initialized for replay using the INITIALIZE_REPLAY Procedure.
Any capture time connection strings that require remapping have been already done using the REMAP_CONNECTION Procedure.
One or more external replay clients (WRC) can be started once the PREPARE_REPLAY procedure has been executed.
Application of the connect_time_scale Parameter
If the following was observed during the original workload capture:
12:00 : Capture was started 12:10 : First session connect (10m after) 12:30 : Second session connect (30m after) 12:42 : Third session connect (42m after)
If the connect_time_scale is 50, then the session connects will happen as follows:
12:00 : Replay was started with 50% connect time scale 12:05 : First session connect ( 5m after) 12:15 : Second session connect (15m after) 12:21 : Third session connect (21m after)
If the connect_time_scale is 200, then the session connects will happen as follows:
12:00 : Replay was started with 200% connect time scale 12:20 : First session connect (20m after) 13:00 : Second session connect (60m after) 13:24 : Third session connect (84m after)
Application of the think_time_scale Parameter
If the following was observed during the original workload capture:
12:00 : User SCOTT connects 12:10 : First user call issued (10m after completion of prevcall) 12:14 : First user call completes in 4mins 12:30 : Second user call issued (16m after completion of prevcall) 12:40 : Second user call completes in 10m 12:42 : Third user call issued ( 2m after completion of prevcall) 12:50 : Third user call completes in 8m
If the think_time_scale is 50 during the workload replay, then the user calls will look something like below:
12:00 : User SCOTT connects
12:05 : First user call issued 5 mins (50% of 10m) after the completion of
previous call
12:10 : First user call completes in 5m (takes a minute longer)
12:18 : Second user call issued 8 mins (50% of 16m) after the completion of prev
call
12:25 : Second user call completes in 7m (takes 3 minutes less)
12:26 : Third user call issued 1 min (50% of 2m) after the completion of prev
call
12:35 : Third user call completes in 9m (takes a minute longer)
Application of the think_time_auto_correct Parameter
If the following was observed during the original workload capture:
12:00 : User SCOTT connects 12:10 : First user call issued (10m after completion of prevcall) 12:14 : First user call completes in 4m 12:30 : Second user call issued (16m after completion of prevcall) 12:40 : Second user call completes in 10m 12:42 : Third user call issued ( 2m after completion of prevcall) 12:50 : Third user call completes in 8m
If the think_time_scale is 100 and the think_time_auto_correct is TRUE during the workload replay, then the user calls will look something like below:
12:00 : User SCOTT connects
12:10 : First user call issued 10 mins after the completion of prev call
12:15 : First user call completes in 5m (takes 1 minute longer)
12:30 : Second user call issued 15 mins (16m minus the extra time of 1m the prev
call took) after the completion of prev call
12:44 : Second user call completes in 14m (takes 4 minutes longer)
12:44 : Third user call issued immediately (2m minus the extra time of 4m the prev
call took) after the completion of prev call
12:52 : Third user call completes in 8m
This procedure processes the workload capture found in capture_dir in place.
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE ( capture_dir IN VARCHAR2);
Table 145-10 PROCESS_CAPTURE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the workload capture directory object (case sensitive). The directory object must point to a valid OS directory that has the appropriate permissions. New files will be added to this directory. (Mandatory) |
This subprogram analyzes the workload capture found in the capture_dir and creates new workload replay specific metadata files that are required to replay the given workload capture. It only creates new files and does not modify any files that were originally created during the workload capture. Therefore, this procedure can be run multiple times on the same capture directory, such as when the procedure encounters unexpected errors or is cancelled by the user.
Once this procedure runs successfully, the capture_dir can be used as input to the INITIALIZE_REPLAY Procedure in order to replay the captured workload present in capture_dir.
Before a workload capture can be replayed in a particular database version, the capture needs to be "processed" using this PROCESS_CAPTURE procedure in the same database version. Once created, a processed workload capture can be used to replay the captured workload multiple times in the same database version.
For example, suppose workload "foo" was captured in rec_dir in Oracle database version 10.2.0.5. In order to replay the workload "foo" in version 11.1.0.1 the workload needs to be processed in version 11.1.0.1. The following procedure needs to be executed in a 11.1.0.1 database in order to process the capture directory rec_dir:
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('rec_dir');
Now, rec_dir contains a valid 11.1.0.1 processed workload capture that can be used to replay the workload "foo" in 11.1.0.1 databases as many number of times as required.
This procedure remaps the captured connection to a new one so that the user sessions can connect to the database in a desired way during workload replay.
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION ( connection_id IN NUMBER, replay_connection IN VARCHAR2);
Table 145-11 REMAP_CONNECTION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
ID of the connection to be remapped.Corresponds to |
|
|
New connection string to be used during replay |
By default, all instances of replay_connection will be equal to NULL. When replay_connection is NULL (default), replay sessions will connect to the default host as determined by the replay client's runtime environment. Consequently, if no capture time connect strings are remapped, then all the replay sessions will simply connect to the default host to replay the workload.
A valid replay_connection should specify a connect identifier or a service point. Please refer to the Oracle Database Net Services Reference for ways to specify connect identifiers (such as net service names, database service names, and net service aliases) and naming methods that can be used to resolve a connect identifier to a connect descriptor.
An error is returned if no row matches the given connection_id.
Use the DBA_WORKLOAD_CONNECTION_MAP view to review all the connection strings that will be used by the subsequent workload replay, and also to examine connection string remappings used for previous workload replays.
This function generates a report on the stipulated workload replay.
DBMS_WORKLOAD_REPLAY.REPORT ( replay_id IN NUMBER, format IN VARCHAR2 ) RETURN CLOB;
Table 145-12 REPORT Function Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the ID of the workload replay whose report is requested. (Mandatory) |
|
|
Specifies the report format. Valid values are |
The report body in the desired format returned as a CLOB
Table 145-13 Constants Used by Report Function
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
'HTML' |
Generates the HTML version of the report |
|
|
|
'TEXT' |
Use this as input to the |
|
|
|
'XML' |
Generates the XML version of the report |
This procedure resumes a paused workload replay.
DBMS_WORKLOAD_REPLAY.RESUME_REPLAY;
Prerequisite: A call to the PAUSE_REPLAY Procedure must have already been issued.
This procedure starts the workload replay. All the external replay clients (WRC) that are currently connected to the replay database will automatically be notified and those replay clients (WRC) will begin issuing the captured workload.
DBMS_WORKLOAD_REPLAY.START_REPLAY;
Prerequisites:
The call to the PAUSE_REPLAY Procedure was already issued.
Enough number of external replay clients (WRC) that can faithfully replay the captured workload have already been started. The status of such external replay clients can be monitored using V$WORKLOAD_REPLAY_CLIENTS.
Use the WRC's CALIBRATE mode to determine the number of replay clients that might be required to faithfully replay the captured workload. For example:
$ wrc mode=calibrate replaydir=.