資料庫異常緩慢的解決 - FAST_START_PARALLEL_ROLLBACK

現象：

1. 應用連線資料異常緩慢。

2，資料庫主機cpu佔用率居高不下 ， io 寫入居高不下。

分析：

1 首先，懷疑應用有問題。

因為多個應用連線資料庫，所以一個個應用停止後，檢視其他應用的資料庫執行速度。

經過測試。 只剩下最後一個應用後，執行速度仍然很慢，資料庫cpu仍然佔用很高。

2 接著懷疑是資料庫的問題。

接著懷疑是資料庫的問題， 把最後一個應用也停止， 把監聽器停止。重啟資料庫，發現主機的io, cpu仍然是居高不下。把資料庫關閉後，io,cpu馬上就到了清閒狀態。

重啟主機，啟動資料庫，資料庫open很慢.

shutdown immediate 資料庫很慢，關閉期間觀察alert日誌。發現redo日誌從第一個切換到最後一個，然後又開始一倫的切換。

為什麼沒有任何應用接入的情況下，資料庫又大量的日誌產生呢？

在觀察後臺的程式，發現有ora_p001,ora_p002........ora_p009.的後臺。比較其他生產資料庫，並沒有這些程式。看來真的是資料庫自己再做大量的操作。

那麼資料庫究竟在做什麼操作呢？

首先觀察一下IO

$ iostat 5 3 -- ----5秒收集一次io,收集3次

avg-cpu: %user %nice %sys %iowait %idle
9.48 0.00 2.30 31.16 57.06

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 12.03 85.00 6006.35 1030538 72817516
sda1 0.05 0.11 0.00 1332 4

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 146.09 2327.86 25.65 11616 128 ---------- sda是資料檔案所在的位置。
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 148

tps 意義: 平均每秒鐘的IO請求次數.

生產系統最繁忙的時候 tps 也就等於30左右，而這個空轉的資料庫居然有146次。

觀察一下資料庫又那些等待
SQL> select SID ,SEQ# ,EVENT from v$session_wait;

SID SEQ# EVENT
---------- ---------- --------------------------------------------------
189 2 Streams AQ: qmn slave idle wait
192 2 Streams AQ: waiting for time management or cleanup
tasks

197 1 jobq slave wait
201 6 Streams AQ: qmn coordinator idl
---------- ---------- --------------------------------------------------
208 4763 wait for a undo record
209 4442 wait for a undo record
211 15696 db file sequential read
214 134 SQL*Net message to client
215 6 rdbms ipc message
216 1065 rdbms ipc message
217 109 rdbms ipc mes
SID SEQ# EVENT
---------- ---------- --------------------------------------------------
222 280 rdbms ipc message
223 20 rdbms ipc message
224 68 rdbms ipc message
225 7 pmon timer

哪來的undo呢？

最後在google上根據ora_p001, wait for a undo record 的關鍵字，找到了一些資訊，以下資訊引起了我的注意：

Oracle工程師首先懷疑是臨時表空間空間不足導致，經檢查臨時表空間沒有空間不足的情況，仔細觀察日誌發現重做日誌檔案不斷切換，分析應該是有較多的事務沒有完成提交或者有較多沒有提交的事務完成回滾。現在面臨的問題是我們沒有很多時間去等待所有的事務去完成回滾或提交。解決問題的思路就是如何儘快結束這些事務的回滾或提交。

根據文章提到的一個加快回滾的引數，fast_start_parallel_rollback 。試著修改一下。

sqlplus > shutdown immediate

sqlplus > startup

sqlplus > alter system set fast_start_parallel_rollback = FALSE scope=spfile

sqlplus > alter database open;

啟動後，過了不久，發現IO,CPU恢復到正常水平。

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 2.67 0.00 0.67 0.00 26.67 0.00 13.33 40.00 0.01 9.50 9.50 0.63
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

重啟應用，發現應用連線資料庫的速度也達到了正常水平。

自此，調優結束。

載錄一下相關資訊：

Oracle工程師首先懷疑是臨時表空間空間不足導致，經檢查臨時表空間沒有空間不足的情況，仔細觀察日誌發現重做日誌檔案不斷切換，分析應該是有較多的事務沒有完成提交或者有較多沒有提交的事務完成回滾。現在面臨的問題是我們沒有很多時間去等待所有的事務去完成回滾或提交。解決問題的思路就是如何儘快結束這些事務的回滾或提交。

　　1) 檢視spfile檔案中是否有fast_start_parallel_rollback引數的設定，檢查結果G網資料庫沒有設定該引數。如果沒有顯式設定，則該引數的預設值為low。修改該引數值為false

　　2) 將資料庫啟動到nomount狀態：startup nomount

　　3) 修改改引數值：alter system set fast_start_parallel_rollback = FALSE scope=spfile

　　4) shutdown immediate關閉資料庫

　　5) startup啟動

　　6) 檢視該引數是否生效：show parameter fast_start_parallel_rollback

　　7) 等待一段時間

　　8) shutdown immediate資料庫可以關閉

　　分析：FAST_START_PARALLEL_ROLLBACK是用來控制事務並行回滾最大程式數的引數。該引數有三個可設值，low，high，false。當設定為false時並行回滾被禁止，由於禁止了並行回滾，在資料庫關閉時，需要回滾的事務將被取消。

並行例項恢復

　　如果 DML 操作是並行操作，則列 QCSID 顯示並行查詢伺服器會話的 SID。在並行回滾事件中，如例項恢復以及隨後的故障事務恢復期間，經常用到該資訊經常。

　　例如，假設在大型的更新期間，例項異常關閉。當例項啟動時，發生故障的事務被回滾。如果啟用了用於並行恢復的初始化引數值，則回滾並行地而不是序列地發生，如同它發生在常規事務回滾中一樣。下一步的任務是評估回滾程式的完成時間。

　　檢視 V$FAST_START_TRANSACTIONS 顯示為回滾故障事務所產生的事務。類似的檢視 V$FAST_START_SERVERS 顯示對回滾進行處理的並行查詢伺服器的數量。這兩個檢視都在以前的版本中提供，但顯示事務識別符號的新列 XID 使得聯接更方便了。在 Oracle9i Database 以及更低的版本中，您必須透過三列(USN — 重做段號，SLT — 重做段中的儲存區號，SEQ — 序列號)來聯接檢視。其父集顯示在 PARENTUSN、PARENTSLT 和 PARENTSEQ 中。在 Oracle Database 10g 中，您只需將其聯接到 XID 列，其父 XID 由直觀的名稱表示：PXID。

　　最有用的資訊部分來自於 V$FAST_START_TRANSACTIONS 檢視中的列 RCVSERVERS。如果發生並行回滾，則該列中顯示並行查詢伺服器的數量。您可以檢視該列，瞭解啟動了多少並行查詢程式：

　　如果輸出是 1，則事務正在由 SMON 程式進行序列回滾 — 顯然這是完成工作的一種不充分的方法。您可以將初始化引數 RECOVERY_PARALLELISM 的值改為除 0 或 1 以外的值，重新啟動例項進行並行回滾。隨後您可以執行 ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH，按 CPU 數量的 4 倍建立並行伺服器。

　　如果上述查詢的輸出顯示不是 1，則正在進行並行回滾。您可以查詢同一檢視 (V$FAST_START_TRANSACTIONS) 來獲得父事務和子事務(父事務 id — PXID，而子事務 id — XID)。XID 還可用於聯接此檢視與 V$FAST_START_SERVERS，以獲得其他詳細資訊。

　　結論

　　總之，當在 Oracle Database 10g 中回滾長期執行的事務時 — 無論是並行例項恢復會話還是使用者執行的回滾語句 — 您所需做的一切就是檢視檢視 V$SESSION_LONGOPS 並評估還需要多少時間。