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 ===
Received on Mon Dec 18 2000 - 03:38:20 CST
