Background: A Client came to us with a performance problem with a jsp page. The page, a data entry screen, was taking over sixty seconds to render its content. Both the Java development team and Oracle team had looked at the problem, but could not see any problems with the Java or SQL code. They asked us to look at this page to see if any improvements could be found.

Summary of solution: We found that both the Java and SQL code did appear to be written efficiently, but too much data was being retrieved and processed by the jsp page. By merging the business logic from the Java code, into the SQL code, we were able to significantly reduce the workload - the screen took less than five seconds to render.

Total Time from start to solution: 1 days   Total Charge Time: 3 hours

Workflow - how we got to the solution:

Customer	Sql Tuning Service
The client submitted the SQL that was run from this jsp page.
	We analysed the SQL and asked the Client to run a small script which collects information about the tables and indexes used in the query and sets up a trace of the session for logging.
The client ran the script and test. They emailed us the output.
	We could see in the logs that the SQL was efficient, but noticed that a lot of data was being retrieved. Looking at the jsp code, we could see that most of the time was spent in the jsp code operating on this data. We offered to provide a solution that would render the screen in less than 10 seconds, for a fixed charge of 3 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 accepted the offer.
	We worked on the solution. We were able to move the logic from the jsp directly into the SQL so that we could reduce the data output and thereby the amount of processing required. We sent our modified jsp code to the Client for testing.
The development team tested the new code - it now took less than 5 seconds to process the screen.