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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
