ThatJeffSmith

Capture and Replay Oracle Activity

Sound familiar? Perhaps you have heard of Oracle’s Real Application Testing (RAT) (docs)?

An excellent feature, although you will pay for it. If you need a cheaper alternative, then Quest Software might have what you are looking for in Benchmark Factory for Databases (product details).

New for v6.5, Benchmark Factory (BMF), now assists you in taking a snapshot of your data and activity, and steps you through replaying it on the same or another instance. You can additionally filter out the activity by user, module, program, etc. We also support Oracle versions 8i through 11gR2 and require no additional packs. The software will even work on just the Standard Edition of Oracle.

A wizard steps you through the process of collecting your data and transactions. Once completed, it allows you run that activity against that data on any instance of Oracle.

You will need to map a server directory so it is available to the Windows machine where BMF is running. Your UNIX admin can help you implement this via SAMBA (docs) or something similar. This allows BMF to access the large DMP and trace file(s) directly without having to FTP them across the network.

You have the option of using STATSPACK or AWR to generate performance reports. STATSPACK (docs) is free, AWR (docs) is not.

After you select the data and users you want to perform the caption, you can optionally define filters. This allows you to discard activity that is not relevant to your test.

You can run the capture immediately, or schedule it to run at a later date. Please note that BMF will temporarily direct DATAPUMP and Trace output to the shared directory you created earlier. It is important that this directory has enough space and can handle the IO demand. If Oracle cannot write to the drive efficiently, it will impact the performance of the database. I recommend you run a simple capture first to help you determine the impact on your system. Or in other words, run a test for 5 minutes before you run one for 5 hours!

The first thing BMF will do is export your data via DataPump (or EXP if using Oracle v8 or v9.) It will then initiate a system-wide trace for the defined amount of time.

You can monitor the sessions and active traces that are running during your capture.

Once the capture is complete, you will see the scenarios that have been captured. You can then work with your BMF project to launch your tests.

You may inspect each query for the transactions, and substitute your own latencies and bind variables – this is optional.

When you are ready to run your test, you simply need to launch the BMF job against the database instance you wish to test. BMF will load the data and replay the transactions as they were recorded. Afterwards you can review the BMF performance results alongside the STATSPACK or AWR findings.

As the test is running, you can monitor the progress of the test and its performance.

You are just a click away from the detailed analysis!