This wait event belongs to commits wait class and implies user session has issued commit but it is not completed as redo log writers has not yet finished writing into the redo log file i.e all redo log buffers are not flushed to redo log file disk. Excessive waits on log file sync wait event indicates either there are too many commits or slow I/O sub system on which redo log files have been placed.
User Actions that should be taken:
• Do not put redo logs on RAID 5. Now a days SSD disks have evolved in much bigger use but NAND flash based SSD disks should not be used to store redo log file as redo log writes are sequential writes for which these disks are not suitable. DDR based SSD can be used to used to store redo logfile. Place log files on dedicated disks. Consider putting log files on striped disks.
• Be prudent with frequency of commits and commit batch size . If there are lots of short duration transactions, see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be piggybacked by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.
•Determine whether any activity can safely be done with NOLOGGING / UNRECOVERABLE options. This may be an option in data ware house where data can be reproducabile.
User Actions that should be taken:
• Do not put redo logs on RAID 5. Now a days SSD disks have evolved in much bigger use but NAND flash based SSD disks should not be used to store redo log file as redo log writes are sequential writes for which these disks are not suitable. DDR based SSD can be used to used to store redo logfile. Place log files on dedicated disks. Consider putting log files on striped disks.
• Be prudent with frequency of commits and commit batch size . If there are lots of short duration transactions, see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be piggybacked by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.
•Determine whether any activity can safely be done with NOLOGGING / UNRECOVERABLE options. This may be an option in data ware house where data can be reproducabile.
Posts
