How to Tune Log File Sync? [ID 857576.1]

Information to help diagnose log file sync can be obtained using the script. found in Note 1064487.1 .

A: 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 below tips will help you to reduce log file sync

1) Tune LGWR to get good throughput to disk . eg: Do not put redo logs on RAID 5.

2) 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.

3) See if any of the processing can use the COMMIT NOWAIT option . 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"

4) See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.

References

NOTE:34592.1 - WAITEVENT: "log file sync" Reference Note
NOTE:1064487.1 - Script. to Collect Log File Sync Diagnostic Information (lfsdiag.sql)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21496237/viewspace-690130/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21496237/viewspace-690130/