SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE 11.1.0.6.0 Production

TNS for 32-bit Windows: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production

SQL> select event#,

2 name,

3 parameter1 p1,

4 parameter2 p2,

5 parameter3 p3

6 from v$event_name

7 where name like '%log%file%' or name ='log bufer space';

EVENT# NAME P1 P2 P3

---------- --------------------------------------------- ---- ----- -----

118 log file sequential read log# block# blocks

119 log file single write log# block# blocks

120 log file parallel write files blocks requests

124 log file switch (checkpoint incomplete)

125 log file switch (private strand flush incomplete)

126 log file switch (archiving needed)

127 switch logfile command

128 log file switch completion

129 log file sync buffer#

544 log file switch (clearing log file)

已選擇10行。

log file switch(archiving needed)

日誌檔案轉換，必須歸檔。在使用歸檔方式下的資料庫時，只有arch程式透過將REDO檔案複製到歸檔日誌檔案中（完成歸檔），LGWR程式才可以改寫或轉換該重做日誌檔案。對歸檔檔案的寫入失敗，可能會中止歸檔程式，在警告日誌中報告。

無引數等待時間：1秒

這個等待事件出現時通常是因為日誌組迴圈寫滿以後，第一個日誌歸檔尚未完成，出現該等待。出現該等待，可能表示io 存在問題。

解決辦法:

1 考慮增大日誌檔案和增加日誌組

2 移動歸檔檔案到快速磁碟

3 調整log_archive_max_processes

log file switch(checkpoint imcomplete)

由於對REDO的檢查點程式未完成而使日誌檔案的轉換變得不可能。你的日誌組都寫完以後，LGWR 試圖寫第一個log file，如果這時資料庫沒有完成寫出記錄在第一個log file 中的dirty 塊時(例如第一個檢查點未完成)，該等待事件出現。

原因：該等待事件通常表示你的DBWR 寫出速度太慢或者IO 存在問題。

解決方法：

1 考慮增加額外的DBWR

2 增加你的日誌組或日誌檔案大小。

無引數等待時間：1秒

log file switch completion

當日志檔案轉換完成時產生的等待事件

無引數等待時間：1秒

log file single write

等待寫logfile寫完成，在更新logfile頭部時發生，在增加一個log file成員的時候增加序列號時發生。

等待時間：物理io完成時間計時

P1: session當前寫的日誌（組）號

P2: block號

P3: 寫的blocks數

log file sequential read

當程式等待從REDO檔案中讀入塊時會產生該等待事件。

引數說明：

P1: 寫的log file序列號

P2: os的blocks數

P3: io請求的數量

等待時間：完成請求讀取的IO所佔用的實際時間。

log file parallel write

當會話等待LGWR 程式將redo log buffer中的redo寫入redo log中時產生該事件。LGWR會等待該事件的完成，使用者會話則等待log file sync事件。僅當使用非同步IO時LGWR並行寫入到redo log，否則它會順序寫入到每個online redo log。出現該事件意味著重做日誌所在的磁碟裝置IO緩慢或IO爭用。

寫入時機：

1 每隔3秒寫入一次

2 在提交或回滾時

3 在滿足_LOG_IO_SIZE閾值時

4 在日誌緩衝區有1MB的重做項時

5 由DBWR提交時

等待引數：

P1: 寫的log file序列號

P2: os的blocks數

P3: io請求的數量

等待時間：完成所有IO所佔用的實際時間，對於非同步IO，則等到最後一個IO操作完成整個寫入才算完成。

解決辦法：

1 改進IO的效能，使用較快的磁碟裝置，不要將redo放在RAID5上。

2 設法降低重做的數量。

只要有可能就使用NOLOGGING選項。

CTAS操作也應該使用該選項

3 熱備可能建立大量的重做項，從而增加log file parallel write等待事件，所以熱備份應該在非高峰時間內執行，並且應該儘可能將表空間排除在熱備份模式外。

log buffer space

當會話由於log buffer不足而無法將redo條目複製到log buffer時，將會出現log buffer space等待。

LGWR週期性的寫出redo條目，以產生可用的log buffer space。該等待事件表示：應用程式生成重做日誌的速度比LGWR程式將其寫入重做日誌檔案的速度快。

這個等待時間沒有p1,p2,p3引數

產生該等待的原因及解決辦法：

過小的日誌緩衝區

1 檢查當前LOG BUFFER的設定，並根據需要做適當的調整

緩慢的I/O子系統

1 確保log file parallel write等待事件的平均等待時間在可接受的範圍內，否則需要改進IO效能。

2 根據應用程式的情況，在適當的位置設定NOLOGGING選項。

3 作為輔助手段藉助Oracle Log miner深入研究來自於v$sql檢視或重做日誌檔案的DML，發現異常行為。

--會話級統計必須等待日誌緩衝區的次數。

SELECT s.SID , s.VALUE

FROM v$sesstat s

WHERE s.statistic# =

(SELECT t.statistic#

FROM v$statname t

WHERE t.NAME = 'redo buffer allocation retries');

--系統級統計必須等待日誌緩衝區的次數

SELECT s.STATISTIC#, s.CLASS, s.NAME , s.VALUE

FROM v$sysstat s

WHERE s.statistic# =

(SELECT t.statistic#

FROM v$statname t

WHERE t.NAME = 'redo buffer allocation retries')

log file sync

此等待事件使用者發出提交或回滾宣告後,等待提交完成的事件,提交命令會去做日誌同步,也就是寫日誌快取到redo logfile，使用者程式將通知LGWR執行寫出操作,LGWR完成任務以後會通知使用者程式. 在提交命令未完成前,使用者將會看見此等待事件,對於回滾操作，該事件記錄從使用者發出rollback命令到回滾完成的時間.注意,它專指因提交,回滾而造成的寫快取到日誌檔案的等待.在LGWR同步期間，LGWR程式在log file parallel write事件上等待同步寫入完成，而使用者會話則在log file sync事件上等待LGWR的完成。

如果該等待事件過多，可能說明LGWR的寫出效率低下，或者系統提交過於頻繁.

log file sync等待事件與事務的終止(提交或回滾)相關。當程式在log file sync事件上花費大量時間時，

通常表明過多的提交或短事務。

該等待事件的主要原因：

1 高提交率

高提交率會增加開始與結束事務時的系統開銷(事務表的更新、提交後的清除、回滾段的使用被記錄在日志緩衝區中等等)。

2 緩慢的IO子系統

如果日誌的I/O系統較為緩慢的話,LGWR的寫出效率低下,而使用者會話則在log file sync事件上等待LGWR的完成。

SELECT s.event, s.time_waited, s.average_wait

FROM v$system_event s

WHERE s.event IN ('log file parallel write', 'log file sync')

注：'log file parallel write'事件的平均等待時間大於10ms(1cs)，這通常表示存在緩慢的IO吞吐量。

3 過大的日誌緩衝區

日誌緩衝區過大，會造成LGWR程式寫入頻率變慢，一次寫入量變大，花費較多的時間。

4 較大的processes引數值

在每個同步操作期間，LGWR必須掃描所有程式的資料結構查詢哪些會話正在這個事件上等待，並將它們的重做寫入到硬碟

針對該問題，可以關注:

log file parallel write等待事件

user commits,user rollback等統計資訊可以用於觀察提交或回滾次數

解決方案:

1.提高LGWR效能,儘量使用快速磁碟,不要把redo log file存放在raid 5的磁碟上

2.使用批次提交

3.適當使用NOLOGGING/UNRECOVERABLE等選項

4.設定合適的processes 的大小

附上幾個關於redo io 和查詢提交頻繁的使用者的sql語句

1 可以透過如下公式計算平均redo寫大小：

avg.redo write size = (Redo block written/redo writes)*512 bytes

select trunc(a.value/b.value*512) as "REDO每次寫入位元組數",

trunc(1024*1024/3) as "系統設定寫入位元組數"

from

(SELECT s.VALUE FROM v$sysstat s

WHERE s.NAME = 'redo blocks written') a,

(SELECT s.VALUE FROM v$sysstat s

WHERE s.NAME = 'redo writes') b

2 查詢會話中誰執行了大量的提交：

SELECT a.sid,

a.event,

a.time_waited,

round(a.time_waited / c.sum_time_waited * 100, 2) || '%' pct_wait_time,

d.VALUE user_commits,

round((SYSDATE - b.logon_time) * 24) hours_connected

FROM v$session_event a,

v$session b,

(SELECT sid, SUM(time_waited) sum_time_waited

FROM v$session_event

WHERE event NOT IN

('smon timer', 'pmon timer', 'rdbms ipc message', 'Null event',

'parallel query dequeue', 'pipe get', 'client message',

'SQL*Net message to client', 'SQL*Net message from client',

'SQL*Net more data from client', 'dispatcher timer',

'virtual circuit status',

'lock manager wait for remote message', 'PX Idle Wait',

'PX Deq: Execution Msg', 'PX Deq: Table Q Normal',

'wakeup time manager', 'slave wait', 'i/o slave wait',

'jobq slave wait', 'null event', 'gcs remote message',

'gcs for action', 'ges remote message', 'queue messages')

HAVING SUM(time_waited) > 0

GROUP BY sid) c,

v$sesstat d

WHERE a.sid = b.sid

AND a.sid = c.sid

AND a.sid = d.sid

AND d.statistic# =

(SELECT statistic# FROM v$statname WHERE NAME = 'user commits')

AND a.time_waited > 10000

AND a.event = 'log file sync'

ORDER BY hours_connected DESC, pct_wait_time

3 使用以下SQL查出誰在頻繁提交資料：

SELECT sid, VALUE

FROM v$sesstat s

WHERE s.statistic# =

(SELECT statistic# FROM v$statname WHERE NAME = 'user commits')

ORDER BY VALUE

SELECT b.NAME, a.VALUE, round(SYSDATE - c.startup_time) day_old

FROM v$sysstat a, v$statname b, v$instance c

WHERE a.statistic# = b.statistic#

AND b.NAME IN ('redo wastage', 'redo size')

注：由於LGWR不象DBWR那樣能夠有多個，所以儘可能將REDO放在IO快的磁碟結構上，不要放在象RAID5這樣的磁碟上。

4 可以使用下需的查詢來找到每次寫入的平均重做日誌塊的數量，以及以位元組為單位的LGWR IO平均大小：

SELECT round((a.VALUE / b.VALUE) + 0.5, 0) AS avg_redo_blks_per_write,

round((a.VALUE / b.VALUE) + 0.5, 0) * c.lebsz AS avg_io_size -- 位元組為單位

FROM v$sysstat a, v$sysstat b, x$kccle c

WHERE c.lenum = 1

AND a.NAME = 'redo blocks written'

AND b.NAME = 'redo writes'

注：x$kccle.lebsz欄位包含每個日誌塊的大小。

【等待事件之二】log 相關的等待

相關文章