The Real Application testing, Database Replay process

The Real Application testing, Database Replay process

The Real Application testing feature, which consists of two separate tools, Database Replay and the SQL performance Analyzer, is arguably the most significant new feature in the Oracle Database 11.1 Release. With this process we can simulate a real production workload on a test system; we can apply some patches, change data storage and see how our performance changes on test system.
In this post we step by step review Database replay process

1. Restart prod DB and startup restrict mode (this in optional)
2. We can use optional workload filters to restrict the workload capture to only a part of the actual production workload:
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(FNAME => 'FILTER_NAME',FATTRIBUTE => 'USER',FVALUE => 'SCOTT');
END;
We can find filter’s in view DBA_WORKLOAD_FILTERS;
3. Start Workload capture, means start process which collect workload to the some files, which then will apply on test DB:
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(NAME => '2010OCT',DIR => 'DATA_PUMP_DIR',DURATION => 1200);
END;
Duration is number of seconds for which workload will be captured this is optional parameter.
DIR is oracle directory which must be empty!
We can manually stop capture process with this statement:
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();
END;
We can see status of process in view DBA_WORKLOAD_CAPTURES
4. Preprocess capture data means that capture data will procedure the metadata for captured workload and transform the captured workload datafiles into reply streams called reply files that you can now replay on the test system:
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(CAPTURE_DIR => 'DATA_PUMP_DIR');
END;
5. Now we must create test DB which should have same data structure as prod DB, we can use rman scripts(backup, restore), snapshot standby DB, Data pump utilities or simple cold backup for creating test DB
6. Check that oracle directory in test DB link on directory where replay files are located, in this scenario we create directory TEST_DIR
7. Initializing the replay Data means initialize the data, which loads the metadata into tables required by workload process:
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(REPLAY_NAME => 'TEST_REPLAY',REPLAY_DIR => 'TEST_DIR');
END;
8. Then we mush remap connections with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(CONNECTION_ID => 111);
END;
9. Prepare the Workload for Replay with statement :
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();
END;
There we run procedure with default parameters, for example we can use parameter synchronization, by default true, meaning that the commit order of captured workload will be preserved during the workload replay
10. Set up Reply Clients. The reply driver is a special application that consumes the captured workload by sending replay requests to the test DB. The reply client in essence simulates the production system on the test DB. To Find optimal number of clients we must run this statement on test server in terminal window up to directory where is test_dir
wrc system/a mode=calibrate replay_dir=./test_dir
if we should run with 2 or more clients we must open new terminals and run process in other windows.
Run client with this statement:
wrc system/a mode=replay replaydir=./test_dir
11. Now we can start replay process with statement:
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY();
END:
You must start minimum of one wrc client before you can start the workload replay.
You can manually stop Replay process by statement, this is optional, because replay process will finished itself:
BEGIN
DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY();
END;
Status and progress of replay process you can see in view : DBA_WORKLOAD_REPLAYS
12. Report and result of replay process you can see with this statement :



DECLARE
CAP_ID NUMBER;
REP_ID NUMBER;
REP_RPT CLOB;
BUFFER VARCHAR2(32767);
BUFFER_SIZE CONSTANT BINARY_INTEGER := 32767;
AMOUNT BINARY_INTEGER;
OFFSET NUMBER(38);
FILE_HANDLE UTL_FILE.FILE_TYPE;
DIRECTORY_NAME CONSTANT VARCHAR2(80) := 'TEST_DIR';
V_FILENAME CONSTANT VARCHAR2(80) := 'REPLAY.HTML';
BEGIN
CAP_ID := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(DIR => 'TEST_DIR');
SELECT MAX(ID)
INTO REP_ID
FROM DBA_WORKLOAD_REPLAYS
WHERE CAPTURE_ID = CAP_ID ;
REP_RPT := DBMS_WORKLOAD_REPLAY.REPORT(REPLAY_ID => REP_ID,
FORMAT => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
-- WRITE REPORT TO FILE
DBMS_OUTPUT.ENABLE(100000);
-- --------------------------------
-- OPEN NEW XML FILE IN WRITE MODE
-- --------------------------------
FILE_HANDLE := UTL_FILE.FOPEN(LOCATION => 'TEST_DIR',
FILENAME => V_FILENAME,
OPEN_MODE => 'W',
MAX_LINESIZE => BUFFER_SIZE);
AMOUNT := BUFFER_SIZE;
OFFSET := 1;
WHILE AMOUNT >= BUFFER_SIZE
LOOP
DBMS_LOB.READ(LOB_LOC => REP_RPT,
AMOUNT => AMOUNT,
OFFSET => OFFSET,
BUFFER => BUFFER);
OFFSET := OFFSET + AMOUNT;
UTL_FILE.PUT(FILE => FILE_HANDLE,
BUFFER => BUFFER);
UTL_FILE.FFLUSH(FILE => FILE_HANDLE);
END LOOP;
UTL_FILE.FCLOSE(FILE => FILE_HANDLE);
END;