Communications - October 2010

There is a general principle at work here that humans who optimize know: when everyone is happy except for you, make sure your local stuff is in order before you go messing around with the global stuff that affects everyone else, too.

This principle is why I flinch whenever someone proposes to change a system’s Oracle SQL*Net packet size when the problem is really a couple of poorly written Java programs that make unnecessarily many database calls (and, hence, unnecessarily many network I/O calls as well). If everybody is getting along fine except for the user of one or two programs, then the saf-est solution to the problem is a change whose scope is localized to just those one or two programs.

Efficiency. Even if everyone on the entire system is suffering, you should still focus first on the program that the business needs fixed. The way to begin is to ensure the program is working as efficiently as it can. Efficiency is the inverse of how much of a task execution’s total service time can be eliminated without adding capacity and without sacrificing required business function.

In other words, efficiency is an inverse measure of waste. Here are some examples of waste that commonly occur in the database application world:

˲ ˲ A middle-tier program creates a distinct SQL statement for every row it inserts into the database. It executes 10,000 database prepare calls (and thus 10,000 network I/O calls) when it could have accomplished the job with one prepare call (and thus 9,999 fewer network I/O calls).

˲ ˲ A middle-tier program makes 100 database fetch calls (and thus 100 network I/O calls) to fetch 994 rows. It could have fetched 994 rows in 10 fetch calls (and thus 90 fewer network I/O calls).

˲ ˲ A SQL statement (my choice of article adjective here is a dead giveaway that I was introduced to SQL within the Oracle community) touches the database buffer cache 7,428,322 times to return a 698-row result set. An extra filter predicate could have returned the seven rows that the end user really wanted to see, with only 52 touches upon the database buffer cache.

Certainly, if a system has some glob-
al problem that creates inefficiency for
broad groups of tasks across the sys-
tem (for example, ill-conceived index,
badly set parameter, poorly configured
hardware), then you should fix it. Do
not tune a system to accommodate pro-
grams that are inefficient, however. (Ad-
mittedly, sometimes you need a tour-
niquet to keep from bleeding to death,
but do not use a stopgap measure as a
permanent solution. Address the ineffi-
ciency.) There is a great deal more lever-
age in curing the program inefficiencies
themselves. Even if the programs are
commercial off-the-shelf applications,
it will benefit you more in the long run
to work with your software vendor to
make your programs efficient than it
will to try to optimize your system to run
with an inherently inefficient workload.