Re: log file sync + latch free WAITs

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Wed, 31 Jan 2001 00:35:21 GMT
Message-ID: <3a775c88.1979718785@nsw.nnrp.telstra.net>

Hello Vitaliy,

You are COMMITting MUCH too often. The 'redo allocation' latch contention is a secondary effect of the extreme COMMIT frequency.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
From: Vitaliy Mogilevskiy <vit100gain_at_hotmail.com>

I got a problem where a session WAIT's for "log file sync" + "latch free" forever and REDO LOGS never get switched. If I manually switch the logs (ALTER SYSTEM SWITCH LOGFILE) the "switch" succeeds but it doesn't help the session WAITs.

V$SESSION_WAIT (waits are either "latch free" or "log file sync"

EVENT                     P1TEXT                           P1
------------------------- ------------------------- ---------
log file sync             buffer#                          38

EVENT                     P1TEXT                           P1
------------------------- ------------------------- ---------
latch free                address                   805363076

When "log file sync" waits occur P1 increments by 1 with intervals of 3-5 seconds then eventually "latch free" wait occurs (P1 for "latch free" is always same = 805363076). Below is the summary for waits/timeouts.

V$SESSION_EVENT

SQL> /

      SID EVENT                               TOTAL_WAITS TOTAL_TIMEOUTS

--------- ----------------------------------- ----------- --------------

       23 latch free                                 1618            957

       23 process startup                               4              0

       23 rdbms ipc reply                              29              0

       23 enqueue                                      18              0

       23 free buffer waits                            14              0

       23 local write wait                              3              0

       23 log buffer space                            568              0

       23 log file switch completion                  259             27

       23 log file sync                            116801            212

       23 db file sequential read                     355              0

       23 db file scattered read                      396              0

       23 file open                                     6              0

       23 SQL*Net message to client                    87              0

       23 SQL*Net message from client                  87              0

       23 PX Deq: Join ACK                             27              0

       23 PX Deq: Signal ACK                           27             12

       23 PX Deq: Parse Reply                          17              0

       23 PX Deq: Execute Reply                      1401              0

18 rows selected.

Please help guys! Never seen this before. Received on Tue Jan 30 2001 - 18:35:21 CST