| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmswrr.sql |
| First Released |
11.1.0.6 |
| 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_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 |
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_WORKLOAD_REPLAY'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_WORKLOAD_REPLAY'; |
| Security Model |
Execute is granted to EXECUTE_CATALOG_ROLE and DBA roles |
| |
ADD_FILTER (new 11.2.0.1)  |
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 |
| |
| 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 |
| Private function used internally: Undocumented |
dbms_workload_replay.client_vitals(
id IN NUMBER,
name IN VARCHAR2,
value IN NUMBER); |
| TBD |
| |
| COMPARE_PERIOD_REPORT (new 11.2.0.1) |
| 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 (new 11.2.0.2) |
|
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 |
| |
| CREATE_FILTER_SET (new 11.2.0.1) |
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.compare_period_report(
replay_dir IN VARCHAR2,
filter_set IN VARCHAR2,
default_action IN VARCHAR2 DEFAULT 'INCLUDE'); |
| TBD |
| |
| DELETE_FILTER (new 11.2.0.1) |
| Deletes the filter with the given name |
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); |
SELECT id, name
FROM dba_workload_replays;
exec dbms_workload_replay.delete_replay_info(1); |
| |
| DIVERGING_STATEMENT_STATUS (new 11.2.0.1) |
| For a single diverging call in a given replay, has its detailed divergence information be loaded.
The 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 |
| |
| 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_UC_GRAPH (new 11.2.0.1) |
| Undocumented |
dbms_workload_replay.export_uc_graph(replay_id IN NUMBER); |
| exec dbms_workload_replay.export_uc_graph(42); |
| |
| 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_DIVERGING_STATEMENT (new 11.2.0.1) |
| 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_PROCESSING_PATH (new 11.2.0.1) |
| 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_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'); |
| |
| GET_REPLAY_PATH (new 11.2.0.1) |
| 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 (new 11.2.0.2) |
| 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;
/ |
| |
| 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_UC_GRAPH (new 11.2.0.1) |
| Undocumented |
dbms_workload_replay.import_uc_graph(replay_id IN NUMBER); |
| exec dbms_workload_replay.import_uc_graph(42); |
| |
| 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 (new 11.2.0.1) |
| Undocumented |
dbms_workload_replay.initialize_replay_internal(
replay_name IN VARCHAR2,
replay_dir IN VARCHAR2,
replay_type IN VARCHAR2); |
| TBD |
| |
| IS_REPLAY_PAUSED (new 11.2.0.1) |
| 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;
/ |
| |
| PAUSE_REPLAY |
| Pauses a workload replay |
dbms_workload_replay.pause_replay; |
| See RESUME_REPLAY Demo Below |
| |
| POPULATE_DIVERGENCE (new 11.2.0.1) |
| 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 (new 11.2.0.1) |
| 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 |
LOADING: the RDBMS is currently undertaking a bulk load of all of the statement divergence data for the given replay |
| NOT LOADED |
NOT LOADED: neither of the above, i.e., not LOADING and at least 1 statement's divergence data has not been loaded |
|
| TBD |
| |
| PREPARE_REPLAY (new overload in 11.2.0.2) |
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 (new 11.2.0.1) |
| Undocumented |
dbms_workload_replay.process_replay_graph RETURN NUMBER; |
DECLARE
retval NUMBER;
BEGIN
retval := dbms_workload_replay.process_replay_graph;
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 |
| 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. Each call adds 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_REPLAY_TIMEOUT (new 11.2.0.2) |
| 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 |
| |
| START_REPLAY |
| Starts the workload replay |
dbms_workload_replay.start_replay; |
| exec dbms_workload_replay.start_replay; |
| |
| USER_CALLS_GRAPH (new 11.2.0.1) |
| Undocumented |
dbms_workload_replay.user_calls_graph(replay_id NUMBER DEFAULT NULL)
RETURN uc_graph_table PIPELINED; |
| TBD |
| |
| USE_FILTER_SET (new 11.2.0.1) |
| 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 |
| |
| Replay Demo |
| Continuation of DBMS_WORKLOAD_CAPTURE demo |
TBD |
