Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: log buffer space

Re: log buffer space

From: Jonathan Lewis <>
Date: Mon, 17 Mar 2003 07:14:16 -0800
Message-ID: <>

I've just tried a different test, along the following lines, which seems to confirm that LGWR is triggered when the buffer is about 1/3 full.

Set log_buffer to an easy number such as 600K.

Create table with one column of a nice large size, e.g. varchar2(1000);

Take snapshot of redo size, redo writes, redo wastage figures from v$sysstat.

Insert N rows into table.

Taks snapshot and find changes.

Vary the number of rows inserted until

    M rows does not result in a redo write     M+1 rows results in a redo write.

Check the redo size for M and M+1 rows.

Under both and, I found that log writer seemed to be consistently triggered at a couple of KB below 1/3 of the log_buffer.

(One oddity that caused me a little hassle with 9.2 at first was that I set the log buffer to 512K, but the actual log buffer size (per v$sga) was actually closer to 640K, so the trigger occurred at 212K when I was expecting it to be 170K.


Jonathan Lewis

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ

> Arup:
> Sorry for the delay ;-)
> I have not seen this is documented anywhere, other than
> 'Oracle Performance Tuning' OReilly Peter & Mark Gurry
> (page 304) where he claims the log writer writes when
> it is 2/3 full... Here is the Original Text.
> Log Buffer
> The log buffer contains the information showing the changes that
> been made to database buffer blocks. When the log buffer reaches
> one-third full (two-thirds full in Oracle 7.3), a user performs a
> or a write takes place to the database,......
> I don't have any Oracle 7.3 database, (for that matter no database
> now as I composing this in Zurich Airport waiting for a connecting
> flight to Bombay..), So I may not be able to test that. But last
> I verified was on an Oracle 8.1 database where the log file writes
> used to be in the order up to 2/3 full.
> You can do a simple test to prove this point. You can use oradebug
> to trace the log writer process and do a CTAS of any big table
> (with a big log buffer) and you will be able to see the writes
> and number of blocks written in a single write.
> I am surprised , this is not documented anywhere in the Oracle
> Documentation or any of the Oracle University course notes.
> Best Regards,
> K Gopalakrishnan

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Mar 17 2003 - 09:14:16 CST

Original text of this message