Background: A Client came to us with a Discoverer performance problem - users were complaining that a financial report would sometimes take ten minutes and sometimes take hours to generate. Their support team had confirmed the problem and noticed that the report ran fine when users where searching on a specific criteria, such as the customer name, but it took much longer when region or date was used to filter results. The Client wanted the report to run at a consistent and acceptable performance level for all user parameters. They were not sure how they would achieve this.

Summary of solution: We tuned the SQL to ensure a consistent performance of no more than twelve minutes - irrespective of how many or which parameters were used. The solution only required a small change to the code of the report, no new tables or indexes were required.

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

Workflow - how we got to the solution:

Customer	Sql Tuning Service
The Client submitted the SQL and PL/SQL used in the Discoverer Report on our homepage and noted that they wanted the report to run at a consistent and acceptable performance level for all user parameters. They were running Oracle Discoverer 4 and Oracle 9i
	We analysed the SQL - there were fourteen user parameters which could be set to filter data. We sent a small script to the Client to run - this script gathers information about the tables and indexes used in the SQL query.
The Client ran the script - it took a few seconds to run, and they emailed us the output.
	Having reviewed the script output, we arranged a thirty minute call with the Client to discuss the typical usage pattern of the parameters. Following this conversation, we were able to offer a solution that would ensure the report ran in less than 15 minutes for all combinations of the fourteen parameters. This charge would be a fixed price of 4 hours work. The standard Sql Tuning Service guarantee applied; a fixed price and no payment would be required if we did not meet this performance level.
The Client agreed to engage our services.
	We tuned the report code using our Oracle 9i Test-Rig. Changes to the pl/sql and sql of the report were necessary to ensure a consistent runtime for the report. Once the tuning was completed, we emailed the modified code back to the Client.
The Client tested the Discoverer Report with all parameter options. They were satisfied with the performance - the quickest the report ran was in just under 3 minutes and the longest was just over 12 minutes.

Tuner's Comment: There is real challenge in writing SQL for Oracle Discoverer and other report tools. Generally there is one SQL statement which will generate the data and allow a user to set a large number of filters or parameters. As well as being functionally correct, this SQL must be designed to work effeciently no matter which parameters are chosen at runtime.