[20161228]奇怪log file sync等待事件.txt

--这个来自链接:

Top 10 Foreground Events by Total Wait Time

Event                                              Waits    Total Wait Time (sec)    Wait Avg(ms)    % DB time    Wait Class log file sync                                      47,412    50K                      1054            68.2        Commit DB CPU                                                      7250.1                                     9.9     log file switch (private strand flush incomplete)    420    5179.7                    12333            7.1        Configuration enq: TX - row lock contention                         46    4448.1                    96698            6.1        application JS kgl get object wait                             3,814    1366                        358            1.9        Administrative JS kill job wait                                   1,269    1278.1                     1007            1.7        Administrative SQL*Net message from dblink                      286,374    602.3                         2             .8        Network direct path write temp                            30,149    405.4                        13             .6        User I/O buffer busy waits                                    302    376.2                      1246             .5        Concurrency db file sequential read                           49,254    301.4                         6             .4        User I/O

--30分钟awr报表,log file sync 达到了50K sec,一般出现这个多数情况是存放redo磁盘IO很慢,或者事务非常多.硬件不堪重负.

--而仔细看db file sequential read 平均等待事件仅仅6ms,而且lz介绍数据文件与redo在同一个磁盘组.

--而看前面Load Profile

Load Profile

                 Per Second    Per Transaction    Per Exec    Per Call

DB Time(s):            36.7                1.4    0.04        0.02 DB CPU(s):              3.6                0.1    0.00        0.00 Redo size (bytes):    164,938.0        6,263.7         

--平均每秒的redo size仅仅160K.这样的量对于现在的磁盘轻松应付.而再仔细看Background Wait Events:

Background Wait Events

    ordered by wait time desc, waits desc (idle events last)

    Only events with Total Wait Time (s) >= .001 are shown     %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Event                     Waits    %Time -outs    Total Wait Time (s)    Avg wait (ms)    Waits /txn    % bg time

LNS wait on SENDREQ       21,345             0                 1,826               86           0.41    37.53 LGWR-LNS wait on channel 116,328            93                 1,647               14           2.21    33.86 log file parallel write   13,220             0                   263               20           0.25    5.40

--//前面2者相加,占用大约70%的后台时间.而这个数值几乎与log file sync占用的db time 68.2% ,相互呼应.

--而dg的参数如下:

log_archive_dest_2    service="XXXX1", LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0

max_connections=1 reopen=300 db_unique_name="XXXX1" net_timeout=30, valid_for=(all_logfiles, primary_role) SERVICE="XXXX1" LGWR ASYNC valid_for=(all_logfiles, primary_role) DB_UNIQUE_NAME="XXXX1"

--//前面是sync,后面是async,估计应该sync.可以推测这个设置导致出现log file sync.

--//检查oracle相关文档:

SYNC and ASYNC

Specifies whether the synchronous (SYNC) or asynchronous (ASYNC) redo transport mode is to be used.

Usage Notes

The LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31 parameters do not support the SYNC attribute.

The redo data generated by a transaction must have been received by every enabled destination that has the SYNC

attribute before that transaction can commit.

The redo data generated by a transaction need not have been received at a destination that has the ASYNC attribute

before that transaction can commit. This is the default behavior if neither SYNC or ASYNC is specified.

--//英文我就不翻译了,我估计应该是对方的网络问题导致这个问题,目前仅仅是猜测,等待对方的确认.