Oracle Real Application Testing (RAT) – Part 3 : What is Replay and how to do it ?
Posted by FatDBA on February 14, 2020
Hi Guys,
In the same continuation from where I left last time …
This is the Part 3 of the final part of this three post series on Oracle Real Application Testing or RAT. This part mainly deals with the replay side or the target end where you finally replays of triggers the captured workload.
Alright, so let’s get started!
I am starting immediately after step 6 (which we covered in Part 2).
Step 7:
Next move all files from capture directory to target system (12c POC VM box in this case). I have created the directory for REPLAY purposes there at the target to receive all these files and to play them later on.
Next transfer the files from source.
Match the count on target directory, should be same.
RAT Replay (General) Steps:
Step 1:
The first step in preparation is to process the CAPTURE in the replay directory.
Note: This will be a one-time activity and only do if you are doing it for the first time. If the load is already processed and you have replayed it earlier then there is not any use to follow this step.
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(capture_dir=>'RAT_TRIALRUN');
END;
/
Step 2:
RESTORE DB: At This point we need to restore the database prior to the point when the Capture was started Note: No need to perform this step if this was already performed using Import or GRPs.
Step 3:
Next is to put the DB in readiness state. We have to initialize the replay.
execute DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY('REPLAY_TRIALRUN1', 'RAT_TRIALRUN');
Step 4:
Next we will PREPARE the replay. There are many arguments that you can pass with the ‘synchronization’ parameter & many other parameters to tailor made your replay. We have finalized and opted PREPARE_REPLAY step i.e. synchronization specially.
execute DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization => 'SCN');
Step 5:
Next to get the required numbers of workload clients to run and replay that load on target database.
For that we have to run wrc utility in calibrate mode.
wrc system/xxxx mode=calibrate replaydir=/nfs_exp/prodrattest/ratdir_02092019
Step 6:
Next is to fire the recommended numbers of wrc (workload) clients on the target database to replay the captured workload.
For example, if 8 is the recommended number of replay clients. Then we can
Ex:
nohup wrc system/oracle@ mode=replay replaydir=/home/replay/rat &
On RAC Node 1:
wrc system/XXXX mode=replay replaydir=/nfs_exp/prodrattest/ratdir_02092019
wrc system/XXXX mode=replay replaydir=/nfs_exp/prodrattest/ratdir_02092019
wrc system/XXXX mode=replay replaydir=/nfs_exp/prodrattest/ratdir_02092019
wrc system/XXXX mode=replay replaydir=/nfs_exp/prodrattest/ratdir_02092019
On RAC Node 2:
wrc system/XXXX mode=replay replaydir=/nfs_exp/prodrattest/ratdir_02092019
wrc system/XXXX mode=replay replaydir=/nfs_exp/prodrattest/ratdir_02092019
wrc system/XXXX mode=replay replaydir=/nfs_exp/prodrattest/ratdir_02092019
wrc system/XXXX mode=replay replaydir=/nfs_exp/prodrattest/ratdir_02092019
Step 7:
Next when all above steps are completed, next we will be replaying the workload.
execute DBMS_WORKLOAD_REPLAY.START_REPLAY();
Step 8:
Monitoring the Replay
There are many ways to perform the monitoring of the replay process, I will be sharing few scripts and discuss few of the methods that can be used to check/verify the replay progress.
Way 1:
Monitor the progress of replay using one of the dynamic view named DBA_WORKLOAD_REPLAYS
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
SELECT ID, NAME,DBNAME,
PARALLEL,STATUS, to_char(START_TIME,'dd/mm/yyyy hh24:mi'),
to_char(END_TIME,'dd/mm/yyyy hh24:mi'),DURATION_SECS/60, NUM_CLIENTS, USER_CALLS, DBTIME, RAC_MODE
AWR_BEGIN_SNAP,AWR_END_SNAP,AWR_EXPORTED
FROM DBA_WORKLOAD_REPLAYS;
where id=2;
Way 2:
To check what’s all is happening in the database including the load pushed by WRC clients.
This is a join of gv$sqlarea, gv$session to get session and SQL details.
select x.inst_id,x.sid ,x.serial#,x.username,sqlarea.parsing_schema_name,x.sql_id
,sqlarea.plan_hash_value
,sqlarea.LAST_ACTIVE_TIME
,sqlarea.DISK_READS
,sqlarea.BUFFER_GETS
,sqlarea.ROWS_PROCESSED
,sqlarea.CPU_TIME
,sqlarea.LAST_LOAD_TIME
,x.event,x.osuser,x.status
,x.process,x.machine,x.program,x.module,x.action
,TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime
,x.LAST_CALL_ET
,x.SECONDS_IN_WAIT,x.state,sql_text,
ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09')) RUNNING_SINCE
from gv$sqlarea sqlarea,gv$session x
where x.sql_hash_value = sqlarea.hash_value
and x.sql_address = sqlarea.address
and sql_text not like '%select x.inst_id,x.sid ,x.serial# ,x.username ,x.sql_id ,x.event%'
and sql_text not like '%select x.inst_id,x.sid ,x.serial#,x.username,sqlarea.parsing_schema_name,x.sql_id ,sqlarea.plan_hash_value%'
and x.status='ACTIVE'
and x.USERNAME is not null
and x.SQL_ADDRESS = sqlarea.ADDRESS
and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE
order by RUNNING_SINCE desc;
Way 3:
Below is the method or a way to identify the replay progress in terms of percentage completed.
Code and text is pasted below.
Step 9:
Reporting
Next step is to generate some RAT specific reporting which will be helpful to replay performance and other statistics.
DECLARE
cap_id NUMBER;
rep_id NUMBER;
rep_rpt CLOB;
rpt_len NUMBER;
BEGIN
rep_rpt :=DBMS_WORKLOAD_REPLAY.REPORT(replay_id => 1,format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
dbms_output.put_line(DBMS_LOB.SUBSTR(rep_rpt,32767,1));
END;
/
Below is the code to generate Capture Vs Replay reports.
var report_bind clob;
begin
DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT (replay_id1 => 1, replay_id2=> null, format => 'HTML', result => :report_bind);
end;
/
Hope It Helps
Prashant Dixit
Tales From A Lazy Fat DBA 
Shanker Thodupunoor said
Hello,
Please help me to understand if we need to install or configure any software to execute workload replay client as I was getting some errors and unable to figure it out.
FatDBA said
Hi Shanker,
You only need to install the Oracle DB software on the same or diferent host from where planning to fire workload.
The replay client is a multithreaded program (an executable named wrc located in the $ORACLE_HOME/bin directory) where each thread submits a workload from a captured session
Raj said
Hello,
Could you please help me in rephrasing the replay_progress_percentage queries to see the percentage of replay. As i am getting the errors.
SQL> @replay_progress.sql
SELECT min(post_commit_scn), max(post_commit_scn) INTO min_scn,max_scn FROM wrr$_replay_scn_order
*
ERROR at line 1:
ORA-00905: missing keyword
SP2-0734: unknown command beginning “done := (c…” – rest of line ignored.
SP2-0734: unknown command beginning “dbms_outpu…” – rest of line ignored.
SP2-0734: unknown command beginning “dbms_outpu…” – rest of line ignored.
SP2-0734: unknown command beginning “dbms_outpu…” – rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning “dbms_outpu…” – rest of line ignored.
SP2-0042: unknown command “END” – rest of line ignored.
SELECT min(post_commit_scn), max(post_commit_scn) INTO min_scn,max_scn FROM wrr$_replay_scn_order
*
ERROR at line 1:
ORA-00905: missing keyword
FatDBA said
Hi Raj,
What is the version of the database you’re using ?
Raj said
19.12.0.0
FatDBA said
Well it’s well tried and tested till 12.2, not sure if any of the column is changed/altered or missed in 19c for referred tables.
Did you tried running the code which I have pasted in the other reply ?
Raj said
Yes, the output pasted is executed as a file.
SQL> @replay_progress.sql
SELECT min(post_commit_scn), max(post_commit_scn) INTO min_scn,max_scn FROM wrr$_replay_scn_order
*
ERROR at line 1:
ORA-00905: missing keyword
SP2-0734: unknown command beginning “done := (c…” – rest of line ignored.
SP2-0734: unknown command beginning “dbms_outpu…” – rest of line ignored.
SP2-0734: unknown command beginning “dbms_outpu…” – rest of line ignored.
SP2-0734: unknown command beginning “dbms_outpu…” – rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning “dbms_outpu…” – rest of line ignored.
SP2-0042: unknown command “END” – rest of line ignored.
SELECT min(post_commit_scn), max(post_commit_scn) INTO min_scn,max_scn FROM wrr$_replay_scn_order
*
ERROR at line 1:
ORA-00905: missing keyword
FatDBA said
Now this is strange!
I am able to run it on my 12.2 database here with no errors nothing. Are you sure, you are not directly copying it from here and pasting in text file (vi), if yes, please remember to change quotes etc.
But yes, missing keyword shouldnt have happened due to that … Let me recheck what could be the cause.
FatDBA said
This is what it is …
—————————————————————————————
— Compute the replay progression and estimate the time left
— Note: This is an estimated time only, not an absolute value as it is based on SCN.
—————————————————————————————
SELECT min(post_commit_scn), max(post_commit_scn)
INTO min_scn,max_scn
FROM wrr$_replay_scn_order;
done := (clock – min_scn) / (max_scn – min_scn);
total_time := (systimestamp – start_time) / done;
dbms_output.put_line(‘Estimated progression in replay: ‘ ||
to_char(100*done, ’00’) || ‘% done.’);
dbms_output.put_line(‘Estimated time before completion: ‘ ||
((1 – done) * total_time));
dbms_output.put_line(‘Estimated total time for replay: ‘ ||
total_time);
dbms_output.put_line(‘Estimated final time for replay: ‘ ||
to_char(start_time + total_time,
‘DD-MON-YY HH24:MI:SS’));
END;
/
FatDBA said
I rather suggest you to put below content into a fresh/new file and then call to monitor approximation in replay.
==========
set serveroutput on
DECLARE
my_next_ticker NUMBER;
clock NUMBER;
wait_for_scn NUMBER;
counts NUMBER;
replay_id NUMBER;
thr_failure NUMBER;
start_time DATE;
num_tickers NUMBER;
min_scn NUMBER;
max_scn NUMBER;
done NUMBER;
total_time INTERVAL DAY TO SECOND;
CURSOR get_next_ticker(my_next_ticker NUMBER) IS
SELECT spid, event, inst_id, wrc_id, client_pid
FROM gv$workload_replay_thread
WHERE file_id = my_next_ticker;
BEGIN
dbms_output.put_line(‘********************************’);
dbms_output.put_line(‘* Replay Status Report *’);
dbms_output.put_line(‘********************************’);
—————————————–
— Make sure that a replay is in progress
—————————————–
SELECT count(*) INTO counts
FROM dba_workload_replays
WHERE status=’IN PROGRESS’;
if (counts = 0) then
dbms_output.put_line(‘No replay in progress!’);
return;
end if;
——————-
— Get replay state
——————-
SELECT id,start_time INTO replay_id, start_time
FROM dba_workload_replays
WHERE status=’IN PROGRESS’;
SELECT count(*) INTO counts
FROM gv$workload_replay_thread
WHERE session_type = ‘REPLAY’;
SELECT min(wait_for_scn), max(next_ticker), max(clock)
INTO wait_for_scn, my_next_ticker, clock
FROM v$workload_replay_thread
WHERE wait_for_scn 0
AND session_type = ‘REPLAY’;
dbms_output.put_line(‘Replay has been running for: ‘ ||
to_char(systimestamp – start_time));
dbms_output.put_line(‘Current clock is: ‘ || clock);
dbms_output.put_line(‘Replay is waiting on clock: ‘ ||
wait_for_scn);
dbms_output.put_line(counts || ‘ threads are currently being
replayed.’);
—————————————-
— Find info about the next clock ticker
—————————————-
num_tickers := 0;
for rec in get_next_ticker(my_next_ticker) loop
— We only want the next clock ticker
num_tickers := num_tickers + 1;
exit when num_tickers > 1;
dbms_output.put_line(‘Next ticker is process ‘ || rec.spid ||
‘ (‘ || rec.wrc_id || ‘,’ || rec.client_pid ||
‘) in instance ‘ || rec.inst_id ||
‘ and is waiting on ‘);
dbms_output.put_line(‘ ‘ || rec.event);
end loop;
—————————————————————————————
— Compute the replay progression and estimate the time left
— Note: This is an estimated time only, not an absolute value as it is based on SCN.
—————————————————————————————
SELECT min(post_commit_scn), max(post_commit_scn)
INTO min_scn,max_scn
FROM wrr$_replay_scn_order;
done := (clock – min_scn) / (max_scn – min_scn);
total_time := (systimestamp – start_time) / done;
dbms_output.put_line(‘Estimated progression in replay: ‘ ||
to_char(100*done, ’00’) || ‘% done.’);
dbms_output.put_line(‘Estimated time before completion: ‘ ||
((1 – done) * total_time));
dbms_output.put_line(‘Estimated total time for replay: ‘ ||
total_time);
dbms_output.put_line(‘Estimated final time for replay: ‘ ||
to_char(start_time + total_time,
‘DD-MON-YY HH24:MI:SS’));
END;
/
Raj said
Found the below is working sql which is providing the top sql’s , wait events and the percentage completion of replay.
select id,name,status from dba_workload_replays;
set serveroutput on
exec dbms_wrr_report.replay(6);
Raj said
It worked in 19c but not sure about lower versions, just fyi
FatDBA said
Great!
Let me check
FatDBA said
Yup, always query dba_workload_replays view to get that info.