一次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
---------这里继续跟踪相应应用的session 来确定这个应用中到底是那些操作造成了如此大的内存开销
1、找出应用的sid和serial#
sql> select sid,serial# from v$session where lower(program) like '%isap_client%';
sid serial#
---------- ----------
78 1703
2、开始跟踪
sql> exec sys.dbms_system.set_sql_trace_in_session(78,1703,true);
pl/sql procedure successfully completed.
3、期间做点关于这个应用的操作（保证和数据库能有交互）
4、停止跟踪，在user_dump_dest目录下会生成跟踪的trace文件
sql> exec sys.dbms_system.set_sql_trace_in_session(78,1703,false);
pl/sql procedure successfully completed.
5、找出并进入user_dump_dest目录，最后生成那个文件就是要用的trace文件
sql> set lines 1024
sql> show parameter user_dump_dest
name type value
------------------------------------ -------------------------------- ------------------------------
user_dump_dest string /opt/oracle/db01/app/oracle/ad
min/orcl/udump
sql> exit
$ cd /opt/oracle/db01/app/oracle/admin/orcl/udump
$ ls -otr | tail -1
-rw-r----- 1 oracle 576097 jan 8 16:15 orcl_ora_24884.trc
$
6、使用tkprof格式化trace文件，sys=no的意思是不查看sys用户的操作，看了也没啥用
$ tkprof orcl_ora_24884.trc report.txt sys=no
tkprof: release 9.2.0.4.0 - production on tue jan 8 16:19:35 2008
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
$
7、查看格式化后的文件report.txt，里头就有跟踪期间发生的所有存储过程（存储过程中执行的sql操作也会逐条显示）和sql操作，还有各sql执行的统计数据，可以了解哪些sql快，哪些慢了
$ more report.txt