第一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层面的内存消耗
这里提供其它查看进程内存慷慨法
----查看相应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 SQL 1.3077E+10 1.3081E+10 1.3081E+10 oracle 41877820 PL/SQL 224 2008 2008 oracle 41877820 Other 2178876745 217887674533554712:
USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED --------------- ------------ ------------------------------------------------ --------------- ---------- ---------- ------------- oracle 33554712 SQL 5380315176 5382747784 5382747784 oracle 33554712 PL/SQL 26240 31224 37560 oracle 33554712 Other 4398399145 4398399145USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED
--------------- ------------ ------------------------------------------------ --------------- ---------- ---------- ------------- oracle 16318890 SQL 1.3066E+10 1.3070E+10 1.3070E+10 oracle 16318890 PL/SQL 224 2008 2008 oracle 16318890 Other 2185355529 2185355529 USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED --------------- ------------ ------------------------------------------------ --------------- ---------- ---------- ------------- oracle 57540768 SQL 1.3291E+10 1.3294E+10 1.3294E+10 oracle 57540768 PL/SQL 224 2008 2008 oracle 57540768 Other 2093690553 2093690553USERNAME SPID PROGRAM CATEGORY USED ALLOCATED MAX_ALLOCATED
--------------- ------------ ------------------------------------------------ --------------- ---------- ---------- ------------- oracle 23396516 SQL 1.1608E+10 1.1611E+10 1.1611E+10 oracle 23396516 PL/SQL 224 2008 2008 oracle 23396516 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 17032、開始跟踪
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_destNAME 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
版权声明：本文博客原创文章，博客，未经同意，不得转载。