There are a number of tools available that can assist with testing performance and scalability of Oracle Databases and their platforms including Swingbench, HammerDB, Benchmark Factory for Databases, Apache JMeter, HP Load Runner (with SiteScope) and others. These all do a pretty good job and I’ve used them all at different times for different reasons. But if you want to record and replay a real production Oracle database and test it’s scalability and performance on against a test environment there is another way that’s worth considering.

As part of Oracle Database 11G Database Replay was introduced as an option that allows the recording of a production database workload and then replay of that workload against a development or test instance of the database. With 12c Oracle has enhanced the DB Replay capability to include additional options including Time Shifting and Workload Scale Up where multiple system peaks can be replayed at the same time, database consolidated replay, where multiple databases can be replayed against a single system to test out the new pluggable database and multi-tenant features and more. DB Replay is a great tool to test database system changes such as upgrades, migrations, re-platforming, patches, and other types of system configuration change.

The DB Capture and DB Replay process is recording database transactions so it is completely application agnostic. You can capture and replay any type of application database activities. This can save a lot of time during certain types of system changes as you don’t have to implement a complete application stack just to test changes to your database (unless you’re also making schema changes).

Based on a recent test exercise I was involved with I thought I’d share some tips that I’ve picked up and get some feedback on other peoples experiences.

• Be aware of time of day constraints in sql queries during capture. This may mean you need to adjust system clocks before replay and disable NTP time / date updates.
• Keep Sync Enabled – This ensures the integrity of the ordering of transactions and prevents unnecessary logical database contention
• Keep think time at 100%, at least until you have stabilised your test and you’re able to reproduce your captured duration predictably, think time can be adjusted if you’re replaying a test form a single instance database against an Oracle RAC environment.
• Follow generally accepted database best practices with regards to file system layout, number of database files, and CPU and memory configurations for your platform. Your platform and OS vendor may have specific best practice recommendations that should be taken into consideration.
• When testing in a virtualized environment for scalability, replay the database capture against multiple systems at the same time and scale up the number of workloads until you reach system bottlenecks
• Use AWR, ASH and workload reports to compare original source capture database to destination replay database. If replay test is same duration you should look for reduced response times, reduced resource utilization, such as CPU time, and reduced contention

Full documentation for DB Capture and Replay can be found in the Oracle Database Testing Guide. Satishbabu Gunukula has also written a good article titled Oracle Database Replay for your Workload Test.

Final Word

Benchmarks and synthetic workload load generators can only do so much to prove scalability and performance. When you need to test a production workload it’s good to be able to do a capture and replay it against a test system. It would be great to hear feedback from others that have used DB Replay and the tips and tricks that you’ve discovered.

—

This post appeared on the Long White Virtual Clouds blog at longwhiteclouds.com, by Michael Webster +. Copyright © 2014 – IT Solutions 2000 Ltd and Michael Webster +. All rights reserved. Not to be reproduced for commercial purposes without written permission.