Tuning for rollbacks?

[renny]

Dear Oracle/DB2 DBAs,

I'm looking for advice on tuning Oracle 9i and DB2 8.1 for rollbacks.

I have a number of developers, who each build and test our
client/server database application privately on their local machine.
Each developer's machine has:
* an OS, either Windows 2000 or XP; and
* a database, either Oracle 9i or DB2 8.1.

The developers have a JUnit test suite which runs several thousand
tests and which takes over an hour to complete. I'm currently looking
at ways to trim down the execution time of the JUnit test suite on
developers' machines.

Each test in the suite effectively does the following:
* setUp - start a database transaction
* test some application functionality
* tearDown - rollback the database transaction, effectively "restoring"
the database state ready for the next test.

The rollback at the end of each test essentially means that the tests
are independent and can be run in any order (or moreover tests can be
added or removed without affecting other tests).

I've observed though that if I change the rollback to be a commit, then
the tests run faster (although some functional checks now fail, as
tests start to "interfere" with one another). We want to preserve the
independence of our tests and so wish to keep the rollbacks - but makes
me wonder whether the rollbacks can be "tuned".

I'm assuming (and please forgive any naivety on my part) that the
default configuration of Oracle/DB2 is to expect commits to be common,
and rollbacks to be rare, and as such by default are tuned to perform
better for commits.

Is my assumption correct? If so, are there any easy tuning parameters
my developers can apply, which would help the thousands of rollbacks
perform any better?

Any help greatly appreciated (and apologies in advance if the
information above is too scant).

Many thanks,

Renny Barrett
renny _at_ eircom _dot_ net

[sybrandb]

In Oracle, commit means: write a commit marker.
Rollback means: read the associated rollback segments and update the
corresponding database blocks.
This should explain why a rollback is 'slower' and why your method is
very strange.
And no: you can't 'tune' rollback, apart from tuning your rollback
segments, as rollback ought to be very rare, and not by design.
--
Sybrand Bakker
Senior Oracle DBA

[renny]

Hi Sybrand,

Thanks for the response.

This should explain why a rollback is 'slower' and why your method is
very strange.

Yes, I agree what we're doing with our test suite is a perverse use of
a database - from your reply it would seem that the slow performance of
rollbacks compared to commits is something we'll have to live with.

Kind regards,;

Renny

[m0002a]

re***@eircom.net wrote:

Hi Sybrand,

Thanks for the response.

This should explain why a rollback is 'slower' and why your method is
very strange.

Yes, I agree what we're doing with our test suite is a perverse use of
a database - from your reply it would seem that the slow performance of
rollbacks compared to commits is something we'll have to live with.

Kind regards,;

Renny

For DB2 you can increase the size of the LOGBUFSZ (the log buffer). I
would set it to 256 or 512 pages for an OLTP application even if you
were not doing a lot of rollbacks. This memory comes out of the DBHEAP,
so you will need to increase that by a corresponding amount (all other
things being equal).

[Umberto]

It's not that commits are better tuned than rollbacks. For every
database (Informix, DB2, Oracle,...) changes are done immediately, so a
commit simply marks the transaction as ended and frees locks/advances
SCN. A rollback must put everything back again as it was before,
undoing the modifications, so it's evident that a rollback will always
be slower than a commit and there is no solution to that. Obviously,
should a crash occur before a commit, pending transaction is rolled
back at instance startup, because it's not marked as committed. That's
the function of redo logs.

Basically, substituting commits for rollbacks, you have made tests
twice faster, eliminating the rollback part.

[DA Morgan]

re***@eircom.net wrote:

Hi Sybrand,

Thanks for the response.

This should explain why a rollback is 'slower' and why your method is
very strange.

Yes, I agree what we're doing with our test suite is a perverse use of
a database - from your reply it would seem that the slow performance of
rollbacks compared to commits is something we'll have to live with.

Kind regards,;

Renny

And in 9i you should not be using rollback segments at all. Rather
you should be using UNDO where you have nothing to say about anything
except retention time. The two versions you mention are best
configured with completely different solutions.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)