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--

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22049049/viewspace-1030175/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22049049/viewspace-1030175/