One of the developers was astonished at the power of SQL Profiles.
They could not believe that I could reduce the running time of a SQL statement from several days (actual runtime is unknown because it was stopped before completion) down to about fifteen (15) minutes by doing about ten (10) minutes of work with the SQL Tuning Advisor.
I find that many DBAs and developers are unaware of these tools. Even if they are aware of them, they are wary of these tools. They would rather do analysis of a SQL performance problem by hand: considering indices, SQL plan hints, etc..
But from today’s productivity requirements, this manual method is sheer madness. There is not the maintenance nor development budget for such luxury except in critical cases.
I have been able to tune about twenty (20) SQL statements in a day using these tools. The application concerned had zero (0) maintenance budget and huge performance problems. I just spent weeks tuning several hundred SQL statements in order to get the application performance down to an acceptable level.
From an operational DBA perspective, the SQL Profile and SQL Tuning Advisor are essential and unremarkable. I usually implement a SQL Profile as a matter of course. Other DBAs freak out at this attitude. They still consider SQL performance to be subject to change control with a full development cycle including QAT. But the operational requirements demand that the problem be fixed now, not in several weeks’ time.
However, SQL Profiles can be integrated into the development cycle. We have done this by implementing SQL Profiles during QAT. Once QAT was completed and accepted, these SQL Profiles were migrated into the production environment (see 17.5.5 Transporting a SQL Profile).
Yet Another OCM 