Background:A client approached us to assist them with a Load and Performance Testing Project. This group were releasing a large number of customisations to their Oracle E-Business Suite implementation. They had scripted the tests using Load Runner and were planning a two day testing session. They wanted us to work with the Load Runner test administrators and development team, to monitor SQL processing during the test, identify problems and provide fixes for the SQL before the next test iteration began.

Summary of solution: We identified and fixed all of the SQL statements that were impacting performance. The final Load Test demonstrated a significant improvement in performance and load capacity.

Total Time from start to solution: 2 days   Total Charge Time: 12 hours

Workflow - how we got to the solution:

Customer	Sql Tuning Service
The client got in touch and explained their requirements. They were about to release a large set of customisations to their Oracle eBusiness Suite Implementation. The LoadRunner scripts and test data had been completed and they were ready to run the tests. Due to project constraints they now had only two days to run the performance tests. They wanted to be as productive as possible within these two days - running as many meaningful tests as possible and fixing problems between tests.
	We agreed that we could help and formulate a test-fix-test process. The DBA team would send us a report on activity during a performance test. We would analyse this test and report back to the team with a list of problem SQL, including the time it would take to tune each SQL statement. Together with the test team, we would agree on a set of SQL to tune for the next test.
The first performance test was run, the DBA team sent the database activity report for us to analyse. At the same time other specialist teams analysed their respective parts of the technology stack.
	We identified over twenty problem statements in the report. There were two SQL statements causing long wait times. We agreed to tune these two queries in time for the next test (scheduled to happen 3 hours later). We tuned the SQLs and sent them to the client to implement.
The development team implemented the code changes and a second test was run. The LoadRunner test report clearly showed improvements in the results directly related to the two tuned queries. The DBA team sent us the database activity report of this second test.
	With the logs from the second test, we were also able to confirm that the two worst problems had been fixed. Given that the other 17 SQL statements identified were similar in impact and tuning effort, we agreed with the team that we would start tuning them and send them as soon as they were tuned. This allowed the team more flexibility in scheduling tests. We continued to tune.
The LoadRunner administrators repeated test iterations in the same manner. At the end of the two days, all twenty SQL queries were tuned and implemented. These queries were verified in the LoadRunner results and database activity reports to have been fixed. We also suggested an alternative design for one component which was exposed as a bottleneck once the other queries had been tuned.