Firstly, I would like to point you to my colleague’s great blog entry on Oracle’s Real Application Testing. The link of which can be found here.

It prompted me to build two videos to demo Real application Testing and write this compact blog entry to support them. The first video demos the capture process, whilst the second video demos the replay of that capture. The scripts used for both capture and replay can be found in this tar file.

Demo Setup

In the demo I have two VMs. A source VM and a target VM. The source VM hosts the database we will capture a workload from. The target VM hosts the database we will replay on. The target VM has more CPU and memory to emulate a migration process where a database is moved onto more powerful hardware. The target database is a copy of the source database (taken using Data pump). Both the source and target databases are running 19c. Real Application Testing capture is supported from 10g R2 and the replay process supported from 11g R1 onwards, but from 19c it is possible to capture and replay the workload from within an individual pluggable database. The demo highlights this newer ability, by capturing and repaying within pluggable databases. Certain parts of the videos have been speeded up for convenance.

Capture

In the capture process I run a Swingbench load against a pluggable database on the source database.  I then use Real Application Testing to capture this load.

I have listed below the main PL/SQL packages and views used during the above video. They are listed in the sequence they are used in the video.

  • DBMS_WORKLOAD_CAPTURE.ADD_FILTER  

                  This procedure adds a filter to capture a subset of the workload. In our case we only want to capture events from the Swingbench schema.

  • DBMS_WORKLOAD_CAPTURE.START_CAPTURE

                  This procedure starts the capture process.

  • DBA_WORKLOAD_CAPTURES

                   A view that provides information on the capture process.

  • DBMS_WORKLOAD_CAPTURE.REPORT 

                   This procedure will generate the post capture report.

  • DBMS_WORKLOAD_CAPTURE.EXPORT_AWR  

                   This procedure exports the AWR snapshots associated with a given capture ID.

For full package details see here

Replay

In the replay video I show the replay of the captured load on the target database. Once the replay is complete I use the replay reporting tools to analyse the difference between the two workloads (captured and replayed)

I have listed below the main PL/SQL packages and views used during the above video. They are listed in the sequence they are used in the video.

  • DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY

This procedure loads data into the replay system that is required before preparing the replay can be done. 

  • DBA_WORKLOAD_CONNECTION_MAP

       This view shows the connection mappings.

  • DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION

This procedure remaps the captured connection to a new one so that user sessions can connect to the database in the desired way during workload replay.

  • DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY

This procedure puts the database state in PREPARE FOR REPLAY mode.

  • WRC

This is the client process that will run the replay against the database

  • DBMS_WORKLOAD_REPLAY.START_REPLAY

This process starts the replay.

  • DBA_WORKLOAD_REPLAYS

A view that provides information on the replay process.

  • DBMS_WORKLOAD_REPLAY.REPORT

This procedure will generate the post replay report.

For full package details see here