Using DBMS_WORKLOAD_REPLAY

Overview

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 is used in the production system to capture a production workload, and the DBMS_WORKLOAD_REPLAY package is subsequently used in a test system to replay the captured production workload for testing purposes.

Security Model

The following code describes the minimum set of privileges required to

Create directory objects

Operate the interface provided by the DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY packages

Appropriate OS permissions are required to access and manipulate files and directories on both the capture and replay system. 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.

The replay client is a multithreaded program (an executable named wrc located in the $ORACLE_HOME/bin directory) where each thread submits a workload from a captured session. The OS user performing the replay must be able to execute wrc on hosts that are 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.

ADD_CAPTURE Function

This function adds the given capture to the current schedule. The directory has to be a valid capture processed in the current database's version. It returns a unique ID that identifies this capture within this schedule.

ADD_FILTER Procedure

This procedure adds a filter to replay only a subset of the captured workload.

The procedure adds a new filter that is used in the next replay filter set created using the CREATE_FILTER_SET Procedure. This filter will be considered an "INCLUSION" or "EXCLUSION" filter depending on the argument passed to CREATE_FILTER_SET when creating the filter set.

(Mandatory) Name of the filter. Can be used to delete the filter later if it is not required.

fattribute

(Mandatory) Specifies the attribute on which the filter is defined as one of the following values of type STRING :

USER

MODULE

ACTION

PROGRAM

SERVICE

CONNECTION_STRING

fvalue

(Mandatory) Specifies the value to which the given 'attribute' must be equal to for the filter to be considered active. Wildcards such as '%' are acceptable for all attributes that are of type STRING. Currently all the listed values of fattribute are of type STRING. INSTANCE_NUMBER is a NUMBER attribute. It is currently only supported for capture.

ADD_SCHEDULE_ORDERING Function

This function adds a schedule order between two captures. Together, schedule_capture_id and waitfor_capture_id form a schedule ordering that previously added by the ADD_SCHEDULE_ORDERING Function. The order is that replay of capture indicated by schedule_capture_id will not start unless the replay of capture indicated by waiting_for_capture_id finishes.

Points to a capture that has been added to the current replay schedule. According to the new schedule ordering added by this subprogram, its replay will not start until the replay of another capture specified by waitfor_capture_id runs to completion.

waitfor_capture_id

Points to a capture that has been added to the current replay schedule. According to the new schedule ordering added by this subprogram, the replay of capture specified by schedule_capture_id will not start until the replay of this capture runs to completion.

CALIBRATE Function

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.

CREATE_FILTER_SET Procedure

This procedure creates a new filter set for the replays at replay_dir. It includes all the replay filters that have already been added by the ADD_FILTER Procedure. After the procedure has completed and replay initiated, the newly-created filter set can be used to filter the replay in replay_dir by calling the USE_FILTER_SET Procedure.

Can be either INCLUDE or EXCLUDE. Determines whether, by default, every captured call must be replayed or not. Also determines whether the workload filters specified must be considered as INCLUSION filters or EXCLUSION filters.)

If it is INCLUDE, then by default all captured calls are replayed, except for the part of the workload defined by the filters. In this case, all the filters that were specified using the ADD_SCHEDULE_ORDERING Function are treated as EXCLUSION filters, and will determine the workload that will not be replayed.

If it is EXCLUDE, then by default no captured call to the database is replayed, except for the part of the workload defined by the filters. In this case, all the filters that were specified using he ADD_SCHEDULE_ORDERING Function are treated as INCLUSION filters, and will determine the workload that is replayed.

Default: INCLUDE and all the filters specified are assumed to be EXCLUSION filters

Usage Notes

This operation must be invoked when no replay is initialized, prepared, or in progress.

DELETE_FILTER Procedure

This procedure deletes the named filter.

Syntax

DBMS_WORKLOAD_REPLAY.DELETE_FILTER(
fname IN VARCHAR2);

Parameters

Table 161-11 DELETE_FILTER Procedure Parameters

Parameter

Description

fname

(Mandatory) Name of the filter that must be deleted

DELETE_REPLAY_INFO Procedure

This procedure deletes the rows in DBA_WORKLOAD_REPLAYS that correspond to the given workload replay ID.

Syntax

DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO (
replay_id IN NUMBER);

Parameters

Table 161-12 DELETE_REPLAY_INFO Procedure Parameters

Parameter

Description

replay_id

(Mandatory) ID of the workload replay that must be deleted. Corresponds to DBA_WORKLOAD_REPLAYS.ID

END_REPLAY_SCHEDULE Procedure

This procedure wraps up the creation of the current schedule. The schedule is now saved and associated with the replay directory and can be used for a replay.

EXPORT_AWR Procedure

This procedure exports the AWR snapshots associated with a stipulated replay ID.

Syntax

DBMS_WORKLOAD_REPLAY.EXPORT_AWR (
replay_id IN NUMBER);

Parameters

Table 161-13 EXPORT_AWR Function Parameters

Parameter

Description

replay_id

(Mandatory) ID of the replay whose AWR snapshots are to be exported

Usage Notes

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.

GENERATE_CAPTURE_SUBSET Procedure

This procedure creates a new capture from an existing workload capture.

(Mandatory) Name of the directory object that points to an existing workload capture

output_capture_dir

(Mandatory) Name of the directory object that points to the new capture

new_capture_name

(Mandatory) Name of new capture

begin_time

Start of the time range - time offset in seconds from the start of a workload capture

begin_include_incomplete

Column to include incomplete calls caused by begin_time

end_time

End of the time range - time offset in seconds from the start of a workload capture. If end_time is zero or end_time is less or equal than begin_time, the time range is invalid. The new capture will use the whole duration of the input capture.

end_include_incomplete

Column to include incomplete calls caused by end_time

parallel_level

Number of Oracle processes used to process the input captures in a parallel fashion. The NULL default value will auto-compute the parallelism level based on number of CPUs, whereas a value of 1 will enforce serial execution.

GET_DIVERGING_STATEMENT Function

This function retrieves information about a diverging call, including the statement text, the SQL ID, and the binds. If the replay of a recorded user call has data or error divergence, it is a diverging call.

This function will silently invoke the POPULATE_DIVERGENCE Procedure to read the information from the capture files. Therefore, if divergence has not been populated, then the first call to this function for a particular diverging call might take longer, especially in very large captures.

(Mandatory) Name of the workload replay directory object (case sensitive).

Return Values

The procedure returns the CAPTURE_ID, which can be associated with both DBA_WORKLOAD_CAPTURES.ID and DBA_WORKLOAD_REPLAYS.CAPTURE_ID to access the imported information.

Usage Notes

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.

Lower bound of call delay in minutes. The replay action is activated only when the delay is equal to or more than min_delay.

max_delay

Upper bound of call delay in minutes. The timeout action throws ORA-15569 when the delay is more than max_delay.

delay_factor

Factor for the call delay that is between min_delay and max_delay. The timeout action throws ORA-15569 when the current replay elapsed time is more than the product of capture elapsed time and delay_factor.

(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 SYSAWR schema. The SYS schema is not a valid input.

Return Values

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.

Usage Notes

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. Drop any such tables in the staging_schema before invoking IMPORT_AWR.

INITIALIZE_CONSOLIDATED_REPLAY Procedure

This procedure puts the database state in INIT for a multiple-capture replay. It uses the replay_dir which has already been defined by the SET_REPLAY_DIRECTORY Procedure, pointing to a directory that contains all the capture directories involved in the schedule. It reads data about schedule schedule_name from the directory, and loads required connection data into the replay system.

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 the user 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.

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.

PAUSE_REPLAY 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.

User calls that were already in-progress when this procedure was invoked are allowed to run to completion. Only subsequent user calls, when issued, are paused.

POPULATE_DIVERGENCE Procedure

This procedure precomputes the divergence information for the given call, stream, or the whole replay so that the GET_DIVERGING_STATEMENT Function returns as quickly as possible for the precomputed calls.

Syntax

DBMS_WORKLOAD_REPLAY.POPULATE_DIVERGENCE (
replay_id IN NUMBER,
stream_id IN NUMBER DEFAULT NULL,
call_counter IN NUMBER DEFAULT NULL);

Parameters

Table 161-21 POPULATE_DIVERGENCE Procedure Parameters

Parameter

Description

replay_id

ID of the replay

stream_id

Stream ID of the diverging call. If NULL is provided, then divergence information is precomputed for all diverging calls in the given replay.

call_counter

Call counter of the diverging call. If NULL is provided, then divergence information is precomputed for all diverging calls in the given stream.

SCN - The COMMIT order observed during the original workload capture is preserved during replay. Every action that is replayed is executed only after all of its dependent COMMITs (all COMMITs that were issued before the given action in the original workload capture) have finished execution.

OBJECT_ID - This is the default, and uses a more advanced synchronization scheme. Every action that is replayed is executed only after the relevant COMMITs have finished executing. The relevant COMMITs are the ones that were issued before the given action in the original workload capture and that had modified at least one of the database objects the given action is referencing (either implicitly or explicitly). This OBJECT_ID scheme has the same logical property of making sure that any action will see the same data it saw during capture, but will allow more concurrency during replays for the actions that do not touch the same objects/tables.

For legacy reasons, there is a boolean version of this procedure:

TRUE means 'OBJECT_ID'

FALSE means 'OFF'

connect_time_scale

Scales the time elapsed between the instant the workload capture was started and the 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. DEFAULTVALUE is 100. See "Application of the connect_time_scale Parameter".

think_time_scale

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. DEFAULTVALUE is 100. See "Application of the think_time_scale Parameter".

think_time_auto_correct

Auto corrects the think time between calls appropriately when a user call takes longer to complete during replay than during the original capture. DEFAULT is TRUE which is to reduce think time if replay goes slower than capture. See "Application of the think_time_auto_correct Parameter"

scale_up_multiplier

Defines the number of times the query workload is scaled up during replay. Each captured session is replayed concurrently as many times as the value of the scale_up_multiplier. However, only one of the sessions in each set of identical replay sessions executes both queries and updates. The remaining sessions only execute queries.

capture_sts

If this parameter is TRUE, then a SQL tuning set capture is also started in parallel with workload replay. The resulting SQL tuning set can be exported using the EXPORT_AWR Procedure along with the AWR data. Currently, parallel SQL tuning set (STS) capture is not supported in an Oracle RAC environment. So, this parameter has no effect in that context. The calling user must have the appropriate privileges ('ADMINISTER SQL TUNING SET'). The default value is FALSE.

sts_cap_interval

Specifies the capture interval of the SQL set capture from the cursor cache in seconds. The default value is 300.

One or more external replay clients (WRC) can be started once the PREPARE_REPLAY procedure has been executed.

With regard to scale_up_multiplier:

One replay session (base session) of each set of identical sessions will replay every call from the capture as usual.

The remaining sessions (scale-up sessions) will only replay calls that are read-only. Thus, DDL, DML, and PL/SQL calls that modified the database is skipped. SELECTFORUPDATE statements are also skipped.

Read-only calls from the scale-up are synchronized appropriately and obey the timings defined by think_time_scale, connect_time_scale, and think_time_auto_correct. Also, the queries are made to wait for the appropriate commits.

No replay data or error divergence records are generated for the scale-up sessions.

All base or scale-up sessions that replay the same capture file will connect from the same workload replay client.

PREPARE_CONSOLIDATED_REPLAY Procedure

Similar to the PREPARE_REPLAY Procedure, this procedure puts the database in a special "Prepare" mode for a multiple-capture replay. The difference is that this subprogram should be used only for consolidated replays.

Turns synchronization ON or OFF during workload replay. When synchronization is ON, the COMMIT order observed during the original workload capture is preserved during replay. Every action that is replayed is executed ONLYAFTER all of its dependent COMMITs (all COMMITs that were issued before the given action in the original workload capture) have finished execution. DEFAULT is TRUE which preserves commit order.

When synchronization is OBJECT_ID, a more advanced synchronization scheme is used. Every action that is replayed is executed only after the relevant COMMITs have finished executing. The relevant COMMITs are the ones that were issued before the given action in the original workload capture and that had modified at least one of the database objects the given action is referencing (either implicitly or explicitly). This OBJECT_ID scheme has the same logical property of making sure that any action will see the same data it saw during capture, but will allow more concurrency during replays for the actions that do not touch the same objects/tables. DEFAULT VALUE: SCN, preserve commit order. For legacy reasons, there is a boolean version of this procedure:

TRUE means 'SCN'

FALSE means 'OFF'

connect_time_scale

Scales the time elapsed between the instant the workload capture was started and the 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. DEFAULTVALUE is 100. See "Application of the connect_time_scale Parameter".

think_time_scale

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. DEFAULTVALUE is 100. See "Application of the think_time_auto_correct Parameter"

think_time_auto_correct

Auto corrects the think time between calls appropriately when user calls takes longer to complete during replay than during the original capture. DEFAULT is TRUE which is to reduce think time if replay goes slower than capture. See "Application of the think_time_auto_correct Parameter"

capture_sts

If this parameter is TRUE, then a SQL tuning set capture is also started in parallel with workload replay. The resulting SQL tuning set can be exported using the EXPORT_AWR Procedure along with the Automatic Workload Repository (AWR) data. Currently, parallel SQL tuning set (STS) capture is not supported in an Oracle RAC environment. So, this parameter has no effect in that context. The calling user must have the appropriate privileges ('ADMINISTER SQL TUNING SET'). The default value is FALSE.

sts_cap_interval

Specifies the capture interval of the SQL set capture from the cursor cache in seconds. The default value is 300.

Usage Notes

A consolidated replay replays multiple captures in one replay. Each capture records different system change number (SCN) values. For this reason SCN-based sync is not supported for consolidated replays. Consolidated replays only support non-sync mode and the Object-ID based synchronization, and SCN-based synchronization is currently not supported.

PROCESS_CAPTURE Procedure

This procedure processes the workload capture found in capture_dir in place.

Syntax

DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (
capture_dir IN VARCHAR2,
parallel_level IN NUMBER DEFAULT NULL);

Parameters

Table 161-24 PROCESS_CAPTURE Procedure Parameters

Parameter

Description

catpure_dir

(Mandatory) 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 are added to this directory.

parallel_level

Number of Oracle processes used to process the capture in parallel. The NULL default value will auto-compute the parallelism level, whereas a value of 1 will enforce serial execution.

Usage Notes

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 must be processed using PROCESS_CAPTURE 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 must be processed in version 11.1.0.1. The following procedure must be executed in an 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 times as required.

REMAP_CONNECTION Procedure

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.

ID of the connection to be remapped. Corresponds to DBA_WORKLOAD_CONNECTION_MAP.CONN_ID.

replay_connection

New connection string to be used during replay

Usage Notes

Prior to calling REMAP_CONNECTION all replay connection strings are set to NULL by default. If a replay_connection is NULL, then the replay sessions will connect as determined by the replay client's runtime environment. For example, if the environment variable TNS_ADMIN is defined and the user does not call the REMAP_CONNECTION Procedure, then the wrc executable will connect to the server specified in the tnsnames.ora file pointed to by TNS_ADMIN.

A valid replay_connection must specify a connect identifier or a service point. See 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 are used by the subsequent workload replay, and also to examine connection string remappings used for previous workload replays.

REMOVE_SCHEDULE_ORDERING Procedure

This procedure removes an existing schedule order from the current replay schedule. Together, schedule_capture_id and waitfor_capture_id form a schedule ordering that previously added by the ADD_SCHEDULE_ORDERING Function (schedule_capture_id, waitfor_capture_id). The order is that replay of capture indicated by schedule_capture_id will not start unless the replay of capture indicated by waiting_for_capture_id finishes.

RESUME_REPLAY Procedure

REUSE_REPLAY_FILTER_SET Procedure

This procedure reuses filters in the specified filter set as if each were added using the ADD_SCHEDULE_ORDERING Function. Each call adds one filter set, which is a collection of individual filters on various attributes. Also, a new filter rule can be added, and an existing filter can be deleted before invoking the CREATE_FILTER_SET Procedure to create a new filter set.

SET_ADVANCED_PARAMETER Procedure

This procedure sets an advanced parameter for replay besides the ones used with the PREPARE_REPLAY Procedure. The advanced parameters control aspects of the replay that are more specialized. The advanced parameters are reset to their default values after the replay has finished.

This parameter controls whether the COMMITs issued by replay sessions is NOWAIT. The default value for this parameter is FALSE. In this case all the COMMITs are issued with the mode they were captured (wait, no-wait, batch, no-batch). If the parameter is set to TRUE, then all COMMITs are issued in no-wait mode. This is useful in cases where the replay is becoming noticeably slow because of a high volume of concurrent COMMITs. Setting the parameter to TRUE will significantly decrease the waits on the 'log file sync' event during the replay with respect to capture.

SET_REPLAY_DIRECTORY Procedure

This procedure sets a directory that contains multiple workload captures as the current replay directory.

Syntax

DBMS_WORKLOAD_REPLAY.SET_REPLAY_DIRECTORY (
replay_dir IN VARCHAR2);

Parameters

Table 161-31 SET_REPLAY_DIRECTORY Procedure Parameters

Parameter

Description

replay_dir

Name of the OS directory containing the captures for a workload consolidation

SET_REPLAY_TIMEOUT Procedure

This procedure sets the replay timeout setting. The purpose is to abort user calls that might make the replay much slower or even cause a replay hang.

Syntax

DBMS_WORKLOAD_REPLAY.SET_REPLAY_TIMEOUT (
enabled OUT BOOLEAN DEFAULT TRUE,
min_delay OUT NUMBER DEFAULT 10,
max_delay OUT NUMBER DEFAULT 120,
delay_factor OUT NUMBER DEFAULT 8);

Parameters

Table 161-32 SET_REPLAY_TIMEOUT Procedure Parameters

Parameter

Description

enabled

TRUE to enable the timeout action, and FALSE to disable.

min_delay

Lower bound of call delay in minutes. The replay action is activated only when the delay is equal to or more than min_delay. Default = 10.

max_delay

Upper bound of call delay in minutes. The timeout action throws ORA-15569 when the delay is more than max_delay. Default = 120.

delay_factor

Factor for the call delay that is between min_delay and max_delay. The timeout action throws ORA-15569 when the current replay elapsed time is more than the product of capture elapsed time and delay_factor. Default = 8.

Usage Notes

This procedure can be called anytime during replay.

Call delay is defined as the difference between replay and capture if replay elapsed time is longer than call elapsed time.

Once a replay timeout action is enabled, a user call will exit with ORA-15569 if it has been delayed more than the condition specified by the replay action. The call and its error are reported as error divergence.

Replay timeout operates as follows:

The timeout action has no effect if it is not enabled.

If the call delay in minutes is less than a lower bound specified by parameter min_delay, then the timeout action is non-operational.

If the delay in minutes is more than a upper bound specified by parameter max_delay, the timeout action will abort the user call and throw ORA-15569.

For delay that is between the lower bound and upper bound, the user call will abort with ORA-15569 only when the current replay elapsed time is more than the product of capture elapsed time and parameter delay_factor.

SET_USER_MAPPING Procedure

This procedure sets a new schema or user name to be used during replay instead of the captured user.

A sufficient number of external replay clients (WRC) that can faithfully replay the captured workload already started. The status of such external replay clients can be monitored using V$WORKLOAD_REPLAY_CLIENTS.

START_REPLAY Procedure

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. It should only be used for consolidated replays.

A sufficient number of external replay clients (WRC) that can faithfully replay the captured workload already 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=.

USE_FILTER_SET Procedure

This procedure applies a filter set to a capture in the current replay schedule. The filter set must have been created by calling the CREATE_FILTER_SET Procedure.