We have an Oracle database server specifically for our unit tests to run against. Is there a way to tune Oracle specifically for this kind of purpose? As the data is constantly being thrown away (since it's just test data). I wonder if there is a way to have an Oracle database in-memory and connect without the TCP/IP stack perhaps to speed up these tests.
Any suggestions?
The answer is likely yes, but changing the database environment from the production configuration to a integration configuration during testing introduces risk that the testing will give false results.
You can incorporate rebuilding of your table indexes as part of the test run. Choose to either take the time hit of rebuilding your indexes prior to the run or after the run. You'll eat up the same total amount of time, but you'll "feel" it less if you rebuild them after the test run.
ALTER INDEX index_name REBUILD
Will rebuild the indexes without dropping and re-creating them.
The TCP/IP stack is unlikely to be adding much to your overhead. You could, however, run the Oracle instance on the same server as your test cases, and access via ORACLE_SID (which I believe uses OS-level inter-process communication).
Before examining changes to Oracle, however, I'd look at what tests are getting run on your continuous integration server. If you haven't done it already, this means splitting the integration tests (which require a back end) from the unit tests (which don't), and running them on different schedules. There's rarely a reason to run a full suite of integration tests for every change.
Next: are you using any sort of object-relational mapper to access your database? If yes, and you're not relying on any particular Oracle quirks, you could replace Oracle with an in-memory database (you don't say what language you're using, so this may or may not be an option).
And finally, consider using the Oracle import/export facility to completely rebuild your database for each integration test run. It's probably quicker, and definitely more stable than trying to delete whatever rows you created (this assumes that your integration tests start with pre-populated data; if not, just drop and rebuild the schema).
If the hangup is the database cleanup/reset stage, and you have Enterprise Edition, look into FLASHBACK DATABASE as (potentially) a quicker way to reset the database to a fixed point.
At worst, you don't need to waste time building the cleanup/reset scripts.
There are a lot of things you could do to the Oracle instance for the scenario you mention like using the correct locking strategy/isolation level, disabling all kinds of undo-logs, etc. You should consult a good Oracle Tuning book for that (I like the one by Mark Gurry, but I'm not sure how up to date that is).
There is one other thing that might be important: If you constantly add and delete data from your db (I mean "totally empty the db"), make sure you're setting up the storage parameter for each table correctly. If you have the space, consider assigning an initial extent equal to the max size for your test cases. (Either in the db creation script, or define once and then just trancate the tables with the reuse storage option.) Then when you run the test cases, the db doesn't have to allocate additional storage space.
I faced a similar issue and I was able to speed up the unit tests by moving the redo logs, undo and users tablespace to a RAM disk. There is a free version of ramdisk software available for you to try out. Commercial versions that back up the files periodically are also very cheap.
In my case the unit tests only verify data integrity and hence this strategy is low risk even though it is does not replicate production setup. We have a separate scale and performance test stgrategy.