News, views, and items of interest on IBM's Db2 database management system and mainframes.

Wednesday, July 01, 2015

Influencing the DB2 Optimizer: Part 1

Today is July 1, 2015 - mid way through the year and the beginning a fresh, new month. With that in mind, I'm kicking off a new series of blog posts here on the various methods of influencing the DB2 Optimizer's access path decisions. The bulk of the material will be excerpted from my book - DB2 Developer's Guide - which just so happens to be on sale for 40% over the 4ht of July holidays at InformIT if you're interested in picking up a copy.

Before going into the various methods that can be used to alter access paths or influence access path selection, let's first take a moment to laud the DB2 optimizer and the fine minds that built it. The DB2 optimizer is one of the most intricate pieces of software
on the market. It does an admirable job of optimizing SQL requests. To achieve
this level of success, the optimizer contains a great deal of
performance-specific expertise. For example, the optimizer estimates both
elapsed times and CPU times when choosing an access path. When a SQL statement
is rebound, the optimizer might choose a new access path that increases CPU
time but decreases elapsed time.

Now many shops may choose to enhance elapsed time at
the expense of additional CPU use because elapsed time has a measurable effect
on user productivity. In other words, it can be good to trade off CPU cycles for
user satisfaction, and the DB2 optimizer attempts to accomplish this. Of
course, if both CPU and elapsed time can be reduced, the optimizer tries to do that, too.

But the DB2 optimizer is not infallible. Sometimes the
application analyst or DBA understands the nature of the data better than DB2. And that is the reason that there are ways to influence the optimizer into choosing an access path
that you know is a better one but the optimizer thinks is a worse one.

As the
functionality and complexity of the optimizer is enhanced from release to
release of DB2, the need to trick the optimizer in this way invariably diminishes. But until the day when software is perfect, the need to tweak it will remain (and I'm pretty sure that will be the case throughout my lifetime).

There are five ways to influence the optimizer’s access path
decisions:

Updating DB2 Catalog statistics

Standard,
DB2-based methods

Tweaking SQL statements

Specifying the OPTIMIZE
FOR n ROWS
clause

Using OPTHINT
to indicate that an access path in the PLAN_TABLE should
be chosen

Over the course of the ensuing weeks, we will examine each of these methods, and more. So stay tuned as we delve into the techniques at your disposal to influence the choices made by the DB2 optimizer.