Intermittent slow commit - Statspack Vs LogMiner

Hi,

   My jdbc app can intermittently take up to 20 seconds to commit a tiny transaction. (To return to the app) It always gets a db connection and executes a stored procedure quickly. Most of the time it commits quickly, but occassionally it can take anywhere up to 20 seconds.

Server is Oracle 9.2.0.4i running on AIX. JDBC app is running on a remote Solaris box.

We suspected redo disk contention.
Ran Statspack which showed average commit time of 1.2 seconds
(1320 user commits in 1640 seconds)

This appears to be a terrible figure (after all if thats the average what is the max).

However, when I ran LogMiner analysis on a slow 5 second commit case (a single insert and update) the commit appears to have happened immediately.
(the txn time and commit time were identical).
But the commit() in my application only returned 5 seconds later.

Where could the delay possibly be ?
Is LogMiner accurate - Should I snoop the network. Any thoughts appreciated
Peter Groarke Received on Tue May 24 2005 - 03:59:27 CDT