When I started working with Oracle in 1990 there weren’t many SQL tuning tools available. At most you had the Oracle supplied tools which allowed you to capture an explain plan and minimal tracing. Most SQL tuning consisted of either encouraging the use to a particular index by making sure the column in the index was on the leading edge of the index or, defeating the use of the index through null concatenation to character values or adding zero or multiplying by one for numeric values. I can’t recall what access paths were actually available but not much more than full table scan or nested loops I am sure. 

Gradually Oracle and other database providers added additional methods of optimization by way of additional access paths such as sort-merge-join, hash-joins, and various new types of indexes. Perhaps it is just my personal bias but Oracle seems to be one of the first to come out with the new features, then the others follow. As an example, bitmap indexes and bitmap-join indexes where added to Oracle and in some cases still aren’t available in other systems. Of course the number of tools for tuning databases has also increased, both those provided by the vendors and those provided by third-party vendors such as Quest.

Now tuning has become a fairly complex process where you need to figure out the best index types, the best access paths and the best table order in which to perform joins. Most databases provide a form of cost-based optimization where a numeric cost is associated with each possible path and the one with the lowest cost is utilized automatically. Most of these cost models utilize IO cost (as a function of time required) as the predominate contributor to the time required to resolve a query and return results. Now, with larger and larger memories and faster CPUs some are moving to a more CPU/memory based costing model. 

In Oracle by simply taking system statistics (IO timings for single and multi-block reads and CPU speed values) the optimizer will calculate both IO and CPU/memory based costs and utilize the path with gives the lowest overall cost. However, using cost-based optimization means that the various statistics required for the cost calculations must be kept up-to-date; row counts, block sizes, index width and depth and many other statistics will determine the cost of a particular SQL statement. Without accurate values for these parameters Oracle will make bad choices for access paths and indexes as will other database systems that utilize a cost based optimizer.

The DBA and developer must work together to ensure that proper indexes and SQL techniques are utilized to guarantee optimal application performance. Any tools utilized to perform development and tuning operations must work together seamlessly allowing not only monitoring of SQL performance but analysis of tuning alternatives and the ability to test alternatives in a database safe manner.