Tuning for rollbacks?

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 Received on Thu Sep 15 2005 - 07:43:32 CDT