Oracle 聯(lián)機(jī)重做日志文件記錄了數(shù)據(jù)庫(kù)的所有變化(DML,DDL或管理員對(duì)數(shù)據(jù)所作的結(jié)構(gòu)性更改等)，用于對(duì)于意外刪除或宕機(jī)利用日志文

Oracle 聯(lián)機(jī)重做日志文件記錄了數(shù)據(jù)庫(kù)的所有變化(DML,DDL或管理員對(duì)數(shù)據(jù)所作的結(jié)構(gòu)性更改等)，用于對(duì)于意外刪除或宕機(jī)利用日志文件實(shí)現(xiàn)數(shù)據(jù)恢復(fù)來(lái)確保數(shù)據(jù)的完整性。但不合理的聯(lián)機(jī)日志文件規(guī)劃將引發(fā)日志相關(guān)的等待事件。下面是這樣一個(gè)來(lái)自生產(chǎn)環(huán)境中的例子。

1、故障描述

--客戶(hù)描述該數(shù)據(jù)庫(kù)晚上用于實(shí)現(xiàn)數(shù)據(jù)同步以及匯總，以前一直工作的比較良好，隨著需要同步的數(shù)量量的增大，最近變得越來(lái)越慢。
--下面我們首先取了客戶(hù)晚8點(diǎn)至第二天7點(diǎn)的awr report。

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ST990 2152526631 ST990 1 10.2.0.3.0 NO v2011db02p

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 21787 21-Feb-13 20:00:22 50 19.5
End Snap: 21798 22-Feb-13 07:00:47 44 20.0
Elapsed: 660.42 (mins)
DB Time: 928.06 (mins)

--從上面的awr report 可知，單實(shí)例，，10.2.0.3版本，期間的會(huì)話數(shù)也不多
--Elapsed < DB Time
--Elapsed Time=(20130222 07:00:00 - 20130221 20:00:00)≈ 660
--DB Time=928.06 ,運(yùn)行環(huán)境為16核CPU, 660*16=10560, cpu花費(fèi)了928.06分鐘在處理Oralce非空閑等待和運(yùn)算上
--從上可知，整個(gè)系統(tǒng)還是比較空閑

--下面來(lái)看看top event
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 20,673 37.1
log file parallel write 27,399 4,797 175 8.6 System I/O
control file parallel write 13,428 4,688 349 8.4 System I/O
log file sync 19,564 3,795 194 6.8 Commit
db file scattered read 26,651,537 3,439 0 6.2 User I/O

--從上面的top event事件上來(lái)看，log file相關(guān)等待事件表現(xiàn)明顯
--log file parallel write等待事件總等待次數(shù)27,399 總等待時(shí)間4,797/60=79.95(min),超出一個(gè)小時(shí)，相當(dāng)可觀
--其次是control file parallel write與log file sync事件的相關(guān)等待

--下面是等待事件的detail信息
Wait Events DB/Inst: ST1200/ST1200 Snaps: 21787-21798
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
log file parallel write 27,399 .0 4,797 175 1.1
control file parallel write 13,428 .0 4,688 349 0.5
log file sync 19,564 10.6 3,795 194 0.8
db file scattered read 26,651,537 .0 3,439 0 1,049.4
db file sequential read 6,682,373 .0 1,567 0 263.1
log file switch (checkpoint 1,091 92.9 1,019 934 0.0
Datapump dump file I/O 633,458 .0 286 0 24.9
log file switch completion 332 31.6 183 552 0.0
log buffer space 255 47.8 155 608 0.0
free buffer waits 2,409 99.5 120 50 0.1
buffer busy waits 145 62.8 96 664 0.0

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com