Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 'log file single write' and 'log file parallel write'

RE: 'log file single write' and 'log file parallel write'

From: Diego Cutrone <dcutrone_at_afip.gov.ar>
Date: Thu, 7 Sep 2000 13:25:17 -0300
Message-Id: <10612.116420@fatcity.com>


Tom, Steve, thanks for answering (please forgive my poor english)

(AIX, Oracle 7.3.4)

Steve, as a matter of fact, this question came to me whan I was running your script "lgwr_stats".
Some time ago it was reporting a sync cost ratio of 1.20 !!! To combat this I set _log_io_size=24 (my log block size is 512 bytes) and the sync cost ratio is
about 0.90 now.

But what I don't understand is how could the ratio be greater than "1", I think this is not possible.
To get this ratio you divide (average log file sync wait/average log file parallel write), where
log file sync wait= it is the time it takes the LGWR to flush the log_buffer data to disk only when a

                               commit takes place.
log file parallel write wait= it is the time it takes the LGWR to flush the log_buffer data to disk whenever
                                a commit takes place (sync write) or when a
background syncs takes place.

According to this, "log file parallel write wait" includes "log file sync wait", so how can it be possible that
this ratio was greater than 1.

I'm sure I'm missing something here, could you please tell me what?

-----Mensaje original-----
De: Steve Adams <steve.adams_at_ixora.com.au> Para: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Fecha: Jueves 7 de Septiembre de 2000 1:42 AM Asunto: RE: 'log file single write' and 'log file parallel write'

>Hello Diego,
>
>The 'log file single write' event is associated with writes to the log
>file header blocks only, and that only happens when a log file is
>being opened or closed. Header block writes have to be single writes
>(rather than parallel writes to all the members of the group) because
>part of the header information is the file number, and that is
>different for each member. These waits are rare and occur in the
>background anyway, so I suggest that you not concern yourself with
>them.
>
>The 'log file parallel write' event is used for routines writes of
>sets of redo blocks by LGWR. It is interesting only as a point of
>comparison for the 'log file sync' time, which is what the Ixora "sync
>cost ratio" does. Both your calculation of the sync cost ratio, and
>your interpretation of it are incorrect. Please use the lgwr_stats.sql
>script at http://www.ixora.com.au/scripts/redo_log.htm#lgwr_stats to
>calculate the ratio, and check the recent answer at
>http://www.ixora.com.au/q+a/0008/29172823.htm for some guidelines on
>how to interpret the number. If you want to know the ratio of
>background writes to sync writes, then use the 'redo writes' and 'redo
>synch writes' statistics from V$SYSSTAT instead.
>
>@ Regards,
>@ Steve Adams
>@ http://www.ixora.com.au/
>@ http://www.christianity.net.au/
>@
>@ Going to OpenWorld?
>@ Catch the Ixora performance tuning seminar too!
>@ See http://www.ixora.com.au/seminars/ for details.
>
>
>-----Original Message-----
>Sent: Thursday, 7 September 2000 5:11
>To: Multiple recipients of list ORACLE-L
>
>
>Hi, hope somebody can help me
>
>What does the 'log file single write' event mean?
>
>What is the difference with 'log file parallel write' event ?
>
>
>
>In one of my databases I have:
>
>Event Average Wait
>
>log file sync 3.69
>log file single write 1.21
>log file parallel write 3.80
>
>
>From this, can I say that my sync cost ratio is 3.69/(1.21+3.80)=0.73
>That means that 73% of LGWR writes takes place due to Sync Writes
>(Commit Statements) and
>that only 27% of them are due to background writes (log buffer above
>_log_io_size).
>Is this correct ???
>
>Thanks
>
>--
>Author: Steve Adams
> INET: steve.adams_at_ixora.com.au
>
>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
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
Received on Thu Sep 07 2000 - 11:25:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US