AIX6.1/11.2.0.3数据库上关于SWAP的一个BUG

昨天去南京某客户那里调优新上线的业务数据库，在查看alert.log日志时发现在过去的一段时间里，每过几个小时或间隔一段时间，就会报类似以下的内容：
Thu Aug 21 09:01:26 2014 WARNING: Heavy swapping observed on system in last 5 mins. pct of memory swapped in [8.42%] pct of memory swapped out [2.16%]. Please make sure there is no memory pressure and the SGA and PGA are configured correctly. Look at DBRM trace file for more details.
Thu Aug 21 14:56:27 2014 WARNING: Heavy swapping observed on system in last 5 mins. pct of memory swapped in [5.40%] pct of memory swapped out [8.63%]. Please make sure there is no memory pressure and the SGA and PGA are configured correctly. Look at DBRM trace file for more details.
......
Sat Oct 18 22:13:48 2014 WARNING: Heavy swapping observed on system in last 5 mins. pct of memory swapped in [7.76%] pct of memory swapped out [0.33%]. Please make sure there is no memory pressure and the SGA and PGA are configured correctly. Look at DBRM trace file for more details.
客户的环境是IBM P570，AIX 6.1，安装了Oracle 11.2.0.3单实例数据库，物理内存64G，仅仅分配了20G给SGA，采用memory自动管理
查阅了一下MOS，发现是AIX平台上的一个bug，相关文档为：[1508575.1]
对应的数据库和平台：
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2] IBM AIX on POWER Systems (64-bit)
症状：

There is new warning message in alert.log in 11.2.0.3 similar to

WARNING: Heavy swapping observed on system in last 5 mins.
pct of memory swapped in [2.08%] pct of memory swapped out [0.12%].
Please make sure there is no memory pressure and the SGA and PGA 
are configured correctly. Look at DBRM trace file for more details.

On AIX platform this message can be seen even when there is no virtual memory swapping at all.    --物理内存足够，而且根本没有使用swap交换空间

You may compare the vmstat from AIX level with DBRM trace file entries to see the differences.

原因：

The issue is caused by unpublished .
 
Swap usage messages are based on statistics that do not reflect the actual usage.

The v$osstat does not reflect proper stats for the swap space paging.

解决方法：

Apply  on top of your IBM AIX on POWER Systems (64-bit) platform. 

P.S: Bug is port-specific.    --这个bug是针对端口指定的平台的

The issue is fixed in patchset 11.2.0.4 and release 12.1.    --说是在12.1的patch中修复了，但实际上12.1还是会有这个问题，会有ora-700错误，详见文档：[ID 1919850.1]

来看一下BUG：14731911的描述：

VMWare

Under VMWare, the messages may perhaps indicate a more serious issue, even when no memory related ORA-4031/ORA-4030 errors are reported. 
Under circumstances, an instance in a virtual machine may be simply terminated by PMON due to error 471 without further errors in the alert log.
The OS logs may in such case report an out of memory condition like below:

[root@vmh ~]# grep Kill /var/log/messages*
/var/log/messages-20140629:Jun 27 18:29:06 vmh-msfc-dodp02 kernel: [1895074.304941] Out of memory: Kill process 42094 (oracle) score 391 or sacrifice child
/var/log/messages-20140629:Jun 27 18:29:06 vmh-msfc-dodp02 kernel: [1895074.305203] Killed process 42094, UID 303, (oracle) total-vm:189081588kB, anon-rss:27412kB, file-rss:109612

通常解决OS内存swap问题有以下几种方案：

1. 诊断是否存在内存泄露的进程，解决内存泄露 2. 调优SGA/PGA，减少oracle对内存的占用 3. 利用/proc/sys/vm/drop_caches，暂时释放一些cache的内存（Linux） 4. 调整系统VM内存管理参数， 例如Linux上sysctl.conf中的以下几个参数:

vm.min_free_kbytes:Raising the value in /proc/sys/vm/min_free_kbytes will cause the system to start reclaiming memory at an earlier time than it would have before.

vm.vfs_cache_pressure:At the default value of vfs_cache_pressure = 100 the kernel will attempt to reclaim dentries and inodes at a “fair” rate with respect to pagecache and swapcache reclaim. Decreasing vfs_cache_pressure causes the kernel to prefer to retain dentry and inode caches. Increasing vfs_cache_pressure beyond 100 causes the kernel to prefer to reclaim dentries and inodes.

vm.swappiness:default 60,Apparently /proc/sys/vm/swappiness on Red Hat Linux allows the admin to tune how aggressively the kernel swaps out processes‘memory. Decreasing the swappiness setting may result in improved Directory performance as the kernel holds more of the server process in memory longer before swapping it out.

设置以下值，以减少OOM(Out Of Memory)的可能性:

# Oracle-Validated setting for vm.min_free_kbytes is 51200 to avoid OOM killer
vm.min_free_kbytes = 51200
vm.swappiness = 40
vm.vfs_cache_pressure = 200