一次pga异常消耗分析

一次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

一次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