Author: dbapranjan

Oracle Real Application Testing (RAT) was created to give DBAs the ability to make preemptive modifications while also knowing exactly what to expect when a change is done. It considerably reduces the possibility of any harmful consequences.

Oracle RAT is made up of two major parts. The first component is referred to as Database Replay. This program allows you to capture and replay workloads from one environment into another.

Simply capture the workload, transfer the files to a test environment, and then recreate the action that your actual users completed.

Oracle Database Replay is an Oracle Database functionality that allows you to capture and replay database workloads. It enables you to precisely measure the impact of system changes, like as upgrades, patches, and configuration changes, on your database system’s overall performance and stability. This allows the enterprise business to quickly adopt new technologies, and changes while eliminating the risk. Database replay will help you to test performance as well as application functionality.

USE CASES

Use Case 1: Conduct a load test, but you don’t have the number of users you need in order to easily do it?

The other component is called SQL Performance Analyzer and it allows you to assess the impact of the load on your test system through detailed analysis.

Use Case 2: if you want to increase SGA in production, you could first make the update in your test environment and use the production load to see if there’s any value in doing this without trial and error in a production system.

Use Case 3: upgrading database versions, consolidating schemas into a single database, introducing new hardware, and more.

Users can capture the workload either using Enterprise Manager GUI Interface or command line interface provided by DBMS_WORKLOAD_xxxxx packages.

There are mainly four phases involved in Oracle Database replay:

• Workload capture
• Workload pre-processing
• Workload replay
• Analysis and reporting

Workload capture

In this phase users will capture the production workload from the database. Enabling the workload capture involves tracking and recording all external requests from users, application etc. and changes will be stored in binary files called capture files. These files will have information such as SCN, SQL Text, bind variables etc.

Create a directory where you want to keep all your capture files:

mkdir -p /u01/app/oracle/rply
SQL>	Create or replace directory  db_replay_dir  AS '/u01/app/oracle/rply';
SQL>	grant read, write on directory db_replay_dir to sys;

Use START_CAPTURE procedure to start workload capture:

BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name     => 'tst_prd_capture',
				dir      => 'DB_REPLAY_DIR',
				duration => 300,
				capture_sts => TRUE,
				sts_cap_interval => 300);
END;
/

Replace capture_name with a name for the workload capture, capture_directory with the directory where the capture files will be stored, and duration with the duration of the capture in seconds.

If the DURATION parameter value is not specified then workload capture will continue until the FINISH_CAPTURE procedure is used.

Use capture_sts parameter to capture a SQL tuning set in parallel with the workload capture.

The default value of STS_CAP_INTERVAL is 300 and this parameter specifies the duration of the SQL tuning set capture.

col name format a15
col status for a15
col dir_path for a30
set lines 200
set pages 9999
 
select id, name, status, start_time, end_time , connects, user_calls, dir_path from dba_workload_captures;

Use the FINISH_CAPTURE procedure to stop the workload capture:

BEGIN
	DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();
END;
/

To add filters to the workload use the ADD_FILTER procedure and to delete the filter use DELETE_FILTER.

Workload pre-processing

In this phase, information in the capture files will be preprocessed and create all the metadata needed for replaying the workload. This step can be resource and time consuming and it’s recommended to perform this step on a test system where you want to replay the database work load.

Copy the capture files from production to test server

It’s recommended to create a restore point, so that changes can be reverted after replay and same database used for other database work load tests:

SQL> CREATE RESTORE POINT before_replay1;

Create a directory on the test server where you want to copy the files:

mkdir -p /u01/app/oracle/rpl
Create or replace directory  db_replay_dir  AS '/u01/app/oracle/rply';
grant read, write on directory db_replay_dir to sys;
mkdir -p /u01/app/oracle/rpl
Create or replace directory  db_replay_dir  AS '/u01/app/oracle/rply';
grant read, write on directory db_replay_dir to sys;

Use the PROCESS_CAPTURE procedure to preprocess the captured workload:

BEGIN
	DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'DB_REPLAY_DIR');
END;
/

It’s important that the preprocessing is done with the same database versions. Users must copy the capture files from production to the test system.

Workload replay

In this phase users can replay the workload that’s captured and pre-processed. All the external requests which are recorded will be performed on the test system with same time and concurrency.

Use the INITIALIZE_REPLAY procedure to initialize workload replay:

BEGIN
	DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
			replay_name=> 'tst_prd_reply1',
			replay_dir => 'DB_REPLAY_DIR');
END;
/

Use the PREPARE_REPLAY procedure to prepare workload replay on the test database system:

BEGIN
	DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE,
					capture_sts => TRUE,
					   sts_cap_interval => 300);
END;
/

The synchronization parameter default value is SCN this means the COMMIT order will be preserved and replay actions will be executed only after all dependent COMMIT actions are complete.

Before you run the replay procedures make sure to estimate the number of clients, hosts necessary to replay the captured workload using wrc.exe (workload replay client):

wrc  mode=calibrate  replaydir=/u01/app/oracle/rply

wrc system/"S4Tv[\X.zRsEc_"@CCLIDRX mode=replay replaydir=/u01/app/oracle/rply

Use the START_REPLAY procedure to start a workload replay:

BEGIN
	DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/

Check Status:

SELECT id, name, STATUS FROM dba_workload_replays where name = 'cli_prd_reply1';

Analysis and Reporting:

In this phase users will perform detailed analysis of the reports from both the workload capture and workload replay phases. These reports will include information about errors, statistics about database time, average time spent, user call,  session info and data divergence.

For advanced analysis and comparison users can use AWR (Automatic work load repository) reports and SQL Performance Analyzer to compare the SQL tuning set from capture and replay.

Query the DBA_WORKLOAD_REPLAYS view to see the information about all the workload replays.

Use DBMS_WORKLOAD_CAPTURE.REPORT function to generate Workload capture report:

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

select DBMS_WORKLOAD_REPLAY.REPORT(replay_id=>&replayid, format=>'HTML') tt 
from dual;

DECLARE
	V_CAPID	NUMBER;
	V_REPORT	CLOB;
BEGIN
	V_CAPID  := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => 'DB_REPLAY_DIR');
	V_REPORT := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => V_CAPID,
				format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/

Where:

    dir specifies the directory which contains the workload capture files

    capture_id specifies the ID  related to the directory which contains the workload capture files

To get the capture_id use DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO function.

The report contains the information about workload capture such as filters, date, time, SCN of capture and overall statistics such as total database time captures, no of logins and transactions and any limitations due to version etc.

Use DBMS_WORKLOAD_REPLAY.REPORT function to generate Workload replay report:

DECLARE
	V_REPOTT	CLOB;
	V_CAPID	NUMBER;
	V_REPID	NUMBER;
 
BEGIN
V_CAPID:= DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => 'DB_REPLAY_DIR');
SELECT MAX (id)   INTO   V_REPID
	FROM	dba_workload_replays
	WHERE	capture_id = V_CAPID;
V_REPORT:= DBMS_WORKLOAD_REPLAY.REPORT(
				replay_id => V_REPID
				format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/

Where:

• dir specifies the directory which contains the workload replay files.
• replay_id specifies the ID  related to the directory which contains the workload replay files. To get the replay_id use DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO function.
• Formatspecifies the report format and three formats available.

• To Generate a TEXT report use DBMS_WORKLOAD_REPLAY.TYPE_TEXT
• To Generate a HTML report use DBMS_WORKLOAD_REPLAY.TYPE_HTML
• To Generate a XML report use   DBMS_WORKLOAD_REPLAY.TYPE_XML

The report contains the information about workload replay and capture such as job name, duration, status, time, path, database information, replay clients and overall statistics like total database time captured and replayed, no of logins, transactions, replay divergence, error divergence etc.

Best Practices for Database Testing

1. Avoid workload interference: Disable maintenance windows and background jobs to avoid workload interference. Background jobs may already exist on test system and so there is no need to duplicate them during replay. For example: auto optimizer stats collection, auto space advisor or auto sql tuning advisor.

• Filter out background monitoring activity: Filter out Enterprise Manager’s dbsnmp sessions or other monitoring and RMAN backup schedule sessions.

• Understand application workload profile details and limitations: Before using SQL Performance Analyzer (SPA) or Database Replay, understand application workload profile details and the database features used. Check whether the application is using features that are not supported by RAT

• Perform SPA testing prior to Database Replay: The goal of SPA testing to identify SQL level performance regression like optimizer plan changes or Exadata cells I/O behavior. This can help significantly the Database replay testing, especially to test Database upgrade or Exadata migration.

• Setup restore points to ensure a consistent replay start point: Setup guaranteed restore points using Database Flashback on the test database before starting database replay so that you will be able to repeatedly replay the captured workload from same point before the capture was taken. Alternatively reset database to point of capture by using either RMAN, Snapshot Standby or imp/exp, Data Pump, etc. Test database should be isolated from the production database.

• Run a test replay for a short duration of workload capture: When preparing a capture replay, you should test that a representative workload operates as expected to avoid wasting time if you find that a larger workload does not work later. This way, potential issues during replay will be easier to debug. As a rule of thumb we recommend Database replay of a capture of representative workload of a one hour duration. Once a one hour Database Replay run has worked satisfactorily, one can move towards a longer duration Database replay. Do not jump directly to a larger duration capture such as 12 or 24 hours before doing this. As far as possible, replicate production hardware and O/S in Test.

• Perform Workload Replay Analysis before performance analysis: For Workload Replay Analysis, compare replay to replay in the same environment and database server as far as possible. Review divergences first and then move on to performance analysis.

• Explore AWR and ASH data for comparison: At the end of the capture, always export the AWR data using DBMS_WORKLOAD_CAPTURE.EXPORT_AWR and import it in test replay Database using DBMS_WORKLOAD_CAPTURE.IMPORT_AWR. Importing AWR data enables detailed analysis of the workload and is also required if you plan to run the AWR Compare Period report on a pair of workload captures or replays.

• Inflight transactions : Need to be factored into replay analysis. Long running background jobs may start before capture starts and these may affect replay quality. The best way minimize inflight txns is to restart database, but it not mandatory or may not be feasible in most real-life scenarios. Given this, it is recommended to factor them into replay analysis and adjusting the capture window appropriately to encompass the interesting period that includes the job you want to test.

1. Database Replay Synchronization: The recommendation is first to run the replay with TIME based (synchronization => false). But each workload can have certain characteristic that more suitable for a particular synchronization type.