Re: Intermittent slow commit - Statspack Vs LogMiner

I would run same thing locally on database it self and see how its performing. If its performing fine there then culprit could be either network or client. From you statspack data it looks like culprit might be your I/O subsystems.
Check I/O response time for the disk that hosts Redo logs? AIX iostat doesn't give you service time but if you're using vxfs then you can use vxdisk. If you don't then use dd to command to measure write I/O performance.

Hope this helps.

--Maks
Peter Groarke wrote:
> 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 - 11:17:40 CDT