11 Replaying a Database Workload

11.1.1 Setting Up the Replay Directory

The captured workload must have been preprocessed and copied to the replay system. A directory object for the directory to which the preprocessed workload is copied must exist in the replay system.

11.1.2 Restoring the Database

Before a workload can be replayed, the application data state of the replay system should be logically equivalent to that of the capture system at the start time of workload capture. This minimizes replay divergence during replay. The method for restoring the database depends on the backup method that was used before capturing the workload. For example, if RMAN was used to back up the capture system, you can use RMAN DUPLICATE capabilities to create the test database. For more information, see "Prerequisites for Capturing a Database Workload".

After the database is created with the appropriate application data on the replay system, perform the system change you want to test, such as a database or operating system upgrade. The primary purpose of Database Replay is to test the effect of system changes on a captured workload. Therefore, the system changes you make should define the test you are conducting with the captured workload.

11.1.3 Resolving References to External Systems

A captured workload may contain references to external systems, such as database links or external tables. Typically, you should reconfigure these external interactions to avoid impacting other production systems during replay. External references that need to be resolved before replaying a workload include:

• Database links

  It is typically not desirable for the replay system to interact with other databases. Therefore, you should reconfigure all database links to point to an appropriate database that contains the data needed for replay.

• External tables

  All external files specified using directory objects referenced by external tables need to be available to the database during replay. The content of these files should be the same as during capture, and the filenames and directory objects used to define the external tables should also be valid.

• Directory objects

  You should reconfigure any references to directories on the production system by appropriately redefining the directory objects present in the replay system after restoring the database.

• URLs

  URLs/URIs that are stored in the database need to be configured so that Web services accessed during the workload capture will point to the proper URLs during replay. If the workload refers to URLs that are stored in the production system, you should isolate the test system network during replay.

• E-mails

  To avoid resending E-mail notifications during replay, any E-mail server accessible to the replay system should be configured to ignore requests for outgoing E-mails.

11.1.4 Remapping Connections

During workload capture, connection strings used to connect to the production system are captured. In order for the replay to succeed, you need to remap these connection strings to the replay system. The replay clients can then connect to the replay system using the remapped connections.

For Oracle Real Application Clusters (Oracle RAC) databases, you can map all connection strings to a load balancing connection string. This is especially useful if the number of nodes on the replay system is different from the capture system. Alternatively, if you want to direct workload to specific instances, you can use services or explicitly specify the instance identifier in the remapped connection strings.

11.1.5 User Remapping

During workload capture, the username of the database user or schema used to connect to the production system is captured. You can choose to remap the captured username to that of a new user or schema.

11.1.6 Specifying Replay Options

After the database is restored and connections are remapped, you can set the following replay options as appropriate:

• Preserving COMMIT Order

• Controlling Session Connection Rate

• Controlling Request Rate Within a Session

11.1.7 Using Filters with Workload Replay

By default, all captured database calls are replayed during workload replay. You can use workload filters to specify which database calls to include in or exclude from the workload during workload replay.

Workload replay filters are first defined and then added to a replay filter set so they can be used in a workload replay. There are two types of workload filters: inclusion filters and exclusion filters. Inclusion filters enable you to specify database calls that will be replayed. Exclusion filters enable you to specify database calls that will not be replayed. You can use either inclusion filters or exclusion filters in a workload replay, but not both. The workload filter is determined as an inclusion or exclusion filter when the replay filter set is created.

11.1.8 Setting Up Replay Clients

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. Before replay begins, the database will wait for replay clients to connect. At this point, you need to set up and start the replay clients, which will connect to the replay system and send requests based on what has been captured in the workload.

Before starting replay clients, ensure that the:

• Replay client software is installed on the hosts where it will run

• Replay clients have access to the replay directory

• Replay directory contains the preprocessed workload capture

• Replay user has the correct user ID, password, and privileges (the replay user needs the DBA role and cannot be the SYS user)

• Replay clients are not started on a system that is running the database

• Replay clients read the capture directory on a file system that is different from the one on which the database files reside

  To do this, copy the capture directory to the system where the replay client will run. After the replay is completed, you can delete the capture directory.

After these prerequisites are met, you can proceed to set up and start the replay clients using the wrc executable. The wrc executable uses the following syntax:

wrc [user/password[@server]] MODE=[value] [keyword=[value]]

The parameters user, password and server specify the username, password and connection string used to connect to the replay database. The parameter mode specifies the mode in which to run the wrc executable. Possible values include replay (the default), calibrate, and list_hosts. The parameter keyword specifies the options to use for the execution and is dependent on the mode selected. To display the possible keywords and their corresponding values, run the wrc executable without any arguments.

The following sections describe the modes that you can select when running the wrc executable:

• Calibrating Replay Clients

• Starting Replay Clients

• Displaying Host Information

%> wrc mode=calibrate replaydir=./replay

Workload Replay Client: Release 11.2.0.0.2 - Production on Fri May 1
13:06:33 2009
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Report for Workload in: /oracle/replay/
-----------------------
 
Recommendation:
Consider using at least 21 clients divided among 6 CPU(s).
 
Workload Characteristics:
- max concurrency: 1004 sessions
- total number of sessions: 1013
 
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

%> wrc system/password@test mode=replay replaydir=./replay

%> wrc mode=list_hosts replaydir=./replay

Workload Replay Client: Release 11.2.0.0.2 - Production on Fri May 1 13:44:48 2009
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Hosts found:
Capture:
        prod1
        prod2
Replay 1:
        test1
Replay 2:
        test1
        test2
Replay 3:
        testwin

11.4.1 Monitoring an Active Workload Replay

This section describes how to monitor an active workload replay using Enterprise Manager.

To monitor an active workload replay:

1. From the Database Replay page, click the Replay Tasks tab.

2. Click the name of the replay task that contains the replay for which you want to monitor replay progress.

3. From the Replays section of the Replay Task page, click the name of the replay you have submitted for processing in the Create Replay wizard. (The Status column for this replay should show In Progress.)

   The Home tab of the Database Replay page appears, and the Replay Summary shows a Status of Running.

   • The replay line in the Replay Progress chart updates dynamically while the Replay is in progress. You can update the chart by clicking the Refresh button.

   • The user calls line in the Replay Progress chart indicates how much of the workload the database has serviced relative to the capture at the same time.

   • Data for the Replay Divergence Summary is not available until the Replay completes.

11.4.2 Viewing a Completed Workload Replay

This section describes how to view a completed workload replay using Enterprise Manager.

To view a completed workload replay:

1. From the Database Replay page, click the Replay Tasks tab.

2. Click the name of the replay task that contains the completed replay you want to view.

3. From the Replays section of the Replay Task page, click the name of the replay you have submitted for processing in the Create Replay wizard. (The Status column for this replay should show Completed.)

   The Home tab of the Database Replay page appears, and the Replay Summary shows a Status of Completed.

   
   Description of the illustration dbr_replay_completed.gif
   
   

   • The replay line in the User Calls chart graphically represents the replay progress during the course of the entire replay from initiation to conclusion. The elapsed time in the Replay Progress chart equates to the number of minutes that were set up for the associated Capture.

     The chart shows how much time it has taken to replay the same workload compared to the elapsed time during the workload capture in terms of user calls. If the Replay line is above or to the left of the Capture line, the replay system is processing the workload faster than the capture system.

   • Under the Replay Divergence Summary, any errors and data discrepancies between the replay system and the capture system are displayed as diverged database calls during replay. You can use the percentage of total calls that diverged as a measure of the replay quality.

     To view details about the diverged calls, click the link that corresponds to the type of diverged call in the Count column to access the Diverged Calls During Replay page. The Diverged Calls During Replay page shows the most relevant set of replayed calls that diverged from the workload captured by grouping them based on common attribute values and specified filter conditions. To view details about a particular diverged call—such as the call attributes, SQL text, and bind variables—click the corresponding link in the SQL ID column to bring up the Replay Diverged Statement page.

4. To return to the Database Replay page, click the Database Replay breadcrumb.

   Tip:

   See Chapter 12, "Analyzing Captured and Replayed Workloads" for information about accessing workload replay reports.

11.6.1 Initializing Replay Data

After the workload capture is preprocessed and the test system is properly prepared, the replay data can be initialized. Initializing replay data loads the necessary metadata into tables required by workload replay. For example, captured connection strings are loaded into a table where they can be remapped for replay.

To initialize replay data, use the INITIALIZE_REPLAY procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => 'dec06_102',
                           replay_dir => 'dec06');
END;
/

In this example, the INITIALIZE_REPLAY procedure loads preprocessed workload data from the dec06 directory into the database.

The INITIALIZE_REPLAY procedure in this example uses the following parameters:

• The replay_name required parameter specifies a replay name that can be used with other APIs to retrieve settings and filters of previous replays.

• The replay_dir required parameter specifies the directory that contains the workload capture that will be replayed.

11.6.2 Connection Remapping

After the replay data is initialized, connection strings used in the workload capture need to be remapped so that user sessions can connect to the appropriate databases and perform external interactions as captured during replay. To view connection mappings, use the DBA_WORKLOAD_CONNECTION_MAP view. For information about connection remapping, see "Remapping Connections".

To remap connections, use the REMAP_CONNECTION procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 101,
                           replay_connection => 'dlsun244:3434/bjava21');
END;
/

In this example, the connection that corresponds to the connection ID 101 will use the new connection string defined by the replay_connection parameter.

The REMAP_CONNECTION procedure in this example uses the following parameters:

• The connection_id required parameter is generated when initializing replay data and corresponds to a connection from the workload capture.

• The replay_connection required parameter specifies the new connection string that will be used during workload replay.

11.6.3 Setting Workload Replay Options

After the replay data is initialized and the connections are appropriately remapped, you need to prepare the database for workload replay. For information about workload replay preparation, see "Steps for Replaying a Database Workload".

To prepare workload replay on the replay system, use the PREPARE_REPLAY procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => OBJECT_ID,
                           capture_sts => TRUE,
                           sts_cap_interval => 300);
END;
/

In this example, the PREPARE_REPLAY procedure prepares a replay that has been previously initialized. A SQL tuning set will also be captured in parallel with the workload replay.

The PREPARE_REPLAY procedure uses the following parameters:

• The synchronization required parameter determines if synchronization will be used during workload replay.

  If this parameter is set to OBJECT_ID (the default value), all replay actions will be executed only after all relevant COMMIT actions have completed. Relevant COMMIT actions must meet the following criteria:

  • Issued before the given action in the workload capture

  • Modified at least one of the database objects for which the given action is referencing, either implicitly or explicitly

  Setting this parameter to OBJECT_ID allows for more concurrency during workload replays for COMMIT actions that do not reference the same database objects during workload capture.

  If this parameter is set to SCN, the COMMIT order in the captured workload will be preserved during replay and all replay actions will be executed only after all dependent COMMIT actions have completed.

  You can disable this option by setting the parameter to OFF, but the replay will likely yield significant replay divergence. However, this may be desirable if the workload consists primarily of independent transactions, and divergence during unsynchronized replay is acceptable.

• The connect_time_scale parameter scales the elapsed time from when the workload capture started to when the session connects with the specified value and is interpreted as a % value. Use this parameter to increase or decrease the number of concurrent users during replay. The default value is 100.

• The think_time_scale parameter scales the elapsed time between two successive user calls from the same session and is interpreted as a % value. Setting this parameter to 0 will send user calls to the database as fast as possible during replay. The default value is 100.

• The think_time_auto_correct parameter corrects the think time (based on the think_time_scale parameter) between calls when user calls take longer to complete during replay than during capture. This parameter can be set to either TRUE or FALSE. Setting this parameter to TRUE reduces the think time if the workload replay is taking longer than the workload capture. The default value is TRUE.

• The scale_up_multiplier parameter defines the number of times the workload is scaled up during replay. Each captured session will be replayed concurrently for as many times as specified by this parameter. However, only one session in each set of identical replay sessions will execute both queries and updates. The rest of the sessions will only execute queries.

• The capture_sts parameter specifies whether to capture a SQL tuning set in parallel with the workload replay. If this parameter is set to TRUE, you can capture a SQL tuning set during workload replay and use SQL Performance Analyzer to compare it to another SQL tuning set without having to re-execute the SQL statements. This enables you to obtain a SQL Performance Analyzer report and compare the SQL performance—before and after the change—while running Database Replay. You can also export the resulting SQL tuning set with its AWR data using the EXPORT_AWR procedure, as described in "Exporting AWR Data for Workload Replay".

  This feature is not supported for Oracle RAC. Workload replay filters that are defined using DBMS_WORKLOAD_REPLAY do not apply to the SQL tuning set capture. The default value for this parameter is FALSE.

• The sts_cap_interval parameter specifies the duration of the SQL tuning set capture from the cursor cache in seconds. The default value is 300. Setting the value of this parameter below the default value may cause additional overhead with some workloads and is not recommended.

For more information about setting these parameters, see "Specifying Replay Options".

11.6.4 Defining Workload Replay Filters and Replay Filter Sets

This section describes how to add and remove workload replay filters, and how to create and use replay filter sets. For information about using workload filters and replay filter sets with workload replay, see "Using Filters with Workload Replay".

This section contains the following topics:

• Adding Workload Replay Filters

• Deleting Workload Replay Filters

• Creating a Replay Filter Set

• Using a Replay Filter Set

BEGIN
  DBMS_WORKLOAD_REPLAY.ADD_FILTER (
                           fname => 'user_ichan',
                           fattribute => 'USER',
                           fvalue => 'ICHAN');
END;
/

BEGIN
  DBMS_WORKLOAD_REPLAY.DELETE_FILTER (fname => 'user_ichan');
END;
/

BEGIN
  DBMS_WORKLOAD_REPLAY.CREATE_FILTER_SET (
                           replay_dir => 'apr09',
                           filter_set => 'replayfilters',
                           default_action => 'INCLUDE');
END;
/

BEGIN
  DBMS_WORKLOAD_REPLAY.USE_FILTER_SET (filter_set => 'replayfilters');
END;
/

11.6.5 Setting the Replay Timeout Action

This section describes how to set a timeout action for the workload replay. You can set a replay timeout action to abort user calls that are significantly slower during replay or cause a replay to hang. For example, you may want to set a replay timeout action to abort runaway queries caused by sub-optimal execution plans following a database upgrade.

When a replay timeout action is enabled, a user call will exit with an ORA-15569 error if it is delayed beyond the conditions specified by the replay timeout action. The aborted call and its error are reported as error divergence.

To set a replay timeout, use the SET_REPLAY_TIMEOUT procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.SET_REPLAY_TIMEOUT (
                           enabled => TRUE,
                           min_delay => 20,
                           max_delay => 60,
                           delay_factor => 10);
END;
/

In this example, the SET_REPLAY_TIMEOUT procedure defines a replay timeout action that will abort a user call if the delay during replay is more than 60 minutes, or if the delay during replay is over 20 minutes and the elapsed time is 10 times greater than the capture elapsed time.

The SET_REPLAY_TIMEOUT procedure in this example uses the following parameters:

• The enabled parameter specifies if the replay timeout action is enabled or disabled. The default value is TRUE.

• The min_delay parameter defines the lower bound value of call delay in minutes. The replay timeout action is only activated when the delay is over this value. The default value is 10.

• The max_delay parameter defines the upper bound value of call delay in minutes. The replay timeout action is activated and issues an error when the delay is over this value. The default value is 120.

• The delay_factor parameter defines a factor for the call delays that are between the values of min_delay and max_delay. The replay timeout action issues an error when the current replay elapsed time is higher than the multiplication of the capture elapsed time and this value. The default value is 8.

To retrieve the replay timeout action setting, use the GET_REPLAY_TIMEOUT procedure:

DECLARE
  enabled        BOOLEAN;
  min_delay      NUMBER;
  max_delay      NUMBER;
  delay_factor   NUMBER;
BEGIN
  DBMS_WORKLOAD_REPLAY.GET_REPLAY_TIMEOUT(enabled, min_delay, max_delay,
                           delay_factor);
END;
/

The GET_REPLAY_TIMEOUT procedure in this example returns the following parameters:

• The enabled parameter returns whether the replay timeout action is enabled or disabled.

• The min_delay parameter returns the lower bound value of call delay in minutes.

• The max_delay parameter returns the upper bound value of call delay in minutes.

• The delay_factor parameter returns the delay factor.

11.6.6 Starting a Workload Replay

Before starting a workload replay, you must first:

• Preprocess the captured workload, as described in "Preprocessing a Database Workload Using APIs"

• Initialize the replay data, as described in "Initializing Replay Data"

• Specify the replay options, as described in "Setting Workload Replay Options"

• Start the replay clients, as described in "Starting Replay Clients"

To start a workload replay, use the START_REPLAY procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/

11.6.7 Pausing a Workload Replay

To pause a workload replay that is in progress, use the PAUSE_REPLAY procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.PAUSE_REPLAY ();
END;
/

Pausing a workload replay will halt all subsequent user calls issued by the replay clients until the workload replay is either resumed or cancelled. User calls that are already in progress will be allowed to complete. This option enables you to temporarily stop the replay to perform a change and observe its impact for the remainder of the replay.

11.6.8 Resuming a Workload Replay

To resume a workload replay that is paused, use the RESUME_REPLAY procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.RESUME_REPLAY ();
END;
/

11.6.9 Cancelling a Workload Replay

To cancel a workload replay, use the CANCEL_REPLAY procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
END;
/

11.6.10 Exporting AWR Data for Workload Replay

Exporting AWR data enables detailed analysis of the workload. This data is also required if you plan to run the AWR Compare Period report on a pair of workload captures or replays.

To export AWR data, use the EXPORT_AWR procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.EXPORT_AWR (replay_id => 1);
END;
/

In this example, the AWR snapshots that correspond to the workload replay with a replay ID of 1 are exported, along with any SQL tuning set that may have been captured during workload replay. The EXPORT_AWR procedure uses the replay_id required parameter, which specifies the ID of the replay whose AWR snapshots will be exported. This procedure will work only if the corresponding workload replay was performed in the current database and the AWR snapshots that correspond to the original replay time period are still available.

11.7.1 Retrieving Information About Diverged Calls

During replay, any error and data discrepancies between the replay system and the capture system are recorded as diverged calls.

To retrieve information about a diverged call—including its SQL identifier, SQL text, and bind values—call the GET_DIVERGING_STATEMENT function using the following parameters:

• Set the replay_id parameter to the ID of the replay in which the call diverged

• Set the stream_id parameter to the stream ID of the diverged call

• Set the call_counter parameter to the call counter of the diverged call

To view these information about a diverged call, use the DBA_WORKLOAD_REPLAY_DIVERGENCE view. The following example illustrates a function call:

DECLARE
  r                   CLOB;
  ls_stream_id        NUMBER;
  ls_call_counter     NUMBER;
  ls_sql_cd           VARCHAR2(20);
  ls_sql_err          VARCHAR2(512);
  CURSOR c IS
  SELECT stream_id, call_counter
  FROM DBA_WORKLOAD_REPLAY_DIVERGENCE
  WHERE replay_id = 72;
BEGIN
  OPEN c;
  LOOP
  FETCH c INTO ls_stream_id, ls_call_counter;
  EXIT when c%notfound;
  DBMS_OUTPUT.PUT_LINE (ls_stream_id||''||ls_call_counter);
  r:=DBMS_WORKLOAD_REPLAY.GET_DIVERGING_STATEMENT(replay_id => 72,
  stream_id => ls_stream_id, call_counter => ls_call_counter);
  DBMS_OUTPUT.PUT_LINE (r);
  END LOOP;
END;
/

11.7.2 Monitoring Workload Replay Using Views

This section summarizes the views that you can display to monitor workload replay. You need DBA privileges to access these views.

• The DBA_WORKLOAD_CAPTURES view lists all the workload captures that have been captured in the current database.

• The DBA_WORKLOAD_FILTERS view lists all workload filters for workload captures defined in the current database.

• The DBA_WORKLOAD_REPLAYS view lists all the workload replays that have been replayed in the current database.

• The DBA_WORKLOAD_REPLAY_DIVERGENCE view enables you to view information about diverged calls, such as the replay identifier, stream identifier, and call counter.

• The DBA_WORKLOAD_REPLAY_FILTER_SET view lists all workload filters for workload replays defined in the current database.

• The DBA_WORKLOAD_CONNECTION_MAP view lists the connection mapping information for workload replay.

• The V$WORKLOAD_REPLAY_THREAD view lists information about all sessions from the replay clients.