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 |