Your waits events clearly points to I/O problems. Your write takes
almost 372ms which is way too high.. Try to get it confirmed from
iostat or vxdisk if you're using veritas. With every commit log writer
needs to has to perform a write to redologs.
Your observation of .3 seconds/commit i.e. 300msec goes quite well with
what statspacks shows.
Hope this helps.
Maks.
Bjarke Wedemeijer wrote:
> "Peter Groarke" <peter.groarke_at_orbiscom.com> wrote in message news:<1117011934.860311.289090_at_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
>
> If you can reproduce the problem of 1 transaction try running the
> transaction with 10046 on, within the trace you can see if the
> transaction wait for example on log file sync. For now, slow commits
> can be a lot of things, including log switching, log buffer flush problems
> etc,try to trace the single transaction with 10046 and read the results
> directly from the WAITS and ELA in the trace.
> (see Cary Millsaps book Oracle Performance Tuning how to interpret
> 10046 traces)
Received on Fri May 27 2005 - 00:17:38 CDT
