How To Size the Database Smart Flash Cache (Doc ID 1317950.1)

APPLIES TO:

GOAL

To answer the question "How can I minimize waits for 'log file sync' ?"

FIX

Log file sync waits occur when sessions wait for redo data to be written to disk. Typically this is caused by slow writes or committing too frequently in the application. Checking the "user commits" section in the AWR report can reveal if the issue is related to frequent committing. 

The tips below will help you to reduce log file sync when writes are slow:

• Tune LGWR to get good throughput to disk . eg: Do not put redo logs on RAID 5.
• Do not put redo logs on Solid State Disk (SSD)
  Although generally, Solid State Disks write performance is good on average, they may endure write peaks which will highly increase waits on 'log file sync'
• 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 must confirmed that the relevant REDO is on disk before it can complete. Although commits can be "piggybacked" by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.
• On 10g , See if any of the processing can use the COMMIT NOWAIT option . 
  
  
  Note: From 11g The COMMIT_WRITE parameter is deprecated. It is retained for backward compatibility only. It is replaced by the COMMIT_LOGGING and COMMIT_WAIT parameters.
  
  See: 
  Oracle® Database Reference
  11g Release 2 (11.2)
  Part Number E17110-08
  COMMIT_LOGGING
  
  
  COMMIT_WAIT
  
  
  COMMIT_WRITE
  
  
  for more details
  
  
  In Oracle 10g Release 2 the COMMIT command has been enhanced with the WRITE clause to give a degree of control over the way redo information is written to the redo logs during the commit operation. This can improve performance, but  it should only be used for processes that meet the following criteria:
  
  • They result in large numbers of transactions that require redo log writes.
  • Data loss can be tolerated in the event of an instance crash during the process.
  • Waiting for redo log writes is a significant part of the waits associated with the process.
  The available options for the COMMIT command and the WRITE clause are displayed below.
  
  
  
  COMMIT;
  COMMIT WRITE WAIT; --&gt The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.
  COMMIT WRITE NOWAIT; --&gt The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log.
  COMMIT WRITE BATCH; --&gt The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.
  COMMIT WRITE IMMEDIATE; --&gt The commit "prods" the LGWR process by sending a message, so that the redo is written immediately to the redo logs.
  
  To avoid make modifications to your code, you can you can use the COMMIT_WRITE parameter. You can set it on session level or on system level.
  
  
  SQL> ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,NOWAIT';
  
  You can use trigger to set the parameter for the user that run the application:
  
  
  SQL> CREATE OR REPLACE TRIGGER sys.global_commit_session_settings AFTER LOGON ON .SCHEMA
  BEGIN
  execute immediate 'alter session set COMMIT_WRITE =''IMMEDIATE,NOWAIT''';
  END;
  /
  
  Please keep in mind that using this option, and in case of database crash, the data that is not written to the redolog file will be lost even though they are committed. This is part of the requirements for commit nowait is that "Data loss can be tolerated in the event of an instance crash during the process"
• See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.