Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E16760-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

159 DBMS_WORKLOAD_REPLAY

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:


Using DBMS_WORKLOAD_REPLAY


Security Model

The following code describes the minimum set of privileges required to

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


Summary of DBMS_WORKLOAD_REPLAY Subprograms

This table list the package subprograms in alphabetical order.

Table 159-1 DBMS_WORKLOAD_REPLAY Package Subprograms

Subprogram Description

ADD_FILTER Procedure

Adds a filter to replay only a subset of the captured workload

CALIBRATE 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

CANCEL_REPLAY Procedure

Cancels the workload replay in progress

COMPARE_PERIOD_REPORT Procedure

Generates a report comparing a replay to its capture or to another replay of the same capture

COMPARE_SQLSET_REPORT Function

Generates a report comparing a sqlset captured during replay to one captured during workload capture or to one captured during another replay of the same capture

CREATE_FILTER_SET Procedure

Uses the replay filters added to create a set of filters to use against the replay in replay_dir

DELETE_FILTER Procedure

Deletes the named filter

DELETE_REPLAY_INFO Procedure

Deletes the rows in DBA_WORKLOAD_REPLAYS that corresponds to the given workload replay ID

EXPORT_AWR Procedure

Exports the AWR snapshots associated with a given replay ID

GET_DIVERGING_STATEMENT Function

Exports the AWR snapshots associated with a given replay ID

GET_REPLAY_INFO Function

Retrieves information about the workload capture and the history of all the workload replay attempts from the related directory

GET_REPLAY_TIMEOUT Procedure

Retrieves the replay timeout setting

IMPORT_AWR Function

Imports the AWR snapshots associated with a given replay ID

INITIALIZE_REPLAY Procedure

Initializes replay, and loads specific data produced during processing into the database

IS_REPLAY_PAUSED Function

Reports whether the replay is currently paused

PAUSE_REPLAY Procedure

Pauses the in-progress workload replay

POPULATE_DIVERGENCE 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

PREPARE_REPLAY Procedure

Puts the RDBMS in a special "Prepare" mode

PROCESS_CAPTURE Procedure

Processes the workload capture found in capture_dir in place

REMAP_CONNECTION 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

REPORT Function

Generates a report on the given workload replay

RESUME_REPLAY Procedure

Resumes a paused workload replay

REUSE_REPLAY_FILTER_SET Procedure

Reuses filters in the specified filter set as if each were added using the ADD_FILTER Procedure

REUSE_REPLAY_FILTER_SET Procedure

Sets an advanced parameter for replay besides the ones used with the PREPARE_REPLAY Procedure

SET_REPLAY_TIMEOUT Procedure

Sets the replay timeout setting

START_REPLAY Procedure

Starts the workload replay

USE_FILTER_SET Procedure

Uses the given filter set that has been created by calling the CREATE_FILTER_SET Procedure to filter the current replay



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.

Syntax

DBMS_WORKLOAD_REPLAY.ADD_FILTER (
   fname          IN VARCHAR2,
   fattribute     IN VARCHAR2,
   fvalue         IN VARCHAR2);

DBMS_WORKLOAD_REPLAY.ADD_FILTER (
   fname          IN VARCHAR2,
   fattribute     IN VARCHAR2,
   fvalue         IN NUMBER);

Parameters

Table 159-2 ADD_FILTER Procedure Parameters

Parameter Description

fname

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

fattribute

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

  • USER - type STRING

  • MODULE - type STRING

  • ACTION - type STRING

  • PROGRAM - type STRING

  • SERVICE - type STRING

  • CONNECTION_STRING - type STRING

fvalue

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. (Mandatory)



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.

Syntax

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;

Parameters

Table 159-3 CALIBRATE Function Parameters

Parameter Description

capture_dir

Name of the directory object that points to the (case sensitive) OS directory that contains processed capture data

process_per_cpu

Maximum number of process allowed per CPU (default is 4)

threads_per_process

Maximum number of threads allowed per process (default is 50)


Return Values

Returns a CLOB formatted as XML, that contains:

Usage Notes


CANCEL_REPLAY Procedure

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.

Syntax

DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY (
   error_msg    IN VARCHAR2 DEFAULT NULL);

Parameters

Table 159-4 CANCEL_REPLAY Procedure Parameters

Parameter Description

error_msg

An optional reason for cancelling the replay can be passed which will be recorded into DBA_WORKLOAD_REPLAYS.ERROR_MESSAGE. DEFAULT is NULL


Usage Notes

Prerequisite: A call to the INITIALIZE_REPLAY Procedure, or PREPARE_REPLAY Procedure, or START_REPLAY Procedure was already issued.


COMPARE_PERIOD_REPORT Procedure

This procedure generates a report comparing a replay to its capture or to another replay of the same capture.

Syntax

DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT (
   replay_id1   IN   NUMBER,
   replay_id2   IN   NUMBER,
   format       IN   VARCHAR2,
   result       OUT  CLOB );

Parameters

Table 159-5 COMPARE_PERIOD_REPORT Procedure Parameters

Parameter Description

replay_id1

First ID of the workload replay whose report is requested

replay_id2

Second ID of the workload replay whose report is requested. If this is NULL, then the comparison is done with the capture.

format

Specifies the report format. Valid values are DBMS_WORKLOAD_CAPTURE.TYPE_HTML and DBMS_WORKLOAD_CAPTURE.TYPE_XML.

result

Output of the report (CLOB)



COMPARE_SQLSET_REPORT Function

This procedure generates a report comparing a sqlset captured during replay to one captured during workload capture or to one captured during another replay of the same capture.

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

Syntax

DBMS_WORKLOAD_REPLAY.COMPARE_SQLSET_REPORT (
   replay_id1    IN NUMBER,
   replay_id2    IN NUMBER,
   format        IN VARCHAR2,
   r_level       IN VARCHAR2 := 'ALL',
   r_sections    IN VARCHAR2 := 'ALL',
   result        OUT CLOB )
  RETURN VARCHAR2;

Parameters

Table 159-6 COMPARE_SQLSET_REPORT Function Parameters

Parameter Description

replay_id1

First ID of the workload replay after a change

replay_id2

Second ID of the workload replay before a change. If this is NULL, then the comparison is done with the capture.

format

Specifies the report format. Valid values are DBMS_WORKLOAD_CAPTURE.TYPE_HTML, DBMS_WORKLOAD_CAPTURE.TYPE_XML and DBMS_WORKLOAD_CAPTURE.TYPE_TEXT.

r_level

See level parameter in the REPORT_ANALYSIS_TASK Function in the DBMS_SQLPA package

r_sections

See section parameter in the REPORT_ANALYSIS_TASK Function in the DBMS_SQLPA package

result

Output of the report (CLOB)



CREATE_FILTER_SET Procedure

This procedure uses the replay filters added since the previous successful call to create a set of filters to use against the replay in replay_dir. After the procedure has completed and replay initiated, the newly-creaed filter set can be used to filter the replay in replay_dir by calling the USE_FILTER_SET Procedure .

Syntax

DBMS_WORKLOAD_REPLAY.CREATE_FILTER_SET(
   replay_dir       IN  VARCHAR2,
   filter_set       IN  VARCHAR2,
   default_action   IN  VARCHAR2 DEFAULT 'INCLUDE'); 

Parameters

Table 159-7 CREATE_FILTER_SET Procedure Parameters

Parameter Description

replay_dir

Object directory of the replay to be filtered

filter_set

Name of the filter set to create (to use in USE_FILTER_SET Procedure)

default_action

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 will be replayed, except for the part of the workload defined by the filters. In this case, all the filters that were specified using the ADD_FILTER Procedure will be 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 will be replayed, except for the part of the workload defined by the filters. In this case, all the filters that were specified using he ADD_FILTER Procedure will be treated as INCLUSION filters, and will determine the workload that will be replayed.

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


Usage Notes

This operation needs to be done 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 159-8 DELETE_FILTER Procedure Parameters

Parameter Description

fname

Name of the filter that must be deleted (Mandatory)



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 159-9 DELETE_REPLAY_INFO Procedure Parameters

Parameter Description

replay_id

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



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 159-10 EXPORT_AWR Function Parameters

Parameter Description

replay_id

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


Usage Notes


GET_DIVERGING_STATEMENT Function

This function retrieves information about a diverging call, including the statement text, the SQL ID, and the binds.

Syntax

DBMS_WORKLOAD_REPLAY.GET_DIVERGING_STATEMENT (
   replay_id    IN NUMBER,
   stream_id    IN NUMBER,
   call_counter IN NUMBER)
  RETURN CLOB;

Parameters

Table 159-11 GET_DIVERGING_STATEMENT Function Parameters

Parameter Description

replay_id

ID of the replay in which that call diverged

stream_id

Stream ID of the diverging call

call_counter

Call counter of the diverging call


Usage Notes


GET_REPLAY_INFO Function

This function retrieves information about the workload capture and the history of all the workload replay attempts from the stipulated directory.

Syntax

DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO (
   dir    IN VARCHAR2)
 RETURN NUMBER;

Parameters

Table 159-12 GET_REPLAY_INFO Function Parameters

Parameter Description

dir

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


Return Values

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.

Usage Notes


GET_REPLAY_TIMEOUT Procedure

This procedure gets the replay timeout setting.

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

Syntax

DBMS_WORKLOAD_REPLAY.GET_REPLAY_TIMEOUT (
   enabled       OUT  BOOLEAN, 
   min_delay     OUT  NUMBER,
   max_delay     OUT  NUMBER,
   delay_factor  OUT  NUMBER);

Parameters

Table 159-13 GET_REPLAY_TIMEOUT Function Parameters

Parameter Description

enabled

TRUE if the timeout action is enabled, FALSE otherwise.

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.

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.


Usage Notes

This procedure can be called anytime during replay.


IMPORT_AWR Function

This procedure imports the AWR snapshots from a given replay.

Syntax

DBMS_WORKLOAD_REPLAY.IMPORT_AWR (
   replay_id       IN   NUMBER,
   staging_schema  IN   VARCHAR2)
  RETURN NUMBER;

Parameters

Table 159-14 IMPORT_AWR Function Parameters

Parameter Description

replay_id

ID of the replay whose AWR snapshots must be exported. (Mandatory)

staging_schema

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


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


INITIALIZE_REPLAY Procedure

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

Syntax

DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (
   replay_name     IN  VARCHAR2,
   replay_dir      IN  VARCHAR2);

Parameters

Table 159-15 INITIALIZE_REPLAY Procedure Parameters

Parameter Description

replay_name

Name of the workload replay. Every replay of a processed workload capture can be given a name. (Mandatory)

replay_dir

Name of the directory object that points to the OS directory (case sensitive) that contains processed capture data


Usage Notes


IS_REPLAY_PAUSED Function

This function reports whether the replay is currently paused.

Syntax

DBMS_WORKLOAD_REPLAY.IS_REPLAY_PAUSED
   RETURN BOOLEAN;

Return Values

Returns TRUE if the PAUSE_REPLAY Procedure has been called successfully and the RESUME_REPLAY Procedure has not been called yet.

Usage Notes

A call to the START_REPLAY Procedure must have already been issued as a pre-requisite.


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.

Syntax

DBMS_WORKLOAD_REPLAY.PAUSE_REPLAY;

Usage Notes


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 159-16 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 will be precomputed for all diverging calls in the given replay.

call_counter

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



PREPARE_REPLAY Procedure

This procedure puts the database state in PREPARE FOR REPLAY mode.

Note:

The functionality associated with the capture_sts and sts_cap_interval parameters is available starting with Oracle Database 11g Release 2 (11.2.0.2).

Syntax

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,
   scale_up_multiplier       IN NUMBER    DEFAULT 1,
   capture_sts               IN BOOLEAN   DEFAULT FALSE,
   sts_cap_interval          IN NUMBER    DEFAULT 300);

DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (
   synchronization           IN VARCHAR2  DEFAULT 'SCN',,
   connect_time_scale        IN NUMBER    DEFAULT 100,
   think_time_scale          IN NUMBER    DEFAULT 100,
   think_time_auto_correct   IN BOOLEAN   DEFAULT TRUE,
   scale_up_multiplier       IN NUMBER    DEFAULT 1,
   capture_sts               IN BOOLEAN   DEFAULT FALSE,
   sts_cap_interval          IN NUMBER    DEFAULT 300);

Parameters

Table 159-17 PREPARE_REPLAY Procedure Parameters

Parameter Description

synchronization

Turns synchronization ON or OFF during workload replay. When synchronization is ON, the COMMIT order observed during the original workload capture will be preserved during replay. Every action that is replayed will be 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. DEFAULT is TRUE which preserves commit order.

When synchronization is OBJECT_ID, a more advanced synchronization scheme is used. Every action that is replayed will be 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 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. DEFAULT VALUE 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. DEFAULT VALUE is 100. See Application of the think_time_scale Parameter.

think_time_auto_correct

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

Examples

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

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);

Parameters

Table 159-18 PROCESS_CAPTURE Procedure Parameters

Parameter Description

catpure_dir

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)


Usage Notes


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.

Syntax

DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (
   connection_id         IN  NUMBER,
   replay_connection     IN  VARCHAR2);

Parameters

Table 159-19 REMAP_CONNECTION Procedure Parameters

Parameter Description

connection_id

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


REPORT Function

This function generates a report on the stipulated workload replay.

Syntax

DBMS_WORKLOAD_REPLAY.REPORT (
   replay_id          IN NUMBER,
   format             IN VARCHAR2 )
  RETURN CLOB;

Parameters

Table 159-20 REPORT Function Parameters

Parameter Description

replay_id

Specifies the ID of the workload replay whose report is requested. (Mandatory)

format

Specifies the report format. Valid values are XML, HTML, or TEXT. (Mandatory)


Return Values

The report body in the desired format returned as a CLOB

Table 159-21 Constants Used by Report Function

Constant Type Value Description

TYPE_HTML

VARCHAR2(4)

'HTML'

Generates the HTML version of the report

TYPE_TEXT

VARCHAR2(4)

'TEXT'

Use this as input to the format argument to generate the text version of the report.

TYPE_XML

VARCHAR2(3)

'XML'

Generates the XML version of the report



RESUME_REPLAY Procedure

This procedure resumes a paused workload replay.

Syntax

DBMS_WORKLOAD_REPLAY.RESUME_REPLAY;

Usage Notes

Prerequisite: A call to the PAUSE_REPLAY Procedure must have already been issued.


REUSE_REPLAY_FILTER_SET Procedure

This procedure reuses filters in the specified filter set as if each were added using the ADD_FILTER Procedure. 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 existing filter can be deleted before invoking the CREATE_FILTER_SET Procedure to create a new filter set.

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

Syntax

DBMS_WORKLOAD_REPLAY.REUSE_REPLAY_FILTER_SET( 
   replay_dir  IN VARCHAR2,
   filter_set  IN VARCHAR2);

Parameters

Table 159-22 REUSE_REPLAY_FILTER_SET Procedure Parameters

Parameter Description

replay_dir

Capture ID of the existing filter set with which it is associated

filter_set

Name of the filter set to be reused



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.

Syntax

DBMS_WORKLOAD_REPLAY.SET_ADVANCED_PARAMETER( 
   pname    IN   VARCHAR2,
   pvalue   IN   VARCHAR2);

DBMS_WORKLOAD_REPLAY.SET_ADVANCED_PARAMETER( 
   pname    IN   VARCHAR2,
   pvalue   IN   NUMBER);

DBMS_WORKLOAD_REPLAY.SET_ADVANCED_PARAMETER( 
   pname    IN   VARCHAR2,
   pvalue   IN   BOOLEAN);

Parameters

Table 159-23 SET_ADVANCED_PARAMETER Procedure Parameters

Parameter Description

pname

Name of the parameter (case insensitive)

pvalue

Value of the parameter


Usage Notes

The current parameters and values that can be used are:

'DO_NO_WAIT_COMMITS': (default: FALSE)

This parameter controls whether the COMMITs issued by replay sessions will be 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_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.

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

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 159-24 SET_REPLAY_TIMEOUT Function Parameters

Parameter Description

enabled

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

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


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.

Syntax

DBMS_WORKLOAD_REPLAY.START_REPLAY;

Usage Notes


USE_FILTER_SET Procedure

This procedure uses the given filter set that has been created by calling the CREATE_FILTER_SET Procedure to filter the current replay.

Syntax

DBMS_WORKLOAD_REPLAY.USE_FILTER_SET( 
   filter_set     IN VARCHAR2);

Parameters

Table 159-25 USE_FILTER_SET Procedure Parameters

Parameter Description

filter_set

Name of the filter set use in this replay


Usage Notes

This procedure must be called after the replay has been initialized and before it is prepared.