一次pga 异常消耗分析

os： aix 6

db：10205

------使用os 命令观察oracle 进程内存消耗情况
#ps gv
...... size
23396516 - A 14907:02 5203 14324376 2058800 xx 90815 38576 2.0 3.0 oracle
23396516 - A 14907:02 5203 14324376 2058800 xx 90815 38576 2.0 3.0 oracle
57540768 - A 3711:33 8331 15108164 15146740 xx 90815 38576 1.9 23.0 oracle
16318890 - A 10639:03 187 15110924 15009560 xx 90815 38576 2.0 23.0 oracle
33554712 - A 17263:24 13740 9598740 185552 xx 90815 38576 2.1 0.0 oracle
41877820 - A 10492:10 826 15110484 15018716 xx 90815 38576 2.0 23.0 oracle
..............

单个进程消耗近15g 明显有问题啊

AIX/LINUX 如何查看单个进程在os层面的内存消耗 http://blog.csdn.net/lixora/article/details/24060299

这里提供其他查看进程内存大方法

----查看对应oracle的应用进程内存消耗情况
select p.username,p.spid,p.program,pm.category,pm.used,pm.allocated,pm.max_allocated
from v$process p ,v$process_memory pm；
-----where p.pid=pm.pid and program like '%TNS%';

41877820 :
USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED
--------------- ------------ ------------------------------------------------ --------------- ---------- ---------- -------------
oracle 41877820 oracle@sssdb SQL 1.3077E+10 1.3081E+10 1.3081E+10
oracle 41877820 oracle@sssdb PL/SQL 224 2008 2008
oracle 41877820 oracle@sssdb Other 2178876745 2178876745

33554712:
USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED
--------------- ------------ ------------------------------------------------ --------------- ---------- ---------- -------------
oracle 33554712 oracle@sssdb SQL 5380315176 5382747784 5382747784
oracle 33554712 oracle@sssdb PL/SQL 26240 31224 37560
oracle 33554712 oracle@sssdb Other 4398399145 4398399145

USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED
--------------- ------------ ------------------------------------------------ --------------- ---------- ---------- -------------
oracle 16318890 oracle@sssdb SQL 1.3066E+10 1.3070E+10 1.3070E+10
oracle 16318890 oracle@sssdb PL/SQL 224 2008 2008
oracle 16318890 oracle@sssdb Other 2185355529 2185355529

USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED
--------------- ------------ ------------------------------------------------ --------------- ---------- ---------- -------------
oracle 57540768 oracle@sssdb SQL 1.3291E+10 1.3294E+10 1.3294E+10
oracle 57540768 oracle@sssdb PL/SQL 224 2008 2008
oracle 57540768 oracle@sssdb Other 2093690553 2093690553

USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED
--------------- ------------ ------------------------------------------------ --------------- ---------- ---------- -------------
oracle 23396516 oracle@sssdb SQL 1.1608E+10 1.1611E+10 1.1611E+10
oracle 23396516 oracle@sssdb PL/SQL 224 2008 2008
oracle 23396516 oracle@sssdb Other 2769131497 2769131497

大部分内存花销在sql 上

select program, MODULE, TERMINAL , MACHINE from v$session where paddr in (select addr from v$process where spid in(23396516,57540768,33554712,16318890 ,41877820));

----确定相应的应用
SQL> select program, MODULE, TERMINAL , MACHINE from v$session where paddr in (select addr from v$process where spid in(23396516,57540768,33554712,16318890 ,41877820));

PROGRAM MODULE TERMINAL MACHINE
------------ ------------- ----------------- ----------------
xxx.exe xxx.exe sfd DC9A29A
xxx.exe xxx.exe sdf72172 WOR722C72172
xxx.exe xxx.exe GR5FD WORKG-4715FD
xxx.exe xxx.exe FSK051 WOBG051
xxx.exe xxx.exe CD3LVB0U MSVWCVB0U