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

Oracle10.2.0.3 fox aix 上 In memory undo latch导致高CPU占用问题解决

最新文章