shared pool 相关latch出现异常等待,影响系统性能

某客户有一服务器,shared pool 相关latch出现异常等待,影响系统性能.分析结果:因为系统空闲内存太少,使用太多Paging Space导致该异常;解决办法:1.增加内存，2.在业务接受范围内减小sga等其他和内存消耗相关参数
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        |           ------

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 2013   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 <---fs cache较少 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

内存参数配置

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 推荐值(大页没有配置,非必须选项)

会话进程占用内存

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左右.

数据库参数配置

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结果为: