Oracle10.2.0.3 fox aix 上 In memory undo latch導致高CPU佔用問題解決

aix5306上執行的oracle 10.2.0.3資料庫，今天報告cpu佔用率很高，資料庫處於不可用狀態。

經過從v$session_wait檢查wait等待事件，發現大量的latch free事件。
檢查v$latch，發現大量的in memory undo latch

生成awrrpt，top5 等待事件如下：

Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- read by other session 319,603,864 184,097 1 32.0 User I/O CPU time 98,077 17.0 db file sequential read 93,162,824 91,149 1 15.8 User I/O enq: TX - row lock contention 31,135 90,858 2918 15.8 Applicatio latch: In memory undo latch 248,239 42,632 172 7.4 Concurrenc

[@more@]

因為read by other session需要定位hot block，應該不是導致高cpu佔用率的問題；
enq: TX - row lock contention等待是因為最近執行了很多select × from x for update鎖定了表導致，暫不考慮。
懷疑latch: In memory undo latch是導致高cpu佔用率的主要原因。

同時發現latch命中率很低：

Latch Hit %: 84.79

查詢metalink，發現：Bug 5751672 - "In memory undo latch" contention from kturimugur [ID 5751672.8]
針對這個問題，透過設定 _in_memory_undo=false可以暫時解決問題

下面是操作過程

SQL> alter system set "_in_memory_undo"=false scope=spfile; SQL> shutdown immediate; SQL> startup;

重啟資料庫後，cpu佔用率明顯降低，資料庫執行正常。
經過半天觀察，並生產awrrpt。
awrrpt的等待事件也趨於正常，如下：

Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- CPU time 63,073 58.2 db file sequential read 26,410,301 27,076 1 25.0 User I/O read by other session 10,001,589 4,749 0 4.4 User I/O direct path read temp 7,984,856 4,226 1 3.9 User I/O db file scattered read 3,709,102 3,924 1 3.6 User I/O -------------------------------------------------------------

latch命中率顯著提高

Latch Hit %: 97.05

--end--