CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_WORKLOAD_REPLAY
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmswrr.sql
First Released 11.1

Constants
Name Data Type Value
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_CMD_END_OF_REPLAY NUMBER 1
KECP_CMD_REPLAY_CANCELLED NUMBER 2

Dependencies
dba_hist_snapshot dbms_server_alert utl_file
dba_sequences dbms_swrf_internal v$database
dba_workload_captures dbms_workload_capture v$instance
dba_workload_replays dbms_workload_replay_lib v$parameter
dbms_advisor dbms_workload_repository wri$_alert_threshold
dbms_assert dbms_wrr_internal wrr$_captures
dbms_lob dual wrr$_connection_map
dbms_lock gv$workload_replay_thread wrr$_replays
dbms_random props$ wrr$_replay_seq_data
dbms_report prvt_smgutil xmltype
Security Model Execute is granted to EXECUTE_CATALOG_ROLE and DBA roles
 
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 50)
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_VITALS (new 11.1.0.7)
Private function used internally: Undocumented dbms_workload_replay.client_vitals(
id    IN NUMBER,
name  IN VARCHAR2,
value IN NUMBER);
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);
SELECT id, name
FROM dba_workload_replays;

exec dbms_workload_replay.delete_replay_info(1);
 
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);
 
GET_ADVANCED_PARAMETER (new 11.1.0.7)
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_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) RETURN NUMBER;
SELECT id, name, dir_path
FROM dba_workload_replays;

exec dbms_workload_replay.export_awr('CTEMP');
 
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
 
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');
 
PAUSE_REPLAY (new 11.1.0.7)

Pauses a workload replay
dbms_workload_replay.pause_replay;
See RESUME_REPLAY Demo Below
 
PREPARE_REPLAY
Puts the DB state in REPLAY mode. 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);
exec dbms_workload_replay.prepare_replay(TRUE, 100, 100, TRUE);
 
PROCESS_CAPTURE (new 11.1.0.7 parameter)
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 (new 11.1.0.7)

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 (new 11.1.0.7)

While a capture process is running returns an estimate of the minutes  remaining before  processing is completed. Will return NULL during the first mintue 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;
/
 
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
 
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 (new 11.1.0.7)
Resets all the advanced parameters to their default values dbms_workload_replay.reset_advanced_parameter;
See SET_ADVANCED_PARAMETERS Demo Below
 
RESUME_REPLAY (new 11.1.0.7)

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;
/
 
SET_ADVANCED_PARAMETER (new 11.1.0.7)

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;
/
 
START_REPLAY
Starts the workload replay dbms_workload_replay.start_replay;
exec dbms_workload_replay.start_replay;
 
Replay Demo
Continuation of DBMS_WORKLOAD_CAPTURE demo TBD
 
Related Topics
DBMS_WORKLOAD_CAPTURE
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [148 users online]    © 2010 psoug.org