Reflections on Tom Kyte’s Approach to Database Tuning


Tom Kyte stresses that application tuning is the best way to deliver performance improvement when he answers a question in On Connection Pools, Cursor Differentiation, and Optimal Ordering:

The questioners wrote, “…application-level tuning is not an option” and that they do not have the resources to do it, but I maintain that they have to do it. “Tuning a database” will deliver single- or even double-digit percentage decreases in application runtime, but these performance issues need orders-of-magnitude application runtime improvement. There is no magic here. If getting the response you wanted didn’t require looking at the application and its overall design and implementation, no one would ever look at the application, its design, or its implementation.

If you want orders-of-magnitude decreases in runtimes, you will be looking at the application and its design and implementation. If you want things to run 10 percent faster, you might (and I stress the word might) get lucky and be able to tune the database without touching the application. It is exceedingly rare—and getting rarer as the database becomes more self-tuning.

The question ends, “…suggest a way to improve performance (any way except SQL tuning). We are at our wit’s end.” I can imagine that you are at your wit’s end. A request like this is sort of like being asked to go from Europe to America on foot, blindfolded, in one day. Tuning an application without being able to touch the application is an impossible job. I suggest that you start by rejecting that tuning proposition and realizing that you are almost certainly going to have to touch the application.

Italics in orginal
Emphasis Mine

This is why I was suspcious of the claims made by IBM when they posted that Reliance cuts database total cost of ownership in half with IBM DB2, especially when IBM fixed the data model before doing the conversion from Oracle to DB2.

And yet, there are a lot of developers and their managers who think bad design and code can be fixed by tuning the database, and buying more hardware.

There is really no subsitute for good design.

Advertisements

One thought on “Reflections on Tom Kyte’s Approach to Database Tuning

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s