Hi Stephane,
I have not been able to test a larger log buffer without maxing out my CPU, but
I have worked out where to peek into the SGA to read the value of the background
write threshold out of memory. (At 8.1.6 it is an offset of 304 bytes into the
kcrfsg structure. You can get the address of that structure from X$KSMFSV, and
use the ORADEBUG PEEK command read the contents of the memory location.)
Accordingly, I can confirm that the 1M limit on the background write threshold
is indeed there at 8.1.6. The bottom line is that you can make your log buffer
as big as you please without much negative impact on your 'log file sync' times.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
From: paquette stephane [mailto:stephane_paquette_at_yahoo.com]
Sent: Monday, 18 December 2000 19:41
To: Multiple recipients of list ORACLE-L
Subject: RE: Redo Copy Latch - Correction
Hi all
On a datawarehouse system where data changes by big
loads in a small time interval, the log buffer is
filled very fast, is there any specific advices on
setting the size of the log_buffer (Oracle 8.1.6)?
I know the best performance would be obtained by
loading without logging but it's too late for this
phase.
- Steve Adams <steve.adams_at_ixora.com.au> a écrit : >
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-----
> Sent: Tuesday, 12 December 2000 12:22
> To: Multiple recipients of list ORACLE-L
>
>
> 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-----
> Sent: Tuesday, 12 December 2000 2:26
> To: Multiple recipients of list ORACLE-L
>
>
> 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
=== message truncated ===
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
Received on Mon Dec 18 2000 - 06:52:29 CST
