ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How To Size the Database Smart Flash Cache (Doc ID 1317950.1)

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

原创 Linux操作系统 作者:renjixinchina 时间:2013-07-26 10:53:24 0 删除 编辑

APPLIES TO:

Oracle Server - Personal Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Oracle Server - Standard Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

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. 

For more information on troubleshooting 'log file sync' waits see:
Document 1376916.1 Troubleshooting: "Log File Sync" Waits 

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


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
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams031.htm#REFRN10265

    COMMIT_WAIT
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams033.htm#REFRN10266

    COMMIT_WRITE
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams034.htm#REFRN10260

    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; --> The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.
    COMMIT WRITE NOWAIT; --> The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log.
    COMMIT WRITE BATCH; --> The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.
    COMMIT WRITE IMMEDIATE; --> 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.

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

上一篇: 11g Smart Flash Cache
请登录后发表评论 登录
全部评论

注册时间:2011-01-30

  • 博文量
    373
  • 访问量
    2055287