oracle 12c Adaptive Scalable Log Writer 导致的log file sync

Multiple Log Writers in 12c Causing Enabling and Disabling of Adaptive Scalable Log Writer Workers Which Cause High 'log file sync' Wait Event (文档 ID 2174075.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

  • The database slows down, or appears to hang occasionally, with many sessions waiting on wait event 'log file sync'
  • AWR report indicates heavy wait time on 'log file sync':
    Top 10 Foreground Events by Total Wait Time

    Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class

    log file sync 651,474 46.2K 70.95 79.1 Commit <<<--- Eighty percent of DB time is due to 'log file sync' wait event.
    DB CPU 4057.2 6.9
    log file sequential read 45,955 1447.9 31.51 2.5 System I/O
    SQL*Net more data to client 199,387 1089.5 5.46 1.9 Network

 

  •  Log writer trace file shows following messages:Created 2 redo writer workers (2 groups of 1 each)

    *** 2016-07-25 04:14:30.349
    kcrfw_slave_adaptive_updatemode: scalable->single group0=1024 all=1032 rw=851 single=1352 scalable_nopipe=1702 scalable_pipe=936 scalable=1696

    *** 2016-07-25 04:14:30.349
    Adaptive scalable LGWR disabling workers

    *** 2016-07-25 04:15:08.025
    kcrfw_slave_adaptive_updatemode: single->scalable redorate=273314 switch=90953

    *** 2016-07-25 04:15:08.025
    Adaptive scalable LGWR enabling workers

    *** 2016-07-25 04:19:09.874
    kcrfw_slave_adaptive_updatemode: scalable->single group0=3210 all=3216 rw=782 single=1424 scalable_nopipe=1564 scalable_pipe=860 scalable=1562

 

CAUSE

Multiple log writer worker processes were first introduced in Release 12c.  This may cause frequent enabling and disabling of log writer adaptive, scalable workers which may produce higher wait time on 'log file sync'. 

 

SOLUTION

 Restrict the log writer to a single worker process (which was the default behavior prior to Release 12c).

ALTER SYSTEM SET "_use_single_log_writer"=TRUE SCOPE=SPFILE;

 Since this parameter is not dynamic and needs to be set using scope=spfile, stop and restart the database instance after making the change. 

REFERENCES

NOTE:1625912.1 - New Background Processes In 12c
NOTE:1462942.1 - Adaptive Switching Between Log Write Methods can Cause 'log file sync' Waits

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值