Background: Many of our clients send us a list of SQL statements to review each month. These SQL statements are the longest running, or heaviest consumers of resources on their system. This list can be compiled from standard database monitoring tools, or we can send you a script that creates this 'top 10' report.

How does it work: The example below is typical of how we work these lists:

Customer	Sql Tuning Service
A client submitted a report of the 'top 10 worst' SQL statements. They were running Oracle 10g and used Enterprise Manager to generate a report of SQL Statements which had the highest CPU and Disk usage over the previous four weeks.
	We analysed the ten SQL statements in the report to see if any of them would benefit from optimization. We found two queries that could be tuned. One SQL statement was taking 4 minutes and being run every ten minutes. The other was a batch job which was running 3 times a day and taking 2.5 hours. We went back to the client with a small script to get some more information about the tables used in these two queries.
The client ran the script. It took less than a minute to run, and they emailed us the output.
	With this extra information we were able to offer the following: - Tune the first query to run in less than 1 minute for a charge of 2 hours work - Tune the batch query to run in less than 80 minutes, for a charge of 5 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.
The client agreed.
	We went to work, tuning the two queries on our Oracle Test-Rig. Once we were happy with the tuned version, we emailed the modified SQL statements to the client for testing. No table or index changes were required.
The tests were successful. The first query ran in under one minute, and a large reduction in CPU usage was detected in later reports.The other query ran in 40 minutes.

Tuner's Comment: It is good practice to become familiar with the ten highest consuming SQL statements on your system. With this familiarity, you will quickly recognise a new entry on the list, an indication that something has changed on your system and must be investigated. Perhaps new applicaton code has been released, or new data volumes have been loaded.

It is also important to remember that most of the SQL statements in your top 10 list will have nothing wrong them - they are on the list because they are the most frequently executed and probably the most important application queries. In the example above, eight of the statements were running optimally and would not benefit from further tuning.