第一pga 畸形消费分析-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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





版权声明:本文博客原创文章,博客,未经同意,不得转载。





本文转自mfrbuaa博客园博客,原文链接:http://www.cnblogs.com/mfrbuaa/p/4711410.html,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章