11g new feather (9)

New System Testing Tools

As a DBA, one of the most bedeviling problems that I’ve regularly faced is to be able to predict accurately how the next set of changes to the database’s application code, database patch set, or hardware configuration will affect that database’s performance. That usually meant purchasing a relatively expensive third-party package (e.g. Mercury Interactive’s LoadRunner) to generate a sample workload against the database using the next version of the application code, and then comparing the results against baseline performance for the current application code version.

Fortunately, Oracle Database 11g has come to the rescue with two new utilities that offer monumental strides forward in system testing:

• Database Replay. Database Replay can capture generated workloads from production systems at the database level. Therefore, it’s no longer necessary to run actual application code to duplicate the load on the database, and this also improves accuracy of the simulated workload because it limits or removes other factors like network latency. These captured workloads can then be replayed on a quality assurance database so that the impact of application changes, software patches, and even hardware upgrades can be measured accurately. This feature is especially valuable in detecting performance issues that could potentially hamstring a production database’s performance that might go otherwise undetected until well after changes have been deployed.
• SQL Performance Analyzer. A robust complement to the Database Replay facility, the SQL Performance Analyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPA provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a performance baseline before a major database or system change, make the desired change to the system, and then replay the SQL workload against the modified database or configuration. The before and after performance of the SQL workload can then be compared with just a few clicks of the mouse. The DBA only needs to isolate any SQL statements that are now performing poorly and tune them via the SQL Tuning Advisor.