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

Part Number B28419-01
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

145 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 Performance Tuning Guide for more information about "Database Replay"

This chapter contains the following topics:


Using DBMS_WORKLOAD_REPLAY


Security Model

Use of the package is restricted to users with DBA role or EXECUTE_CATALOG_ROLE.

Additionally, the user of the package must have access to the host directory that contains the workload capture files, and one or more hosts that has the Workload Replay Client installed and can be used to drive the captured workload during a database replay.


Summary of DBMS_WORKLOAD_REPLAY Subprograms

This table list the package subprograms in alphabetical order.

Table 145-1 DBMS_WORKLOAD_REPLAY Package Subprograms

Subprogram Description
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
CALIBRATE Function
Cancels the workload replay in progress
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_REPLAY_INFO Function
Retrieves information about the workload capture and the history of all the workload replay attempts from the related directory
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
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
START_REPLAY Procedure
Starts the workload replay


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 145-2 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 145-3 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.


DELETE_REPLAY_INFO Procedure

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

Syntax

DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO (
   replay_id    IN  NUMBER);

Parameters

Table 145-4 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 145-5 EXPORT_AWR Function Parameters

Parameter Description
replay_id ID of the replay whose AWR snapshots should be exported. (Mandatory)

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 145-6 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


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 145-7 IMPORT_AWR Function Parameters

Parameter Description
replay_id ID of the replay whose AWR snapshots should 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 PREPARE_REPLAY Procedure).

Syntax

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

Parameters

Table 145-8 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


PREPARE_REPLAY Procedure

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

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

Parameters

Table 145-9 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 it's 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.
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

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 145-10 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 145-11 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 145-12 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 145-13 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


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