RAC Wait Event: gcs log flush sync

Contents

1. Overview : gcs log flush sync wait event
2. Monitor gv$session wait events for LGWR, LMS processes
3.  Gcs Log Flush Sync Wait histogramms
4. References

Overview : gcs log flush sync wait event

• Before sending a reconstructed CR block or CUR block, LMS will verify that corresponding redo vectors are flushed to disk.
• LMS will flush request a log flush when there are uncomitted transactions within the required block -> Increasing commit frequence will increase this wait event too
• Use lfsdiag.sql script to understand the wait time suffered by LGWR process
• If the redo vector are not flushed, LMS on node2 need to wait for ‘gcs log flush sync’ event after requesting LGWR for a log flush, analogous to ‘log file sync’ event.
•  in a healthy database 90 % of GCS Log flush Sync should be 2ms ore less
• Always check related instances for LOG FILE SYNC event as this event also reduces the Redo I/O bandwith
• If gcs log flush sync wait event is significant then you need to understand your LGWR performance ( you may use lfsdiag,sql script )

 

Monitor gv$session wait events for LGWR, LMS processes

SQL> select w.inst_id, w.sid,w.program, w.event , w.STATE , w.SECONDS_IN_WAIT SEC_in_WAIT , w.P1TEXT || ': '  || w.P1 || ' - '    || w.P2TEXT  || ': '  || w.P2 || ' - '      || w.P3TEXT || ': '  || w.P3 as P1_P2_P3 from gv$session w where        program like '%LGWR%' or program like '%LMS%' or  program like '%JDBC%' order by program;INST_ID        SID PROGRAM                          EVENT                         STATE             SEC_IN_WAIT P1_P2_P3------- ---------- -------------------------------- ---------------------------- ------------------ ----------- ---------------------------------------------            2         18 [e-mail] (LGWR) log file parallel write      WAITED SHORT TIME  0 files: 1 - blocks: 1 - requests: 1      2         13 [e-mail] (LMS0) gcs log flush sync           WAITING            0 waittime: 30 - poll: 0 - event: 138

 

 Gcs Log Flush Sync Wait histogramms

Script:  @event_histogram_from_awr.sqlAcceptable gcs log flush sync histogramm (90 % of wait requests returns in less that 2 ms )BEGIN_INTERVAL_TIME            Inst    SNAP_ID EVENT_NAME        WAIT_TIME_MILLI WAIT_COUNT------------------------------ ---- ---------- ----------------  --------------- ----------28-NOV-13 04.24.27.507 PM         1       876 gcs log flush sync              1      58940                                  1       876 gcs log flush sync              2      51775                                  1       876 gcs log flush sync              4       1433                                  1       876 gcs log flush sync              8        605                                  1       876 gcs log flush sync             16        271                                  1       876 gcs log flush sync             32        181                                  1       876 gcs log flush sync             64        133                                  1       876 gcs log flush sync            128         78                                  1       876 gcs log flush sync            256         7                                  1       876 gcs log flush sync            512         1 Gcs log flush sync wait histogramm with high wait times 28-NOV-13 04.25.58.021 PM         1       877 gcs log flush sync               1        304                                  1       877 gcs log flush sync               2        662                                  1       877 gcs log flush sync               4      11816                                  1       877 gcs log flush sync               8      46981                                    1       877 gcs log flush sync              16      68740                                    1       877 gcs log flush sync              32        202                                  1       877 gcs log flush sync              64        141                                  1       877 gcs log flush sync             128         83                                  1       877 gcs log flush sync             256          8                                  1       877 gcs log flush sync             512          1