Oracle RDBMS and Tools Services - Performance Testing Framework

The Problem

The client had no procedures for perfomance testing of individual peaces of code. Formal performance tests were carried out but only after UAT had been completed, leaving little time for fixes that required code re-work. The developers were largely unfamiliar with the range of tools available to assist in testing discrete sections of code for performance.

The Remit

To produce a toolkit that would easily enable developers to gather a range of outputs for test runs that would then be analysed performance. The toolkit had to be fully documented to enable new developers to use it.

The Solution Outline

• Since the Oracle databases in Development through to Test and Production resided on UNIX hosts, the solution was UNIX centred.
• A suite of shell scripts were designed and built that gathered TKProf, STATSPACK, PLSQL Profiler, V$MYSTAT, vmstat and iostat outputs.
• These were incorporated into a single wrapper shell script that also invoked the PL/SQL code being tested.
• The developer would write a SQLPLUS script containing the code he wanted to run for testing (usually a call to a PLSQL package)
• The wrapper shell script would be run specifying the login and developer script name.
• The wrapper script would execute the developer script and wrap around the various stats gathering scripts as appropiate.
• Output of all the stats would be gathered in a separate folder for analysis.

Benefits Realised

• Developers could test changes within the unit testing phase, and not have to wait for formal tests.
• Developers were exposed to the nuances of performance tuning, instead of it being left to a few experts.
• In some cases the need for training course was highlighted.

Further Information

This framework is available as a product. Please get in touch if you are interested in this toolkit.