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.
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 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!
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.