RE: Someone remind me - SLOW LGWR - buffer flushes prevent new blocks being read in

We're seeing large delays in LGWR writes (800ms) getting written to the lgwr trace file in 10.2.0.4 for small kb writes. System is reporting large IO waits and sysadmins are looking into it now.

Chris

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

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Thursday, October 18, 2012 4:12 PM To: Taylor Christopher - Nashville; oracle-l_at_freelists.org Subject: RE: Someone remind me - SLOW LGWR - buffer flushes prevent new blocks being read in

Lots of possibilities:

A couple-tree likely candidates:

1. If LGWR falls far enough behind that you run short of log buffer space then updates will hang until that is cured. (If logon auditing is on, that can appear to be a complete hang to a casual glance.)
2. If LGWR and/or ARCH fall so far behind that the online redo capacitance you have configured is full, about everything but ARCH hangs (filling the archive destination is a classic way to make this happen. If LGWR can fall this far behind on your system without ARCH being stopped, something is probably dramatically wrong with your configuration. If bursty activity sometimes causes ARCH to fall far enough behind so that online logs wrap, you probably need more online log space, either via more or larger groups).
3. If you need your load of freeing buffers is very high combined with occupied buffers being frequently updated, DBWR might have to wait for LGWR before a buffer block and be written and become a candidate to be freed.

JL writes about this a bit in "Oracle Core" and Arup Nanda has a presentation online various places about log buffer flushes piling up into gc buffer busy waits with RAC. Craig S. makes a similar presentation that is quite entertaining for a single instance, but I don't see it online at the moment. I think Riyaj has also written about this.

So at steady load state if your need for new free buffer cache blocks exceeds the rate buffers are freed, you'll eventually be waiting for free buffers. If lgwr is slow enough to be the pacing task in freeing blocks, then in that context "slow lgwr buffer flushes prevent new blocks being read in."

In looking at why lgwr might be slow, it is useful to establish whether the problem is lgwr getting sufficient CPU cycles at low enough latency to keep up or your write rate once lgwr is actually writing something.

Good luck. That's the approximate shape of what I think you want to know. For a detailed answer to a specific problem, we'll need the usual stuff to help

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

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Thursday, October 18, 2012 3:25 PM To: oracle-l_at_freelists.org
Subject: Someone remind me - SLOW LGWR - buffer flushes prevent new blocks being read in

I was reading somewhere (and I can't put my hands on it now of course) where when LGWR is slow, that it backs stuff up bad enough that blocks can't get into the cache fast enough and will slow the whole database down (that's the gist of it I think). When LGWR is exceptionally slow/hung. Can someone point me in the right direction here?

Chris

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 18 2012 - 23:54:04 CEST