RE: Log file sync spike

This is very similar to an issue we experienced under 9i and Solaris 8. We spent a few weeks pointing fingers (and having fingers pointed at us), but ultimately it came down to the buffered writes to the disk.

As this was in 2004, I am mining the email for more details. We were helped by TUSC (*wink wink* to the guys that hang out here). We made two changes that had a dramatic effect; we switched from EMC disk to local (Sun) disk and dumped Veritas File Manager for UFS, but only for the mounts which housed the redo logs (independent of everything else, we are good OFA boys). We never had approval to go back and test to see if either one change or the other did the trick, so I can only recommend you do both. Later in life, we switched to faster disk from EMC (DMX) and Veritas DirectIO, again not knowing if both were needed or not.

Let me know if you want more details, as your situation may be different than ours.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laimutis Nedzinskas Sent: Friday, June 09, 2006 11:01 AM
To: oracle-l_at_freelists.org
Subject: Log file sync spike

I am trying to understand what is causing "log file sync" 5-15 minutes duration spike in my db.
What puzzles me is that I can not see spike in the other activities on my db.

It is strange that:

• "redo write time" statistics does not follow "redo synch time" at the time of the spike
• v$sysstat value of "redo write time" is half of the "redo synch time".

If "redo synch time" spike happens because of hw problems then why "redo write time" does not follow it?
If it is not hw issue then what else LGWR can be doing 50% of time(see the difference between redo write time and redo synch time) ? Or am I hitting yet another V$-bug?

Database statistics in centiseconds follows:

56,105,687 cs redo synch time from v$sysstat (1.140.674 cs redo synch time from v$sesstat)
54,796,921 cs log file sync from v$system_event

24,163,771 cs redo write time from v$sysstat

162,672 cs log file switch completion from v$system_event

Thank you in advance,
Laimis N.
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 09 2006 - 11:30:19 CDT