An often overlooked feature of Oracle is Real Application Testing, called RAT.
RAT is by far the best way of testing the impact of changes to the database (not applications). This can be anything from upgrading to new hardware, upgrading the database release, adjusting database parameters, creating new optimizer statistics, adding indexes, partitioning etc.
Before the emerge of RAT, companies using Oracle had to write and execute extensive testplans, involving a number of people, spending many manhours. Or the opted for the “just do it” approach and hoped for the best. I once worked at a very large customer where I wrote a number of scripts that recorded the SQL’s from the SQLAREA in the database memory, and the execution plans of these. We then upgraded a clone of the production database, and checked the execution plans again. Although this approach proved to be very valuable in the end, it had a number of issues. The most important one being that we did not have the bindvariables, so we had a significant amount of uncertainty resulting in some manual processing/checking.
This all changes with RAT, which is able to remove the uncertainty, since it is able to record all relevant information and rerun the captured workflow over and over.
What RAT does is, to record all calls to the database for a period of time (1 hour, 3 days, 1 week…) and then re-executed these on a copy/clone of that same database. The result is then measured by overall efficiency, which is broken down to which statements/calls are performing better and which are performing worse. Typically, You will dive into the statements performing worse, make some ajustments and re-execute the test over again. This can be repeated untill You are satisfied with the outcome.
The beauty of RAT is, that it can allow a DBA to upgrade a database (or move to another platform etc etc) knowing the outcome before actually doing it in production. There will be no more hoping and guessing that it will work, and no more days with poor performance following an upgrade/change. Also, since the test is made with an actual recording from the real production system, You do not need a bunch of testers with a detailed testplan etc. In many cases, a single DBA can do this. (But if You have special areas of Your applications that were not used during the recording, these still need manual testing though).
RAT can capture the workload on an Oracle 9.2 (and higher) database for replaying on an Oracle 11 database.
Doing a RAT test can be broken down into these steps:
- Capture the workload on production.
This creates files on the database server, with information needed for replay. It includes wall clock time, sql text, bind variables etc. - Process the workload.
This creates all the needed metadata for replaying the workload. - Restore a backup of the production database database, to the time where the workload recorded was started.
- Change/upgrade the restored database (or do whatever change You will test, it could also be new hardware).
- Replay the workload on the restored database.
- Analyse the replay of the workload.
- Fix possible issues and repeat from step 3) – if needed.
It should be obvious that most of these steps can be done by a single DBA, although step 4+7 might involve developers etc., depending on what needs to be changed.
Oracle RAT is a an licensable option for the Enterprise Edition of the database, it is not availible for Standard Edition.
Conclusion: RAT is a tool that in a very easy way enables us to foresee how a change will impact on the database, and in a similar way, it provides us with an easy way of testing possible fixes, before going into production. The old way of just doing it and “hoping for the best” is not needed any more.
If you would like more information on the Oracle RAT, feel free to contact our support team at +45 70 272 247 or fill out the contact form.