Oracle | DBMS_WORKLOAD_REPLAY

Oracle DBMS_WORKLOAD_REPLAY_I 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 Internal supporting package supporting DBMS_WORKLOAD_REPLAY for replaying transactions for real application testing.

AUTHID DEFINER

Dependencies

ALL_USERS	DBMS_SQLTUNE_INTERNAL
AS_REPLAY	DBMS_STANDARD	WRR$_COMMIT_FIRST_CALL_SCN
DBA_ADDM_FINDINGS	DBMS_STATS	WRR$_CONNECTION_MAP
DBA_HIST_ACTIVE_SESS_HISTORY	DBMS_SYSTEM	WRR$_FILTERS
DBA_HIST_DATABASE_INSTANCE	DBMS_SYS_ERROR	WRR$_REPLAYS
DBA_HIST_FILESTATXS	DBMS_UTILITY	WRR$_REPLAY_CALL_FILTER
DBA_HIST_IC_DEVICE_STATS	DBMS_WORKLOAD_CAPTURE_I	WRR$_REPLAY_CALL_INFO
DBA_HIST_INST_CACHE_TRANSFER	DBMS_WORKLOAD_CAPTURE_LIB	WRR$_REPLAY_CLIENTS
DBA_HIST_INTERCONNECT_PINGS	DBMS_WORKLOAD_REPLAY	WRR$_REPLAY_COMMITS
DBA_HIST_IOSTAT_FILETYPE	DBMS_WORKLOAD_REPLAY_LIB	WRR$_REPLAY_COMMIT_REMAPPING
DBA_HIST_OSSTAT	DBMS_WORKLOAD_REPOSITORY	WRR$_REPLAY_DEP_GRAPH
DBA_HIST_PARAMETER	DBMS_WRR_INTERNAL	WRR$_REPLAY_DIRECTORY
DBA_HIST_SNAPSHOT	DBMS_WRR_PROTECTED	WRR$_REPLAY_DIVERGENCE
DBA_HIST_SNAP_ERROR	DBMS_WRR_REPORT	WRR$_REPLAY_DIV_SUMMARY
DBA_HIST_SQLTEXT	DIUTIL	WRR$_REPLAY_FILES
DBA_HIST_SYSTEM_EVENT	DUAL	WRR$_REPLAY_FILTER_SET
DBA_HIST_SYS_TIME_MODEL	GV$ACTIVE_INSTANCES	WRR$_REPLAY_GROUP_ASSIGNMENTS
DBA_HIST_TEMPSTATXS	GV$INSTANCE	WRR$_REPLAY_INSTANCES
DBA_SQLSET	GV$WORKLOAD_REPLAY_THREAD	WRR$_REPLAY_LOGIN_QUEUE
DBA_WI_JOBS	PLITBLM	WRR$_REPLAY_REFERENCES
DBA_WI_PATTERNS	PROPS$	WRR$_REPLAY_SCHEDULES
DBA_WI_PATTERN_ITEMS	SQLSET_ROW	WRR$_REPLAY_SCN_ORDER
DBA_WI_STATEMENTS	SYS_IXMLAGG	WRR$_REPLAY_SEQ_DATA
DBA_WI_TEMPLATES	UMF$_CAPTURE_RAW	WRR$_REPLAY_SQL_BINDS
DBA_WORKLOAD_CAPTURES	UTL_FILE	WRR$_REPLAY_SQL_MAP
DBA_WORKLOAD_CAPTURE_SQLTEXT	UTL_RAW	WRR$_REPLAY_SQL_TEXT
DBA_WORKLOAD_LONG_SQLTEXT	V$CONTAINERS	WRR$_REPLAY_STATS
DBA_WORKLOAD_REPLAYS	V$DATABASE	WRR$_SCHEDULE_CAPTURES
DBA_WORKLOAD_REPLAY_DIVERGENCE	V$INSTANCE	WRR$_SCHEDULE_ORDERING
DBA_WORKLOAD_SCHEDULE_CAPTURES	V$PARAMETER	WRR$_SEQUENCE_EXCEPTIONS
DBMS_ADDM	WRR$_ASH_TIME_PERIOD	WRR$_USER_MAP
DBMS_ADVISOR	WRR$_CAPTURES	WRR$_WORKLOAD_ATTRIBUTES
DBMS_ASSERT	WRR$_CAPTURE_FILES	WRR$_WORKLOAD_EX_OBJECTS
DBMS_LOB	WRR$_CAPTURE_FILE_DETAILS	WRR$_WORKLOAD_SESSIONS
DBMS_LOCK	WRR$_CAPTURE_ID	XMLAGG
DBMS_REPORT	WRR$_CAPTURE_SQLTEXT	XMLSEQUENCE
DBMS_SQLTUNE	WRR$_CAPTURE_STATS	XMLTYPE

Documented No

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 18c

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvtwrr.plb

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	USE_FILTER_SET
GET_ADVANCED_PARAMETER	PREPARE_CONSOLIDATED_REPLAY	WORKLOAD_INTELLIGENCE_REPORT
GET_ATTRIBUTE	PREPARE_REPLAY

ADD_CAPTURE

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

Overload 1 dbms_workload_replay_i.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_i.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_i.add_filter( fname IN VARCHAR2, fattribute IN VARCHAR2, fvalue IN VARCHAR2);

TBD

Overload 2 dbms_workload_replay_i.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_i.add_schedule_ordering( schedule_capture_id IN NUMBER, waitfor_capture_id IN NUMBER);

exec dbms_workload_replay_i.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_i.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_i.assign_group_to_instance( group_id IN NUMBER, instance_number IN NUMBER);

exec dbms_workload_replay_i.assign_group_to_instance(6, 2);

BEGIN_REPLAY_SCHEDULE

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

exec dbms_workload_replay_i.begin_replay_schedule('UWReplaySched');

CALIBRATE

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

set long 1000000

      set serveroutput on

      

      DECLARE

       c CLOB;

      BEGIN

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

        dbms_output.put_line(c);

      END;

      /

CANCEL_REPLAY

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

desc dba_workload_replays

      

      SELECT name, error_message

      FROM dba_workload_replays;

      

      exec dbms_workload_replay_i.cancel_replay('Just Because');

      

      SELECT name, error_message

      FROM dba_workload_replays;

CLIENT_CONNECT

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

TBD

CLIENT_GET_REPLAY_SUBDIR

Undocumented dbms_workload_replay_i.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_i.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_i.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_i.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_i.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_i.configure(duration_limit IN NUMBER);

exec 
      dbms_workload_replay_i.configure(10);

      BEGIN dbms_workload_replay_i.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 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_i.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_i.delete_attribute( capture_id IN NUMBER, replay_id IN NUMBER, name IN VARCHAR2);

TBD

DELETE_FILTER

Delete the named filter dbms_workload_replay_i.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_i.delete_replay_info( replay_id IN NUMBER, permanent IN BOOLEAN DEFAULT FALSE);

SELECT id, name

      FROM dba_workload_replays;

      

      exec dbms_workload_replay_i.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_i.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_i.end_replay_schedule;

exec dbms_workload_replay_i.end_replay_schedule;

EXPORT_AWR

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

SELECT id, name

      FROM dba_workload_replays;

      

      exec dbms_workload_replay_i.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_i.export_perofrmance_data(capture_id IN NUMBER);

exec dbms_workload_replay_i.export_performance_data(6);

EXPORT_UC_GRAPH

Undocumented dbms_workload_replay_i.export_uc_graph(replay_id IN NUMBER);

exec dbms_workload_replay_i.export_uc_graph(42);

GENERATE_CAPTURE_SUBSET

Creates a new capture from an existing workload capture dbms_workload_replay_i.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);j

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_i.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_i.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_i.get_captured_tables(capture_dir IN VARCHAR2) RETURN CLOB;

SELECT dbms_workload_replay_i.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_i.get_clock RETURN NUMBER;

SELECT dbms_workload_replay_i.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_i.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_i.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_i.get_processing_path(capture_id IN NUMBER) RETURN VARCHAR2;

set serveroutput on

      

      DECLARE

       dirpath VARCHAR2(100);

      BEGIN

        dirpath := dbms_workload_replay_i.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_i.get_replay_i_directory RETURN VARCHAR2;

SELECT dbms_workload_replay_i.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_i.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_i.export_awr('CTEMP');

GET_REPLAY_PATH

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

set serveroutput on

      

      DECLARE

       dirpath VARCHAR2(100);

      BEGIN

        dirpath := dbms_workload_replay_i.get_replay_path(6);

        dbms_output.put_line(dirpath);

      END;

      /

GET_REPLAY_TIMEOUT

Returns the replay timeout setting dbms_workload_replay_i.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_i.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_i.group_workload(max_groups IN NUMBER);

exec dbms_workload_replay_i.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_i.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_i.import_performance_data( capture_id IN NUMBER, staging_schema IN VARCHAR2, force_cleanup IN BOOLEAN DEFAULT FALSE);

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

IMPORT_UC_GRAPH

Undocumented dbms_workload_replay_i.import_uc_graph(replay_id IN NUMBER);

exec dbms_workload_replay_i.import_uc_graph(42);

INITIALIZE_CONSOLIDATED_REPLAY

Puts the DB into INIT state for a multiple-capture replay dbms_workload_replay_i.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_i.initialize_replay( replay_name IN VARCHAR2, replay_dir IN VARCHAR2);

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

INITIALIZE_REPLAY_INTERNAL

Undocumented

The values for "REPLAY TYPE" are not published dbms_workload_replay_i.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_i.is_replay_paused RETURN BOOLEAN;

set serveroutput on

      

      BEGIN

        IF dbms_workload_replay_i.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_i.load_divergence(replay_id IN NUMBER);

exec dbms_workload_replay_i.load_divergence(42);

Overload 2 dbms_workload_replay_i.load_divergence(play_dir IN VARCHAR2);

exec dbms_workload_replay_i.load_divergence('CTEMP');

LOAD_LONG_SQLTEXT

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

exec dbms_workload_replay_i.load_long_sqltext(11);

LOAD_TRACKED_COMMITS

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

exec dbms_workload_replay_i.load_tracked_commits(8);

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

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

PAUSE_REPLAY

Pauses a workload replay dbms_workload_replay_i.pause_replay;

See RESUME_REPLAY Demo Below

PERSIST_ATTRIBUTES

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

exec dbms_workload_replay_i.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_i.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_i.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_i.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_i.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_i.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_i.prepare_replay(TRUE, 100, 100, TRUE);

Overload 2 dbms_workload_replay_i.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_i.process_capture( capture_dir IN VARCHAR2, parallel_level IN NUMBER);

exec dbms_workload_replay_i.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_i.process_capture_completion RETURN NUMBER;

DECLARE

       retval VARCHAR2(10);

      BEGIN

        retval := '-'||dbms_workload_replay_i.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_i.process_capture_remaining_time RETURN NUMBER;

DECLARE

       retval VARCHAR2(10);

      BEGIN

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

        dbms_output.put_line(retval);

      END;

      /

PROCESS_REPLAY_GRAPH

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

DECLARE

       retval NUMBER;

      BEGIN

        retval := dbms_workload_replay_i.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_i.remap_connection( connection_id IN NUMBER, replay_connection IN VARCHAR2);

TBD

REMOVE_CAPTURE

Remove the given capture from the current schedule dbms_workload_replay_i.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_i.remove_repay_schedule(schedule_name IN VARCHAR2);

TBD

REMOVE_SCHEDULE_ORDERING

Remove a wait-for dependency from a replay schedule dbms_workload_replay_i.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_i.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_i.reset_advanced_parameter;

See SET_ADVANCED_PARAMETERS Demo Below

RESUME_REPLAY

Resumes a paused workload replay dbms_workload_replay_i.resume_replay;

BEGIN

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

        dbms_workload_replay_i.start_replay;

        dbms_workload_replay_i.pause_replay;

        dbms_workload_replay_i.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_i. 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_i.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_i.set_advanced_parameter( pname IN VARCHAR2, pvalue IN NUMBER);

TBD

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

set serveroutput on

      

      DECLARE

       retval VARCHAR2(30);

      BEGIN

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

        dbms_workload_replay_i.set_advanced_parameter('DO_NO_WAIT_COMMITS', TRUE);

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

        dbms_output.put_line(retval);

        dbms_workload_replay_i.reset_advanced_parameters;

      END;

      /

SET_ATTRIBUTE

Undocumented attributes for Enterprise Manager dbms_workload_replay_i.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_i.set_consolidated_directory(replay_dir IN VARCHAR2);

exec dbms_workload_replay_i.set_consolidated_directory('CTEMP');

SET_REPLAY_DIRECTORY

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

exec dbms_workload_replay_i.set_replay_directory('CTEMP');

SET_REPLAY_TIMEOUT

Sets the replay timeout value dbms_workload_replay_i.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_i.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_i.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_i.set_user_mapping( schedule_cap_id IN NUMBER, capture_user IN VARCHAR2, replay_user IN VARCHAR2);

TBD

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

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

START_CONSOLIDATED_REPLAY

Start_replay for workload consolidation dbms_workload_replay_i.start_consolidated_replay;

exec dbms_workload_replay_i.start_consolidated_replay;

START_REPLAY

Starts the workload replay dbms_workload_replay_i.start_replay;

exec dbms_workload_replay_i.start_replay;

STOP_STS_C

Undocumented dbms_workload_replay_i.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_i.sync_attributes_from_file(capture_id IN NUMBER);

exec dbms_workload_replay_i.sync_attributes_from_file(6);

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_i.user_filter_set(filter_set IN VARCHAR2);

TBD

WORKLOAD_INTELLIGENCE_REPORT

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

TBD