客户的一套测试库系统响应缓慢，通SQLPLUS登陆到数据库中大约5-6秒才能登陆进去，正常情况下也就1秒即可登陆，简单的一个show parameter 命令也得好几秒才返回。
登陆到数据库中发现ALERT文件报了大量的ORA-3136错误信息。
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
Tue Jan 10 11:19:17 2012
WARNING: inbound connection timed out (ORA-3136)
ORA-3136错误一般在网络不稳定，数据库系统资源耗尽的时候，客户端进行连接的时候容易出现。
topas一下发现系统的内存资源耗光了，交换空间都用掉了30%多。
MEMORY
Real,MB 32768
% Comp 39.6
% Noncomp 61.2
% Client 61.2
PAGING SPACE
Size,MB 32768
% Used 31.1
% Free 68.8
大部分内存都被客户端分页占掉了。
数据库的物理内存为32G，交换空间为32G如下：
$ lsattr -El mem0
goodsize 32768 Amount of usable physical memory in Mbytes False
size 32768 Total amount of physical memory in Mbytes False
$ lsps -a
Page Space Physical Volume Volume Group Size %Used Active Auto Type
paging00 hdisk1 rootvg 16384MB 31 yes yes lv
hd6 hdisk0 rootvg 16384MB 31 yes yes lv
$
检查了一下数据库配置ORACLE的SGA,PGA都设置的不大
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ---------------------- -----------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 10G
sga_target big integer 10G
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ---------------------- -----------
pga_aggregate_target big integer 4G
总共才14G，还有大约18G的空间可供操作系统利用，不应该出现内存紧张的问题。
$ vmstat -v
8388608 memory pages
7961825 lruable pages
10110 free pages
4 memory pools
994480 pinned pages
80.0 maxpin percentage
20.0 minperm percentage
80.0 maxperm percentage
63.9 numperm percentage
5093543 file pages
0.0 compressed percentage
0 compressed pages
63.9 numclient percentage
80.0 maxclient percentage
5093543 client pages
0 remote pageouts scheduled
32561 pending disk I/Os blocked with no pbuf
18706130 paging space I/Os blocked with no psbuf
2740 filesystem I/Os blocked with no fsbuf
200 client filesystem I/Os blocked with no fsbuf
1904898 external pager filesystem I/Os blocked with no fsbuf
0 Virtualized Partition Memory Page Faults
0.00 Time resolving virtualized partition memory page faults
通过vmstat -v 发现系统的内核参数 maxperm, maxclient 都设置为80%，对于数据库系统来说，这个设置的太高了。
numperm percentage 都达到了63.9 了，大部分内存都被文件系统缓存占掉了。
切换到root用户执行如下命令：
# vmo -p -o maxclient%=30 -o maxperm%=30 -o minperm%=10 -o strict_maxclient=1
Setting minperm% to 10 in nextboot file
Setting maxperm% to 30 in nextboot file
Setting maxclient% to 30 in nextboot file
Setting strict_maxclient to 1 in nextboot file
Setting minperm% to 10
Setting maxperm% to 30
Setting maxclient% to 30
Setting strict_maxclient to 1
# vmo -L | grep strict
strict_maxclient 1 1 1 0 1 boolean D
strict_maxperm
strict_maxperm 0 0 0 0 1 boolean D
strict_maxclient
一段时间后系统内存恢复正常;
$ vmstat -v
8388608 memory pages
7961825 lruable pages
2700191 free pages
4 memory pools
994864 pinned pages
80.0 maxpin percentage
10.0 minperm percentage
30.0 maxperm percentage
29.9 numperm percentage
2384473 file pages
0.0 compressed percentage
0 compressed pages
29.9 numclient percentage
30.0 maxclient percentage
2384473 client pages
0 remote pageouts scheduled
32561 pending disk I/Os blocked with no pbuf
18706130 paging space I/Os blocked with no psbuf
2740 filesystem I/Os blocked with no fsbuf
200 client filesystem I/Os blocked with no fsbuf
1904898 external pager filesystem I/Os blocked with no fsbuf
0 Virtualized Partition Memory Page Faults
0.00 Time resolving virtualized partition memory page faults
numperm percentage 已经大大降低。
topas系统内存情况如下：
MEMORY
Real,MB 32768
% Comp 39.6
% Noncomp 28.9
% Client 28.9
再次登录数据库，即可瞬间完成。
阅读(3338) | 评论(1) | 转发(5) |
