# Computing percent of transactions that sort

#### Barry Spiegel

Computing percent of transactions that sort
I've seen a few different methods for computing the percent of transactions
that perform sorts. Some recent statistics I've collected in a data
warehouse environment that I support make me wonder if there is a consensus.

A recent seminar noted that you should use:

(TOTAL SORTS * 100) / (COMMIT STATEMENTS ATTEMPTED + ROLLBACK
STATEMENTS ATTEMPTED)

For some time periods, I find percentages well over 300%, which seems to
indicate that single transactions perform loads of sorts (possible) or that
I've shortchanged the denominator.

The System Monitor Guide and Reference doesn't use the term 'transactions',
but instead suggests that the total number of statements can be computed
with:

(DYNAMIC SQL STATEMENTS ATTEMPTED + STATIC SQL STATEMENTS ATTEMPTED
+ FAILED STATEMENTS ATTEMPTED)

However, for the same time periods noted above, this alternate denominator
still results in percentages over 100% -- better but still makes me wonder.

The Guide also notes that the total number of units of work can be computed
with:

(COMMIT STATEMENTS ATTEMPTED + ROLLBACK STATEMENTS ATTEMPTED +
INTERNAL COMMITS + INTERNAL ROLLBACKS)

As you would imagine, this results in pretty much the same high percentages
as the first method.

So, what would you recommend as a good method for computing the percent of
transactions that sort?

Barry

> Barry Spiegel
> EDS - New York Solution Centre
> 25 Northpointe Parkway
> Amherst, NY 14228
> Phone 716-564-6614 (8-373)
> Fax 716-564-6775
> Pager 716-448-6279
>
>

#### Scott Hayes

Re: Computing percent of transactions that sort
(in response to Barry Spiegel)
"SORT is a FOUR letter word"

Hi Barry!

Some of those formulas, in particular the first, look
very familiar. <grin>

If a "Transaction" is a voluntary, intentionally completed,
action, then COMMIT STATEMENTS ATTEMPTED + ROLLBACK STATEMENTS
ATTEMPTED would accurately reflect the number of application
intended transactions ("make it so" or "cancel" - commit/rollback).

If you include INTERNAL COMMITS and INTERNAL ROLLBACKS in your
transaction count, you are no longer looking at performance from
becomes contaminated with involuntary commits and rollbacks
performed at the whim of the DB2 engine.

For the percentage of transactions sorting, the first formula
you have is correct. Percentages > 100% indicate that the
typical transaction is doing more than one sort. You are not
alone in seeing 300+ % transactions sorting, there are probably
many folks out there that could top that. For some SQL commonly
found in today's ERP/CRM/E-biz applications, check out the
explains and sometimes you'll see several sorts performed for a
single statement.

Another valuable way to look at the data is "Sorts per Transaction",
or: TOTAL SORTS / (COMMIT ATTEMPTED + ROLLBACK ATTEMPTED)
Numbers of .10 or less would be outstanding, suggesting that only
1 of 10 transactions does a sort.

Since you mentioned total number of statements (good formula below),
you could also look at "Sorts per Statement":
TOTAL SORTS / TOTAL STATEMENTS

For high performance OLTP transactional applications, the percentage
of SORTS that overflow should be very small, less than 1%. Most
DBAs are successful at avoiding overflows by pumping up the SORTHEAP.
This transfers the resource bottleneck from one of physical I/O in
TEMPSPACE, to one of enormous CPU consumption, which is why we find,
during many of our performance audits, that CPU burn rates are
extremely, and often unnecessarily, high.

Take a careful look at all the performance information that is
available from the DB2 UDB performance instrumentation. Sort time
is specified in milliseconds. Lock time is specified in milliseconds.
Bufferpool I/O time is specified in milliseconds. Direct I/O time
is specified in milliseconds. Just about everything is measured in
milliseconds, EXCEPT CPU time. CPU time is specified in SECONDS.
Multiply CPU seconds by 1000 to get CPU milliseconds. For each
resource, sorts, locks, I/O times, and CPU, for each application
that you run, you may be absolutely startled to learn where the
time really goes. Are your applications constrained by SORTS?
CPU? I/O? We call this analysis "Transaction Composition Analysis",
or TCA for short. And we do it everyday, for every application.

You will also note, if you look carefully at the DB2 instrumentation,
that CPU time is not reported by snapshots. Most tools on the market,
and most DBAs, only look at snapshots (they're easy for one reason).
CPU time is reported by DB2 UDB events, and only Connection-MINER(TM)
and SQL-GUY(TM) from Database-GUYS(TM) report and leverage CPU
measurements. Trying to tune DB2 UDB with only snapshot information
is like trying to tune an automobile with only a screwdriver.

To emphasize the importance of looking at the right performance data
and the importance of CPU time, there's a company with an IBM RS/6000
S80 w/ 18 CPUs... after trialing our tools for a couple weeks, not only
did they make it through the holiday shopping online crunch, but they
are presently contemplating returning excessive CPU capacity to IBM.

Exceed your need for Speed. Get the GUYS, and crank up your
revenue / minute rates. (That's roughly COMMITS divided by
shopping days divided by 1440 <grin>).

Happy New Year everyone! Have a safe and fun 2001!

Warmest regards,
Scott

See what our customers are saying:
http://www.database-guys.com/guestbook/guestbook.html

http://www.database-guys.com/solutionsindx.shtml

IDUG North America in Orlando where I'll be presenting an all
new talk "SORT is a FOUR letter word!".

> I've seen a few different methods for computing the percent of transactions
> that perform sorts. Some recent statistics I've collected in a data
> warehouse environment that I support make me wonder if there is a consensus.
>
> A recent seminar noted that you should use:
>
> (TOTAL SORTS * 100) / (COMMIT STATEMENTS ATTEMPTED + ROLLBACK
> STATEMENTS ATTEMPTED)
>
> For some time periods, I find percentages well over 300%, which seems to
> indicate that single transactions perform loads of sorts (possible) or that
> I've shortchanged the denominator.
>
> The System Monitor Guide and Reference doesn't use the term 'transactions',
> but instead suggests that the total number of statements can be computed
> with:
>
> (DYNAMIC SQL STATEMENTS ATTEMPTED + STATIC SQL STATEMENTS ATTEMPTED
> + FAILED STATEMENTS ATTEMPTED)
>
> However, for the same time periods noted above, this alternate denominator
> still results in percentages over 100% -- better but still makes me wonder.
>
>
> The Guide also notes that the total number of units of work can be computed
> with:
>
> (COMMIT STATEMENTS ATTEMPTED + ROLLBACK STATEMENTS ATTEMPTED +
> INTERNAL COMMITS + INTERNAL ROLLBACKS)
>
> As you would imagine, this results in pretty much the same high percentages
> as the first method.
>
> So, what would you recommend as a good method for computing the percent of
> transactions that sort?
>
>
> Barry
>
> > Barry Spiegel
> > EDS - New York Solution Centre
> > 25 Northpointe Parkway
> > Amherst, NY 14228
> > Phone 716-564-6614 (8-373)
> > Fax 716-564-6775