Re: Intermittent slow commit - Statspack Vs LogMiner

From: Peter Groarke <peter.groarke_at_orbiscom.com>
Date: 25 May 2005 02:05:34 -0700
Message-ID: <1117011934.860311.289090@g14g2000cwa.googlegroups.com>

Thanks for your replies.
Firstly, I don't administer the database directly myself, it is a clients database.

Here is a snapshot from statspack taken over a 3 hr period: Top 5 Timed Events

% Total

Event                                               Waits    Time (s)

Ela Time

-------------------------------------------- ------------ -----------
--------
log file sync                                       2,806       1,640
 31.81
control file parallel write                         3,237       1,206
 23.39
sbtwrite2                                          15,018       1,028
 19.95
async disk IO                                      16,947         711
 13.79
db file parallel write                                 98         159

3.09

This was for 1320 user commits.

The client then moved some activity off the disk and just sent the following new Statspack analysis:
Top 5 Timed Events

% Total

Event                                               Waits    Time (s)

Ela Time

-------------------------------------------- ------------ -----------
--------
control file parallel write                         3,368         465
 58.47
log file sync                                         861         206
 25.90
process startup                                     1,271          32
  4.01
control file sequential read                        5,581          28
  3.49
db file sequential read                             1,983          19

2.44

This was for 688 commits.

These stats appear better.
log file sync is now not the major time wait event and average commit time now appears to be down to under .3 seconds. However that value is still not great and there still appears these occassional slow transactions.

Something very interesting was also found in one of these slow transaction cases:
An insert to a table appears to happen 4 seconds before the commit to the redo log !
(The table has a sysdate column and is pretty much the only DML carried out)
Why would it take Oracle 4 seconds from database write to redo log write ?

Any help appreciated
Peter Groarke Received on Wed May 25 2005 - 04:05:34 CDT