RE: Redo Copy Latch - Correction

Hi All,

Time to eat some humble pie. I have received an email from someone on the list who works for Oracle and has access to the source code telling me that the "rumour" about the 1M background write threshold is in fact true, and has been since 8.0. Upon reflection, it makes sense that I was able to see background writes well in excess of 1M, because I was using lots of processes to generate the redo and may have unintentionally starved LGWR of CPU time. I'll repeat the test more carefully when I have some time.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
From: Steve Adams [mailto:steve.adams_at_ixora.com.au] Sent: Tuesday, 12 December 2000 12:22
To: Multiple recipients of list ORACLE-L Subject: RE: Redo Copy Latch

Hi All,

I've just noticed this thread, and it seems that some further clarification is needed.

It is not true that if the log buffer is less than 1M then LGWR will not be posted to do background writes when the buffer becomes more than 1/3rd full (or exceeds _log_io_size). This is a ludicrous suggestion. Just think of all the 'log buffer space' waits that would ensue. Anyway, the statistics demonstrate plainly that the background write threshold is still respected, regardless of the size of the log buffer.

Incidentally, there is another rumour about that a 1M maximum has been introduced at Oracle8 for the background write threshold to prevent long 'log file sync' waits if the DBA configures a log buffer larger than 3M. I think this comes from Oracle's "Oracle8 Performance Tuning Workshop". Although the idea has some merit, and Oracle may be planning to implement it in a future release, it is not there as of 8.1.6.2.

As to LGWR's use of the redo latches, it is fairly obvious that LGWR would need the 'redo allocation' latch because it reads and writes the same variables that foregrounds do when allocating space in the buffer. Although there are others, the main variables concerned are kcrfsfbb (the base disk block number), kcrfsfi (the index of current block being filled) and kcrfshsb (the highest block that needs to be synced). As you say, LGWR no longer waits for the copy latches at 8i. Have a look at the Ixora tip on "Redo Latch Tuning" at http://www.ixora.com.au/tips/tuning/redo_latches.htm for more information.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
From: Diego Cutrone [mailto:dcutrone_at_afip.gov.ar] Sent: Tuesday, 12 December 2000 2:26
To: Multiple recipients of list ORACLE-L Subject: RE: Redo Copy Latch

Hi Gaja,

Thanks for your clarification. I didn't know that LGWR functionality had changed in Oracle 8. Please confirm me this (for Oracle 8 realease or above):

If log_buffer < 1MB then Oracle8 LGWR behaves like Oracle7 does. I mean LGWR writes when 1/3 of log buffer fills (background write), and in order to do this, it takes redo copies and redo allocation latch to mark entries in the redo log buffer, etc ... . And, of course, it also writes when a commit takes place (sync write), and when that happens it also takes these latches.

But, if log_buffer >= 1MB then LGWR behaves as you described in your post. LGWR only flushes entries to disk if log buffer is "full" or when a commit takes place. And when "any" one of these events happens, LGWR takes the redo copy latches and the RAL, it marks what it has to flush to disk, realeases these latches, then it writes the entries to disk (or the whole log buffer), it takes RAL again, updates "base disk block" variable and realeses RAL. am I correct?

Please correct me if I'm wrong.
Thanks

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, December 08, 2000 2:20 PM

Hi Anil, Diego & the list,

I think I may have a clarification to your disagreement and it is related to a functionality change in how LGWR writes the redo entries from the log_buffer to disk. It stems from one of the "events" that cause LGWR to write - the 1/3rd full event.

Prior to Oracle8, if log_buffer were 'x bytes' in size, when there were 'x/3 bytes worth of redo entries' in the log_buffer, LGWR wrote the x/3 bytes to disk, allowing other server processes to continue writing to the remaining 2x/3 bytes. That was the whole point behind it being a 'circular buffer'. This occurred without any redo copy latches being taken away or frozen.

In Oracle8 that functionality got modified a little. The 1/3 full event really does not 'kick in' unless log_buffer was sized at 1Mb. So if log_buffer is less than 1Mb. in size, LGWR will wait for log_buffer to be full and then initiate the write. When that happens, it is only normal for the redo copy latches to be momentarily frozen or taken away (as the case may be), to prevent any writes to log_buffer (because it is already full).

The point I was trying to make in my original posting that LGWR itself should not take away any of the redo copy latches for the normal process of writing redo entries to disk. The subtle difference I am trying to point out is between the 'entire redo log buffer' getting flushed to disk vs. redo entries written to disk periodically (when 1/3rd full or every 3 secs.).

The need to flush the redo log buffer really will not arise if the 1/3rd full event kicks in (as needed). However, this doesn't happen if log_buffer is sized < 1Mb. To add a little more complexity to the problem, there is also the 'nuance' of the 'write ahead' feature of LGWR a.k.a. 'piggy-backed writes' and this occurs when multiple commit tokens are written to the log_buffer, because multiple transactions committed approximately at the same time and posted their tokens to log_buffer.

Hope that helps,

Gaja

• Anil Sikri <anil_sikri_at_hotmail.com> wrote:

Hi Gaja ( and the list ),

Nice explanation, as always. I do disagree on one point, though. I believe that at least on Oracle 8.0.x, LGWR does take all the redo copy latches when it is in the process of flushing the redo log buffer to write the contents to the appropriate redo log file. This is done to prevent any server process from modifying the contents of log buffer while it is being flushed by the LGWR. All these server processes contending for the log buffer have to wait on a log file sync event which can be seen in V$SESSION_WAIT ( This is the same event for which DBWR waits so that LGWR can flush the log buffer first ).

I think this ( LGWR taking all the redo copy latches ) has changed on Oracle8i.

Regards,
Anil Sikri

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com> Reply-To: ORACLE-L_at_fatcity.com Received on Tue Dec 12 2000 - 23:55:21 CST