天萃荷净
IBM aix小型机内存使用太多导致Oracle数据库shared pool 相关latch异常
某客户有一服务器,shared pool 相关latch出现异常等待,影响系统性能.
分析结果:因为系统空闲内存太少,使用太多Paging Space导致该异常;解决办法:
1.增加内存,
2.在业务接受范围内减小sga等其他和内存消耗相关参数
IBM AIX命令nmon查看剩余内存
x Physical PageSpace | pages/sec In Out | FileSystemCache
x% Used 99.8% 34.9% | to Paging Space 0.0 0.0 | (numperm) 14.8%
x% Free 0.2% 65.1% | to File System 0.0 33.0 | Process 63.9%
xMB Used 21452.8MB 11446.1MB | Page Scans 0.0 | System 21.1%
xMB Free (少)-->51.2MB 21321.9MB | Page Cycles 0.0 | Free 0.2%
xTotal(MB) 21504.0MB 32768.0MB | Page Steals 0.0 | ------
IBM AIX命令topas查看内存配置
Disk Busy% KBPS TPS KB-Read KB-Writ MEMORY
Topas Monitor for host: p570b03 EVENTS/QUEUES FILE/TTY
Wed Jan 9 13:30:30 Interval: 2 Cswitch 785 Readch 173.1K
Syscall 54407 Writech 213.1K
CPU User% Kern% Wait% Idle% Reads 118 Rawin 0
ALL 43.6 1.7 0.0 54.8 Writes 110 Ttyout 352
Forks 0 Igets 0
Network KBPS I-Pack O-Pack KB-In KB-Out Execs 0 Namei 5
Total 84.5 146.0 200.6 26.4 58.1 Runqueue 0.5 Dirblk 0
Waitqueue 0.0
Disk Busy% KBPS TPS KB-Read KB-Writ MEMORY
Total 0.0 164.6 17.0 0.0 164.6 PAGING Real,MB 21504
Faults 12408 % Comp 86
FileSystem KBPS TPS KB-Read KB-Writ Steals 0 % Noncomp 13
Total 316.3 17.9 151.5 164.9 PgspIn 0 % Client 13
PgspOut 0
Name PID CPU% PgSp Owner PageIn 0 PAGING SPACE
oracle 6357252 16.7 8.4 oracle PageOut 42 Size,MB 32768
oracle 15401474 8.3 70.8 oracle Sios 42 % Used 35
oracle 12714542 8.3 8.3 oracle % Free 65
oracle 5767556 8.3 8.3 oracle NFS (calls/sec)
oracle 5898996 8.3 134.9 oracle SerV2 0 WPAR Activ 0
oracle 17629634 8.3 134.9 oracle CliV2 0 WPAR Total 0
oracle 13959694 0.0 8.4 oracle SerV3 0 Press: "h"-help
oracle 5439860 0.0 134.3 oracle CliV3 0 "q"-quit
查看IBM AIX命令内存参数配置
vmo -F -a
--数据库相关参数
minperm% = 3
v_pinshm = 0
lru_file_repage = 0
maxclient% = 90
maxperm% = 90
strict_maxclient = 1
strict_maxperm = 0
page_steal_method = 1
因为是AIX 6.1,这里的vmo配置基本上是oracle 推荐值(大页没有配置,非必须选项)
查看IBM AIX会话进程占用内存
procmap 15466998
15466998 : oraclewasudb (LOCAL=NO)
100000000 97466K read/exec oracle
11000088d 2430K read/write oracle
9fffffff0000000 51K read/exec /usr/ccs/bin/usla64
9fffffff000cfe2 0K read/write /usr/ccs/bin/usla64
900000000b14930 2K read/exec /usr/lib/libC.a[shr3_64.o]
9001000a0122930 0K read/write /usr/lib/libC.a[shr3_64.o]
900000000af5b00 118K read/exec /usr/lib/libC.a[shrcore_64.o]
9001000a0319100 12K read/write /usr/lib/libC.a[shrcore_64.o]
900000000ad7000 118K read/exec /usr/lib/libC.a[ansicore_64.o]
9001000a030fe00 36K read/write /usr/lib/libC.a[ansicore_64.o]
900000000411468 0K read/exec /usr/lib/libicudata.a[shr_64.o]
9001000a0121468 0K read/write /usr/lib/libicudata.a[shr_64.o]
90000000040f738 2K read/exec /usr/lib/libC.a[shr2_64.o]
9001000a0323738 0K read/write /usr/lib/libC.a[shr2_64.o]
9000000008ec800 1699K read/exec /usr/lib/libC.a[ansi_64.o]
9001000a0324a00 277K read/write /usr/lib/libC.a[ansi_64.o]
9000000008c9b00 135K read/exec /usr/lib/libC.a[shr_64.o]
9001000a031db00 19K read/write /usr/lib/libC.a[shr_64.o]
900000000708180 1732K read/exec /usr/lib/libicuuc.a[shr_64.o]
9001000a036bdac 180K read/write /usr/lib/libicuuc.a[shr_64.o]
900000000493d80 2510K read/exec /usr/lib/libicui18n.a[shr_64.o]
9001000a0399148 270K read/write /usr/lib/libicui18n.a[shr_64.o]
900000000473200 91K read/exec /usr/lib/libsrc.a[shr_64.o]
9001000a01127a8 55K read/write /usr/lib/libsrc.a[shr_64.o]
90000000045a300 98K read/exec /usr/lib/libcorcfg.a[shr_64.o]
9001000a04147c8 18K read/write /usr/lib/libcorcfg.a[shr_64.o]
900000000b16200 750K read/exec /usr/lib/liblvm.a[shr_64.o]
9001000a03dd028 219K read/write /usr/lib/liblvm.a[shr_64.o]
900000000444f00 82K read/exec /usr/lib/libcfg.a[shr_64.o]
9001000a027b8f0 26K read/write /usr/lib/libcfg.a[shr_64.o]
90000000040e3a0 2K read/exec /usr/lib/libcrypt.a[shr_64.o]
9001000a0106948 0K read/write /usr/lib/libcrypt.a[shr_64.o]
90000000233c860 5K read/exec /usr/lib/libc.a[aio_64.o]
9001000a0437568 0K read/write /usr/lib/libc.a[aio_64.o]
9000000003efc00 120K read/exec /usr/lib/libodm.a[shr_64.o]
9001000a0107cc8 40K read/write /usr/lib/libodm.a[shr_64.o]
900000000bd2c80 147K read/exec /usr/lib/libperfstat.a[shr_64.o]
9001000a041a960 14K read/write /usr/lib/libperfstat.a[shr_64.o]
900000000bf8000 0K read/exec /usr/lib/libdl.a[shr_64.o]
9001000a041f000 0K read/write /usr/lib/libdl.a[shr_64.o]
9000000024ac100 8680K read/exec /oracle/product/10g/lib/libjox10.a[shr.o]
8001000a0000ca0 588K read/write /oracle/product/10g/lib/libjox10.a[shr.o]
900000000a96000 257K read/exec /usr/lib/libpthreads.a[shr_xpg5_64.o]
9001000a0283000 559K read/write /usr/lib/libpthreads.a[shr_xpg5_64.o]
900000000000800 4025K read/exec /usr/lib/libc.a[shr_64.o]
9001000a0000020 1047K read/write /usr/lib/libc.a[shr_64.o]
Total 123902K
在上表中,标记为read/write的内存即是进程的私有内存,每个会话大概占用内存近6M,数据库大概有80多个会话,占用内存大概,占用内存大概500M左右.
Oracle研究中心查看数据库SGA+PGA+进程参数配置
SQL> select sum(PGA_ALLOC_MEM)/1024/1024/1024,count(*) from v$process;
SUM(PGA_ALLOC_MEM)/1024/1024/1024 COUNT(*)
--------------------------------- ----------
2.46758329 84
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 500M
pga_aggregate_target big integer 2000M
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 12000M
sga_target big integer 10000M
SQL> show sga;
Total System Global Area 1.2583E+10 bytes
Fixed Size 2117744 bytes
Variable Size 7600082832 bytes
Database Buffers 4966055936 bytes
Redo Buffers 14655488 bytes
当前系统整体ORACLE使用内存汇总:sga 12G+pga 2.5G+process 0.5G,大概占用内存15G,留给系统内存6G左右,系统使用大量交换分区,导致系统性能下降,最明显的为:shared pool相关latch等待异常,具体awr为:
因为系统因为个别session需要大量内存设置_pga_max_size参数,导致部分会话系统占用2.5g内存,建议设置该参数为默认值,并对个别会话独立设置,设置pga_aggregate_target=1.5G,sga_target=sga_max_size=8.5G,awr结果为:
-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心
本文由大师惜分飞原创分享,转载请尽量保留本站网址
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之案例:IBM aix小型机内存使用太多导致Oracle shared pool 相关lat