Oracle | DBMS_WORKLOAD

Oracle DBMS_WORKLOAD_REPLAY Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides an interface to replay a workload capture that was originally created with DBMS_WORKLOAD_CAPTURE.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Report Constants
TYPE_HTML	VARCHAR2(4)	'HTML'
TYPE_TEXT	VARCHAR2(4)	'TEXT'
TYPE_XML	VARCHAR2(3)	'XML'
TYPE_XML_CC	VARCHAR2(6)	'XML_CC'
Client Connect Constants
KECP_CLIENT_CONNECT_LOGIN	NUMBER	1
KECP_CLIENT_CONNECT_ADMIN	NUMBER	2
KECP_CLIENT_CONNECT_GOODBYE	NUMBER	3
KECP_CLIENT_CONNECT_THRDFAIL	NUMBER	4
KECP_CLIENT_CONNECT_CHKPPID	NUMBER	5
KECP_CLIENT_CONNECT_CLOCK_TICK	NUMBER	6
KECP_CLIENT_CONNECT_CHK_VSN	NUMBER	7
KECP_CMD_END_OF_REPLAY	NUMBER	1
KECP_CMD_REPLAY_CANCELLED	NUMBER	2

Data Types TYPE uc_graph_record IS RECORD(time NUMBER, user_calls NUMBER, flags NUMBER); / TYPE uc_graph_table IS TABLE OF uc_graph_record; /

Dependencies

AS_REPLAY	DBMS_REPLAY_HUB	DBMS_WRR_PROTECTED
DBA_WORKLOAD_REPLAYS	DBMS_STANDARD	GV$SYSMETRIC_HISTORY
DBA_WORKLOAD_REPLAY_DIVERGENCE	DBMS_WORKLOAD_REPLAY_I	WRH$_SYSMETRIC_HISTORY
DBMS_RAT_MASK_INTERNAL	DBMS_WRR_INTERNAL	WRR$_REPLAY_UC_GRAPH

Documented Yes

Exceptions

Error Code	Reason
ORA-20222	Workload capture in <directory_name> is missing required .wmd files.
ORA-20223	No initialized, prepared, or ongoing replay

First Available 11.1

Security Model Owned by SYS with EXECUTE granted to the DBA and EXECUTE_CATALOG_ROLE roles

Source {ORACLE_HOME}/rdbms/admin/dbmswrr.sql

Subprograms

ADD_CAPTURE	GET_CAPTURED_TABLES	PROCESS_CAPTURE
ADD_FILTER	GET_CLOCK	PROCESS_CAPTURE_COMPLETION
ADD_SCHEDULE_ORDERING	GET_DIVERGING_STATEMENT	PROCESS_CAPTURE_REMAINING_TIME
ADJUST_TIMES_TO_SNAP_TIMEZONE	GET_PERF_DATA_EXPORT_STATUS	PROCESS_REPLAY_GRAPH
ASSIGN_GROUP_TO_INSTANCE	GET_PROCESSING_PATH	REMAP_CONNECTION
BEGIN_REPLAY_SCHEDULE	GET_REPLAY_DIRECTORY	REMOVE_CAPTURE
CALIBRATE	GET_REPLAY_INFO	REMOVE_REPLAY_SCHEDULE
CANCEL_REPLAY	GET_REPLAY_PATH	REMOVE_SCHEDULE_ORDERING
CLIENT_CONNECT	GET_REPLAY_TIMEOUT	REPORT
CLIENT_GET_REPLAY_SUBDIR	GROUP_WORKLOAD	RESET_ADVANCED_PARAMETERS
CLIENT_VITALS	IMPORT_AWR	RESUME_REPLAY
COMPARE_PERIOD_REPORT	IMPORT_PERFORMANCE_DATA	REUSE_REPLAY_FILTER_SET
COMPARE_SQLSET_REPORT	IMPORT_UC_GRAPH	SET_ADVANCED_PARAMETER
CONFIGURE	INITIALIZE_CONSOLIDATED_REPLAY	SET_ATTRIBUTE
CREATE_FILTER_SET	INITIALIZE_REPLAY	SET_CONSOLIDATED_DIRECTORY
DELETE_ATTRIBUTE	INITIALIZE_REPLAY_INTERNAL	SET_REPLAY_DIRECTORY
DELETE_FILTER	IS_REPLAY_PAUSED	SET_REPLAY_TIMEOUT
DELETE_REPLAY_INFO	LOAD_DIVERGENCE	SET_SQL_MAPPING
DIVERGING_STATEMENT_STATUS	LOAD_LONG_SQLTEXT	SET_USER_MAPPING
END_REPLAY_SCHEDULE	LOAD_TRACKED_COMMITS	START_CONSOLIDATED_REPLAY
EXPORT_AWR	PAUSE_REPLAY	START_REPLAY
EXPORT_PERFORMANCE_DATA	PERSIST_ATTRIBUTES	STOP_STS_C
EXPORT_UC_GRAPH	POPULATE_DIVERGENCE	SYNC_ATTRIBUTES_FROM_FILE
GENERATE_CAPTURE_SUBSET	POPULATE_DIVERGENCE_STATUS	USER_CALLS_GRAPH
GET_ADVANCED_PARAMETER	PREPARE_CONSOLIDATED_REPLAY	USE_FILTER_SET
GET_ATTRIBUTE	PREPARE_REPLAY	WORKLOAD_INTELLIGENCE_REPORT

ADD_CAPTURE

Add the given capture to the current schedule. This overload is designed for PL/SQL usage.

Overload 1 dbms_workload_replay.add_capture( capture_dir_name IN VARCHAR2, start_delay_seconds IN NUMBER, stop_replay IN BOOLEAN, take_begin_snapshot IN BOOLEAN DEFAULT FALSE, take_end_snapshot IN BOOLEAN DEFAULT FALSE, query_only IN BOOLEAN DEFAULT FALSE) RETURN NUMBER;

TBD

Allow adding a capture to the current schedule. This overload is designed for SQL usage.

Overload 2 dbms_workload_replay.add_capture( capture_dir_name IN VARCHAR2, start_delay_seconds IN NUMBER DEFAULT 0, stop_replay IN VARCHAR2 DEFAULT 'N', take_begin_snapshot IN VARCHAR2 DEFAULT 'N', take_end_snapshot IN VARCHAR2 DEFAULT 'N', query_only IN VARCHAR2 DEFAULT 'N') RETURN NUMBER;

TBD

ADD_FILTER

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

Overload 1 dbms_workload_replay.add_filter( fname IN VARCHAR2, fattribute IN VARCHAR2, fvalue IN VARCHAR2);

TBD

Overload 2 dbms_workload_replay.add_filter( fname IN VARCHAR2, fattribute IN VARCHAR2, fvalue IN NUMBER);

TBD

ADD_SCHEDULE_ORDERING

Add a wait-for dependency between two captures in the replay schedule dbms_workload_replay.add_schedule_ordering( schedule_capture_id IN NUMBER, waitfor_capture_id IN NUMBER);

exec dbms_workload_replay.add_schedule_ordering(6, 9);

ADJUST_TIMES_TO_SNAP_TIMEZONE

Adjust dbtimezone based start and end time using the timezone offset recorded by AWR (internal use only) dbms_workload_replay.adjust_times_to_snap_timezone( btime IN OUT DATE, awrbsnap IN NUMBER, etime IN OUT DATE, awresnap IN NUMBER, dbid IN NUMBER);

TBD

ASSIGN_GROUP_TO_INSTANCE

Assigns a group of capture files to be processed by a particular node in a RAC cluster dbms_workload_replay.assign_group_to_instance( group_id IN NUMBER, instance_number IN NUMBER);

exec dbms_workload_replay.assign_group_to_instance(6, 2);

BEGIN_REPLAY_SCHEDULE

Initiate the creation of a reusable replay schedule dbms_workload_replay.begin_replay_schedule(schedule_name IN VARCHAR2);

exec dbms_workload_replay.begin_replay_schedule('UWReplaySched');

CALIBRATE

Compute the estimated number of replay clients and CPU needed to replay a given workload dbms_workload_replay.calibrate( capture_dir IN VARCHAR2, process_per_cpu IN BINARY_INTEGER DEFAULT 4, threads_per_process IN BINARY_INTEGER DEFAULT 100) RETURN CLOB;

set long 1000000

      set serveroutput on

      

      DECLARE

       c CLOB;

      BEGIN

        c := dbms_workload_replay.calibrate('CTEMP');

        dbms_output.put_line(c);

      END;

      /

CANCEL_REPLAY

Cancels the workload replay in progress dbms_workload_replay.cancel_replay(reason IN VARCHAR2 DEFAULT NULL);

desc dba_workload_replays

      

      SELECT name, error_message

      FROM dba_workload_replays;

      

      exec dbms_workload_replay.cancel_replay('Just Because');

      

      SELECT name, error_message

      FROM dba_workload_replays;

CLIENT_CONNECT

Private function used internally: Undocumented dbms_workload_replay.client_connect( who IN NUMBER, arg IN NUMBER DEFAULT 0) RETURN NUMBER;

TBD

CLIENT_GET_REPLAY_SUBDIR

Undocumented dbms_workload_replay.client_get_replay_subdir( replay_subdir OUT VARCHAR2, sched_cap_id OUT VARCHAR2);

DECLARE

       rsubdir VARCHAR2(60);

       cap_id  VARCHAR2(60);

      BEGIN

        dbms_workload_replay.client_get_replay_subdir(rsubdir, cap_id);

        dbms_output.put_line(rsubdir);

        dbms_output.put_line(cap_id);

      END;

      /

CLIENT_VITALS

Private function used internally: Undocumented dbms_workload_replay.client_vitals( id IN NUMBER, name IN VARCHAR2, value IN NUMBER);

TBD

COMPARE_PERIOD_REPORT

Generates a report comparing a replay to its capture or to another replay of the same capture dbms_workload_replay.compare_period_report( replay_id1 IN NUMBER, replay_id2 IN NUMBER, format IN VARCHAR2, result OUT CLOB);

TBD

COMPARE_SQLSET_REPORT

Generates a report comparing a sqlset captured during workload capture with one captured during a replay of the same capture 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;

TBD

CONFIGURE (new 21c)

Set the duration limit for replay.

When the limit is reached, the replay will be stopped automatically (this may actually happen up to one minute later). dbms_workload_replay.configure(duration_limit IN INTEGER); PRAGMA SUPPLEMENTAL_LOG_DATA( configure, NONE );

exec dbms_workload_replay.configure(10);

      BEGIN dbms_workload_replay.configure(10); END;

      *

      ERROR at line 1:

      ORA-20223: no initialized, prepared, or ongoing replay

      ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY_I", line 7147

      ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 168

      ORA-06512: at line 1

CREATE_FILTER_SET

Uses all the replay filters that have been added (since the previous succesful call to CREATE_FILTER_SET) to create a set of filters to use against the replay in 'replay_dir'. This operation needs to be done when no replay is initialized, prepared or in progress. dbms_workload_replay.create_filter_set( replay_dir IN VARCHAR2, filter_set IN VARCHAR2, default_action IN VARCHAR2 DEFAULT 'INCLUDE');

TBD

DELETE_ATTRIBUTE

An internal procedure for deleting Enterprise Manager replay attributes dbms_workload_replay.delete_attribute( capture_id IN NUMBER, replay_id IN NUMBER, name IN VARCHAR2);

TBD

DELETE_FILTER

Delete the named filter dbms_workload_replay.delete_filter(fname IN VARCHAR2);

TBD

DELETE_REPLAY_INFO

Deletes the rows in DBA_WORKLOAD_REPLAYS that corresponds to the given workload replay id dbms_workload_replay.delete_replay_info( replay_id IN NUMBER, permanent IN BOOLEAN DEFAULT FALSE);

SELECT id, name

      FROM dba_workload_replays;

      

      exec dbms_workload_replay.delete_replay_info(1);

DIVERGING_STATEMENT_STATUS

Load detailed divergence information for a single replay diverging call. Possible results are: LOADED (statement divergence data for this statement is loaded) and NOT LOADED (statement divergence data is not loaded yet). dbms_workload_replay.diverging_statement_status( replay_id IN NUMBER, stream_id IN NUMBER, call_counter IN NUMBER) RETURN VARCHAR2;

TBD

END_REPLAY_SCHEDULE

Wraps up the creation of the current schedule saving it and its replay directory dbms_workload_replay.end_replay_schedule;

exec dbms_workload_replay.end_replay_schedule;

EXPORT_AWR

Exports the AWR snapshots associated with a given replay_id dbms_workload_replay.export_awr(replay_id IN NUMBER);

SELECT id, name

      FROM dba_workload_replays;

      

      exec dbms_workload_replay.export_awr(1);

EXPORT_PERFORMANCE_DATA

Exports the AWR snapshots associated with a given capture_id as well as the SQL set that may have been captured along with the workload dbms_workload_replay.export_perofrmance_data(capture_id IN NUMBER);

exec dbms_workload_replay.export_performance_data(6);

EXPORT_UC_GRAPH

Undocumented dbms_workload_replay.export_uc_graph(replay_id IN NUMBER);

exec dbms_workload_replay.export_uc_graph(42);

GENERATE_CAPTURE_SUBSET

Creates a new capture from an existing workload capture dbms_workload_replay.generate_capture_subset( input_capture_dir IN VARCHAR2, output_capture_dir IN VARCHAR2, new_capture_name IN VARCHAR2, begin_time IN NUMBER DEFAULT 0, begin_include_incomplete IN BOOLEAN DEFAULT TRUE, end_time IN NUMBER DEFAULT 0, end_include_incomplete IN BOOLEAN DEFAULT FALSE, parallel_level IN NUMBER DEFAULT 1);

TBD

GET_ADVANCED_PARAMETER

Gets the value of an advanced parameter and returns the value as a VARCHAR2 regardless of the data type dbms_workload_replay.get_advanced_parameter(pname IN VARCHAR2) RETURN VARCHAR2;

See SET_ADVANCED_PARAMETER Demo Below

GET_ATTRIBUTE

Enterprise Manager attributes that according to the doc are for "For internal use only and subject to change in future releases" dbms_workload_replay.get_attribute( capture_id IN NUMBER, replay_id IN NUMBER, name IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_CAPTURED_TABLES

Extract from the capture files the list of Database objects that have been accessed by the captured workload on the capture system dbms_workload_replay.get_captured_tables(capture_dir IN VARCHAR2) RETURN CLOB;

SELECT dbms_workload_replay.get_captured_tables('CTEMP')

      FROM dual;

GET_CLOCK

Returns the current value of the replay clock via a kernel callout to kecpGetClock. Will fail if a replay is not running. dbms_workload_replay.get_clock RETURN NUMBER;

SELECT dbms_workload_replay.get_clock

      FROM dual;

GET_DIVERGING_STATEMENT

Get information on a diverging call, including the statement text, the SQL id and the binds dbms_workload_replay.get_diverging_statement( replay_id IN NUMBER, stream_id IN NUMBER, call_counter IN NUMBER) RETURN CLOB;

TBD

GET_PERF_DATA_EXPORT_STATUS

Populates awr_data and sts_data with the filenames of the exported performance data. If no data exists they are set to NULL. dbms_workload_replay.get_perf_data_export_status( replay_id IN NUMBER, awr_data OUT VARCHAR2, sts_data OUT VARCHAR2);

TBD

GET_PROCESSING_PATH

Returns the full path to the directory dbms_workload_replay.get_processing_path(capture_id IN NUMBER) RETURN VARCHAR2;

set serveroutput on

      

      DECLARE

       dirpath VARCHAR2(100);

      BEGIN

        dirpath := dbms_workload_replay.get_processing_path(6);

        dbms_output.put_line(dirpath);

      END;

      /

GET_REPLAY_DIRECTORY

Returns the directory object name of the current replay set by SET_REPLAY_DIRECTORY: Otherwise NULL dbms_workload_replay.get_replay_directory RETURN VARCHAR2;

SELECT dbms_workload_replay.get_replay_directory

      FROM dual;

GET_REPLAY_INFO

Looks into the given directory and retrieves information about the workload capture and the history of all the workload replay attempts dbms_workload_replay.get_replay_info( dir IN VARCHAR2 load_divergence IN BOOLEAN DEFAULT FALSE) RETURN NUMBER;

SELECT id, name, dir_path

      FROM dba_workload_replays;

      

      exec dbms_workload_replay.export_awr('CTEMP');

GET_REPLAY_PATH

Returns the full path to the directory dbms_workload_replay.get_replay_path(replay_id IN NUMBER) RETURN VARCHAR2

set serveroutput on

      

      DECLARE

       dirpath VARCHAR2(100);

      BEGIN

        dirpath := dbms_workload_replay.get_replay_path(6);

        dbms_output.put_line(dirpath);

      END;

      /

GET_REPLAY_TIMEOUT

Returns the replay timeout setting dbms_workload_replay.get_replay_timeout( enabled OUT BOOLEAN, min_delay OUT NUMBER, max_delay OUT NUMBER, delay_factor OUT NUMBER);

set serveroutput on

      

      DECLARE

       vEnabled  BOOLEAN;

       vMinDelay NUMBER;

       vMaxDelay NUMBER;

       vDelFactr NUMBER;

      BEGIN

        dbms_workload_replay.get_replay_timeout(vEnabled, vMinDelay, vMaxDelay, vDelFactr);

        IF vEnabled THEN

          dbms_output.put_line('Enabled');

        ELSE

          dbms_output.put_line('Not Enabled');

        END IF;

      END;

      /

GROUP_WORKLOAD

Finds a grouping for the workload, resolves conflicts and merges them into groups based on the number of files until the number of groups is less than the max. Results are stored in WRR$_WORKLOAD_GROUPS and WRR$_REPLAY_LOGIN_QUEUE. dbms_workload_replay.group_workload(max_groups IN NUMBER);

exec dbms_workload_replay.group_workload(10);

IMPORT_AWR

Imports the AWR snapshots from a given replay, provided those AWR snapshots were successfully exported earlier from the original replay system dbms_workload_replay.import_awr( replay_id IN NUMBER, staging_schema IN VARCHAR2, force_cleanup IN BOOLEAN DEFAULT FALSE) RETURN NUMBER;

TBD

IMPORT_PERFORMANCE_DATA

Imports the AWR snapshots from a given capture, provided those AWR snapshots were exported earlier from the original capture system using DBMS_WORKLOAD_CAPTURE.EXPORT_AWR dbms_workload_replay.import_performance_data( capture_id IN NUMBER, staging_schema IN VARCHAR2, force_cleanup IN BOOLEAN DEFAULT FALSE);

exec dbms_workload_replay.import_performance_data(6, 'UWSTAGE', TRUE);

IMPORT_UC_GRAPH

Undocumented dbms_workload_replay.import_uc_graph(replay_id IN NUMBER);

exec dbms_workload_replay.import_uc_graph(42);

INITIALIZE_CONSOLIDATED_REPLAY

Puts the DB into INIT state for a multiple-capture replay dbms_workload_replay.initialize_consolidated_replay( replay_name IN VARCHAR2, schedule_name IN VARCHAR2);

TBD

INITIALIZE_REPLAY

Puts the DB state in INIT for REPLAY mode dbms_workload_replay.initialize_replay( replay_name IN VARCHAR2, replay_dir IN VARCHAR2);

exec dbms_workload_replay.initialize_replay('UWReplay', 'CTEMP');

INITIALIZE_REPLAY_INTERNAL

Undocumented

The values for "REPLAY TYPE" are not published dbms_workload_replay.initialize_replay_internal( replay_name IN VARCHAR2, replay_dir IN VARCHAR2, replay_type IN VARCHAR2);

TBD

IS_REPLAY_PAUSED

Returns whether the replay is currenty paused. Returns TRUE if and only if PAUSE_REPLAY has been called successfully and RESUME_REPLAY has not been called yet dbms_workload_replay.is_replay_paused RETURN BOOLEAN;

set serveroutput on

      

      BEGIN

        IF dbms_workload_replay.is_replay_paused THEN

          dbms_output.put_line('Paused');

        ELSE

          dbms_output.put_line('Not Paused');

        END IF;

      END;

      /

LOAD_DIVERGENCE

Load divergence data for a given replay id
Overload 1 dbms_workload_replay.load_divergence(replay_id IN NUMBER);

exec dbms_workload_replay.load_divergence(42);

Overload 2 dbms_workload_replay.load_divergence(play_dir IN VARCHAR2);

exec dbms_workload_replay.load_divergence('CTEMP');

LOAD_LONG_SQLTEXT

Loads captured SQL statements whose length is greater than 1000 characters dbms_workload_replay.load_long_sqltext(capture_id IN NUMBER);

exec dbms_workload_replay.load_long_sqltext(11);

LOAD_TRACKED_COMMITS

Commits data for a given replay id
Overload 1 dbms_workload_replay.load_tracked_commits(replay_id IN NUMBER);

exec dbms_workload_replay.load_tracked_commits(8);

Load tracked commits data for all replays in a given directory object
Overload 2 dbms_workload_replay.load_tracked_commits(replay_dir IN VARCHAR2);

exec dbms_workload_replay.load_tracked_commits('u03/apps/oracle/replay');

PAUSE_REPLAY

Pauses a workload replay dbms_workload_replay.pause_replay;

See RESUME_REPLAY Demo Below

PERSIST_ATTRIBUTES

Persists all attributes across all captures and replays dbms_workload_replay.persist_attributes(capture_id IN NUMBER);

exec dbms_workload_replay.persist_attributes(6);

POPULATE_DIVERGENCE

Precompute the divergence information for the given call, stream or the whole replay, so that GET_DIVERGING_STATEMENT returns almost instantly for the precomputed calls dbms_workload_replay.populate_divergence( replay_id IN NUMBER, stream_id IN NUMBER DEFAULT NULL, call_counter IN NUMBER DEFAULT NULL);

TBD

POPULATE_DIVERGENCE_STATUS

Status of the divergence detailed information for the given replay

dbms_workload_replay.populate_divergence_status(replay_id IN NUMBER) RETURN VARCHAR2;

Return Values	Description
LOADED	All statement divergence information for this replay is loaded
LOADING	The RDBMS is currently undertaking a bulk load of all of the statement divergence data for the given replay
NOT LOADED	Neither of the above, i.e., not LOADING and at least 1 statement's divergence data has not been loaded

TBD

PREPARE_CONSOLIDATED_REPLAY

Puts the DB state in PREPARE mode for a multiple-capture replay dbms_workload_replay.prepare_consolidated_replay( synchronization IN BOOLEAN, connect_time_scale IN NUMBER DEFAULT 100, think_time_scale IN NUMBER DEFAULT 100, think_time_auto_correct IN BOOLEAN DEFAULT TRUE, capture_sts IN BOOLEAN DEFAULT FALSE, sts_cap_interval IN NUMBER DEFAULT 300);

TBD

Overload 2 dbms_workload_replay.prepare_consolidated_replay( synchronization IN VARCHAR2 DEFAULT 'OBJECT_ID', connect_time_scale IN NUMBER DEFAULT 100, think_time_scale IN NUMBER DEFAULT 100, think_time_auto_correct IN BOOLEAN DEFAULT TRUE, capture_sts IN BOOLEAN DEFAULT FALSE, sts_cap_interval IN NUMBER DEFAULT 300);

TBD

PREPARE_REPLAY

Puts the DB state in REPLAY mode

Overload 1 dbms_workload_replay.prepare_replay( synchronization IN BOOLEAN DEFAULT TRUE, -- FALSE = OFF 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);

exec dbms_workload_replay.prepare_replay(TRUE, 100, 100, TRUE);

Overload 2 dbms_workload_replay.prepare_replay( synchronization IN BOOLEAN 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);

TBD

PROCESS_CAPTURE

Processes the workload capture found in capture_dir dbms_workload_replay.process_capture( capture_dir IN VARCHAR2, parallel_level IN NUMBER);

exec dbms_workload_replay.process_capture('CTEMP', 2);

PROCESS_CAPTURE_COMPLETION

While a process capture is running returns the percentage of the capture files that have been processed dbms_workload_replay.process_capture_completion RETURN NUMBER;

DECLARE

       retval VARCHAR2(10);

      BEGIN

        retval := '-'||dbms_workload_replay.process_capture_completion||'-';

        dbms_output.put_line(retval);

      END;

      /

PROCESS_CAPTURE_REMAINING_TIME

While a capture process is running returns an estimate of the minutes remaining before processing is completed. Will return NULL during the first minute of capture processing or if not running. dbms_workload_replay.process_capture_remaining_time RETURN NUMBER;

DECLARE

       retval VARCHAR2(10);

      BEGIN

        retval := '-' || dbms_workload_replay.process_capture_remaining_time || '-';

        dbms_output.put_line(retval);

      END;

      /

PROCESS_REPLAY_GRAPH

Modify default degree of parallelism dbms_workload_replay.process_replay_graph(parallel_level IN NUMBER DEFAULT 1) RETURN NUMBER;

DECLARE

       retval NUMBER;

      BEGIN

        retval := dbms_workload_replay.process_replay_graph(2);

        dbms_output.put_line(retval);

      END;

      /

REMAP_CONNECTION

Remap the captured connection to a new one so that the user sessions can connect to the database in a desired way during workload replay dbms_workload_replay.remap_connection( connection_id IN NUMBER, replay_connection IN VARCHAR2);

TBD

REMOVE_CAPTURE

Remove the given capture from the current schedule dbms_workload_replay.remove_capture(schedule_capture_id IN NUMBER);

TBD

REMOVE_REPLAY_SCHEDULE

Removes an existing replay schedule. All the records about its captures and the wait-for capture orders deleted. The WMD file for the replay schedule is modified accordingly. dbms_workload_replay.remove_repay_schedule(schedule_name IN VARCHAR2);

TBD

REMOVE_SCHEDULE_ORDERING

Remove a wait-for dependency from a replay schedule dbms_workload_replay.remove_schedule_ordering( schedule_capture_id IN NUMBER, waitfor_capture_id IN NUMBER);

TBD

REPORT

Generates a report on the given workload replay dbms_workload_replay.report(replay_id IN NUMBER, format IN VARCHAR2) RETURN CLOB;

TBD

RESET_ADVANCED_PARAMETER

Resets all the advanced parameters to their default values dbms_workload_replay.reset_advanced_parameter;

See SET_ADVANCED_PARAMETERS Demo Below

RESUME_REPLAY

Resumes a paused workload replay dbms_workload_replay.resume_replay;

BEGIN

        dbms_workload_replay.initialize_replay('UWReplay', 'CTEMP');

        dbms_workload_replay.start_replay;

        dbms_workload_replay.pause_replay;

        dbms_workload_replay.resume_replay;

      END;

      /

REUSE_REPLAY_FILTER_SET

Reuses existing filters with each call adding one filter set dbms_workload_reuse_replay_filter_set( replay_dir IN VARCHAR2, filter_set IN VARCHAR2);

TBD

SET_ADVANCED_PARAMETER

Sets an advanced parameter for replay besides the ones used with PREPARE_REPLAY. Advanced parameters are not reset to their default values after the replay has finished. This means that once the parameters are set they will persist across replays.

Overload 1 dbms_workload_replay.set_advanced_parameter( pname IN VARCHAR2, pvalue IN VARCHAR2);

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

TBD

Overload 2 dbms_workload_replay.set_advanced_parameter( pname IN VARCHAR2, pvalue IN NUMBER);

TBD

Overload 3 dbms_workload_replay.set_advanced_parameter( pname IN VARCHAR2, pvalue IN BOOLEAN);

set serveroutput on

      

      DECLARE

       retval VARCHAR2(30);

      BEGIN

        dbms_workload_replay.initialize_replay('UWReplay', 'CTEMP');

        dbms_workload_replay.set_advanced_parameter('DO_NO_WAIT_COMMITS', TRUE);

        retval := dbms_workload_replay.get_advanced_parameter( 'DO_NO_WAIT_COMMITS');

        dbms_output.put_line(retval);

        dbms_workload_replay.reset_advanced_parameters;

      END;

      /

SET_ATTRIBUTE

Undocumented attributes for Enterprise Manager dbms_workload_replay.set_attribute( capture_id IN NUMBER, replay_id IN NUMBER, name IN VARCHAR2, -- VARCHAR2(50) value IN VARCHAR2); -- VARCHAR2(200)

TBD

SET_CONSOLIDATED_DIRECTORY

Sets the directory object to be used by a consolidated replay dbms_workload_replay.set_consolidated_directory(replay_dir IN VARCHAR2);

exec dbms_workload_replay.set_consolidated_directory('CTEMP');

SET_REPLAY_DIRECTORY

Identifies a directory object with one or more captures as the replay directory dbms_workload_replay.set_replay_directory(replay_dir IN VARCHAR2);

exec dbms_workload_replay.set_replay_directory('CTEMP');

SET_REPLAY_TIMEOUT

Sets the replay timeout value dbms_workload_replay.set_replay_timeout( enabled IN BOOLEAN DEFAULT TRUE, min_delay IN NUMBER DEFAULT 10, max_delay IN NUMBER DEFAULT 120, delay_factor IN NUMBER DEFAULT 8);

TBD

SET_SQL_MAPPING

Specifies SQL statements to be skipped or replaced in replay

Overload 1 dbms_workload_replay.set_sql_mapping( schedule_cap_id IN NUMBER, sql_id IN VARCHAR2, operation IN VARCHAR2, replacement_sql_text IN VARCHAR2);

TBD

Overload 2 dbms_workload_replay.set_sql_mapping( sql_id IN VARCHAR2, operation IN VARCHAR2, replacement_sql_text IN VARCHAR2);

TBD

SET_USER_MAPPING

Sets a new schema/user name to be used during replay instead of the captured user dbms_workload_replay.set_user_mapping( schedule_cap_id IN NUMBER, capture_user IN VARCHAR2, replay_user IN VARCHAR2);

TBD

Overload 2 dbms_workload_replay.set_user_mapping( capture_user IN VARCHAR2, replay_user IN VARCHAR2);

exec dbms_workload_replay.set_user_mapping('UWCLASS', 'UWSTAGE');

START_CONSOLIDATED_REPLAY

Start_replay for workload consolidation dbms_workload_replay.start_consolidated_replay;

exec dbms_workload_replay.start_consolidated_replay;

START_REPLAY

Starts the workload replay dbms_workload_replay.start_replay;

exec dbms_workload_replay.start_replay;

STOP_STS_C

Undocumented dbms_workload_replay.stop_sts_c( sts_name IN VARCHAR2, sts_owner IN VARCHAR2, in_db_caprep OUT BOOLEAN) RETURN BOOLEAN;

TBD

SYNC_ATTRIBUTES_FROM_FILE

Load the latest attributes from the os file and upsert the changes in the existing attributes. Changes to the file are given priority. dbms_workload_replay.sync_attributes_from_file(capture_id IN NUMBER);

exec dbms_workload_replay.sync_attributes_from_file(6);

USER_CALLS_GRAPH

Undocumented dbms_workload_replay.user_calls_graph(replay_id NUMBER DEFAULT NULL) RETURN uc_graph_table PIPELINED;

SELECT * FROM TABLE(dbms_workload_replay.user_calls_graph(42));

USE_FILTER_SET

Uses the given filter set created by calling CREATE_FILTER_SET to filter the current replay: Should be called after the replay has been initialized, and before it is prepared dbms_workload_replay.user_filter_set(filter_set IN VARCHAR2);

TBD

WORKLOAD_INTELLIGENCE_REPORT

Generates a report that displays the results of Workload Intelligence dbms_workload_replay.workload_intelligence_report( wi_job_name IN VARCHAR2, top_results IN NUMBER, format IN VARCHAR2) RETURN CLOB;

TBD