Low priority high volume database writes killing you?

Scenario: Your application has some very high volume -- and perhaps large -- writes to the database that are low priority. A perfect example would be a log that contains a CLOB.

Perhaps this is clogging up your database manager to the point it is affecting the performance of your app itself. Your instinct would be to perhaps defer the write of these log records to a background app perhaps via a JMS queue.

However, Oracle 10g+ has a most excellent feature that lets you implement similar functionality just by modifying your 'COMMIT' statement at the end of your insert.

Instead of doing a 'COMMIT', you do a 'COMMIT WRITE BATCH NOWAIT'. BATCH grants the database manager the permission to batch up the writes and write them out at its convenience -- and the NOWAIT instructs to return control to your app immediately. So, by adding just a few words to your 'COMMIT', you would have effectually implemented a queuing solution!

However, nothing is ever that simple. java.sql.Connection().commit(); is not going to accomplish this. You would have to do something like:

Connection c = DataSource.getConnection();
c.setAutocommit(false);
...prepare statement, insert, close prepared statement;
Statement s = java.Sql.Connection.createStatement();
s.execute("COMMIT WRITE BATCH NOWAIT");
s.close();
c.close();

Just double check to see if perhaps your DBA changed the system wide parameter COMMIT_WRITE . Its default is 'IMMEDIATE,WAIT'. Leave it that way.