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

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

--這個來自連結:http://www.itpub.net/thread-2073857-1-1.html的討論,很奇怪的問題:

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.

--//英文我就不翻譯了,我估計應該是對方的網路問題導致這個問題,目前僅僅是猜測,等待對方的確認.