RE: log buffer space

Jonathan:

I have just sent a mail which has the test statistics. I would appreciate your comments on that..

Alternatively, people who are curious may want to test the log writer writing habits using the event 10046^8.

KG

Best Regards,
K Gopalakrishnan  

-----Original Message-----
Lewis
Sent: Monday, March 17, 2003 7:14 AM
To: Multiple recipients of list ORACLE-L

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 8.1.7.4 and 9.2.0.2, 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.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 16 March 2003 13:28

> 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.
>
> <QUOTE>
> Log Buffer
>
> The log buffer contains the information showing the changes that
have
> 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
commit,
> or a write takes place to the database,......
>
> </UNQUOTE>
>
> 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
time
> 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: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: K Gopalakrishnan
  INET: kaygopal_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).