主页 / dba / 问题 / 61178
Accepted
ceinmart
Asked: 2014-03-19 10:47:49 +0800 CST

详细识别特定会话的内存消耗

  • 772

我们使用的是 Oracle 11g 标准，没有任何选项（diag/tunning/perf）。

我可以用下面的 SQL 识别一个会话，该会话在过去几个小时内消耗大量内存（1.4 GB）......（pga 和 uga）

有什么方法可以获取有关此消费的详细信息？
不管是使用临时表还是别的什么？

with x as (select s.osuser osuser , s.username
     , s.status
     , se.sid
     , s.serial# serial
     , n.name
     , round(max(se.value)/1024/1024, 2) maxmem_mb
     , max(se.value) as maxmem
  from v$sesstat se , v$statname n , v$session s
 where n.statistic# = se.statistic#
  and n.name in ('session pga memory','session pga memory max', 'session uga memory','session uga memory max')
   and s.sid        = se.sid
 group by s.osuser, s.username, s.status, se.sid, s.serial#, n.name
 order by maxmem desc 
 ) 
 select * from x where rownum < 5
 ;
oracle

2 个回答

  1. Best Answer

    经过一些研究，我发现了视图 V$PROCESS_MEMORY_DETAIL 以及如何使用它。
    默认情况下它总是空的，需要一些命令来“启用”它（oradebug 或 alter session）。

    我找到的参考资料：

    Oracle 文章中的这句话说明了一切：

    How to use V$PROCESS_MEMORY and V$PROCESS_MEMORY_DETAIL to identify 
where the memory is growing. 

In Oracle 10.2 and higher exist 2 new views that can be used to find 
where the memory continue to grow. This views can be used instead of 
heap dump to find where the memory is growing: 

 - V$PROCESS_MEMORY: 

     This view displays dynamic PGA memory usage by named component categories for each Oracle process. This view will contain up to six 
rows for each Oracle process, one row for: 
      - Java 
      - PL/SQL 
      - OLAP 
      - SQL 
      - Freeable 
      - Other 

 - V$PROCESS_MEMORY_DETAIL 
    Contain break down of memory allocation for each component. 
    - To activate this view can one of following commands executed: 
       SQL> alter session set events'immediate trace name PGA_DETAIL_GET level <PID>'; 
       From ORADEBUG: 
       SQL> ORADEBUG SETMYPID; 
       SQL> ORADEBUG DUMP PGA_DETAIL_GET <PID>; 
    - To remove all rows in the view run following command: 
       SQL> alter session set events'immediate trace name PGA_DETAIL_CANCEL level <PID>'; 
       From ORADEBUG: 
       SQL> ORADEBUG DUMP PGA_DETAIL_CANCEL <PID>;

    和一个有用的 SQL：

    SELECT
    s.sid,p.spid,pm.*
FROM 
    v$session s
  , v$process p
  , v$process_memory pm
WHERE
    s.paddr = p.addr
AND p.pid = pm.pid
-- AND p.spid IN (1423)
AND s.sid IN (244)
ORDER BY
    sid
  , category

    这里有详细的信息……
    只需要挖掘神秘的名字。 

      1* select * from v$process_memory_detail
SQL> /

       PID    SERIAL# CATEGORY        NAME                       HEAP_NAME            BYTES ALLOCATION_COUNT HEAP_DESCRIPTOR  PARENT_HEAP_DESC
---------- ---------- --------------- -------------------------- --------------- ---------- ---------------- ---------------- ----------------
        15          1 SQL             miscellaneous                                   18880               80 00               00
        15          1 Other           koi korfc                  koh-kghu sessi       24544                2 0000002A97A1A1C0 0000002A97935BE0
        15          1 Other           kxsFrame8kPage             session heap         24744                3 0000002A97935BE0 000000000B0BC680
        15          1 PL/SQL          static frame of inst       koh-kghu sessi       26696                3 0000002A97A2BD50 0000002A97935BE0
        15          1 Other           kgsc ht segs               session heap         29824              327 0000002A97935BE0 000000000B0BC680
        15          1 Other           kgh stack                  pga heap             32832                1 000000000B0B6C20 00
        15          1 Other           kopo object                koh-kghu sessi       34728                3 0000002A97A1A1C0 0000002A97935BE0
        15          1 Other           koh-kghu session heap      session heap         35352               15 0000002A97935BE0 000000000B0BC680
        15          1 Other           Fixed Uga                  pga heap             39024                1 000000000B0B6C20 00
        15          1 PL/SQL          miscellaneous                                   64736               47 00               00
        15          1 Other           mark                       Alloc server h      101088               24 0000002A97A99ED0 0000002A9795E090
        15          1 Other           free memory                top call heap       107520                3 000000000B0BC460 00
        15          1 Other           mark                       Alloc environm      111576               18 0000002A9795E090 0000002A97935BE0
        15          1 Other           kxsFrame4kPage             session heap        120408               29 0000002A97935BE0 000000000B0BC680
        15          1 Other           free memory                session heap        345672              213 0000002A97935BE0 000000000B0BC680
        15          1 Other           miscellaneous                                  380264              619 00               00
        15          1 Other           free memory                pga heap            917616             4618 000000000B0B6C20 00
        15          1 Other           permanent memory           koh-kghu call      2618480            32731 0000002AAE10E8C0 000000000B0B6C20
        15          1 Other           permanent memory           koh dur heap d     2619072            32731 0000002A97947E40 0000002A97935BE0
        15          1 Other           permanent memory           session heap       7450512              180 0000002A97935BE0 000000000B0BC680
        15          1 Other           permanent memory           pga heap          23983776             2907 000000000B0B6C20 00
        15          1 Other           kol vstring                koh-kghu call     34298216            32727 0000002AAE10E8C0 000000000B0B6C20
        15          1 Other           kadaih image               koh dur heap d   132475264            65452 0000002AAE0E66C8 000000000B0B6C20
        15          1 Other           koh-kghu call heap         pga heap         164949272            65457 000000000B0B6C20 00
        37         67 Other           mark                       Alloc server h        8480                3 0000002A97870860 0000002A978715C8
        37         67 Other           KFKPGA                     KFK_IO_SUBHEAP        9936                1 0000002A97899E10 000000000B0B6C20
        37         67 Other           dbgr entry                 diag pga             12248              218 0000002A976007E0 000000000B0B6C20
        37         67 Other           permanent memory           diag pga             16056                6 0000002A976007E0 000000000B0B6C20
        37         67 PL/SQL          miscellaneous                                   16232               34 00               00
        37         67 PL/SQL          PL/SQL STACK               PLS PGA hp           16432                2 0000002A9788F150 000000000B0B6C20
        37         67 Other           kgh stack                  pga heap             17024                1 000000000B0B6C20 00
        37         67 Other           dbgeInitProcessCtx:InvCtx  diag pga             17088                2 0000002A976007E0 000000000B0B6C20
        37         67 Other           kgsc ht segs               session heap         17320              286 0000002A979467C8 000000000B0BC680
        37         67 Other           kxsc: kkspsc0 2            session heap         20056               23 0000002A979467C8 000000000B0BC680
        37         67 Other           koh-kghu session heap      session heap         22752               10 0000002A979467C8 000000000B0BC680
        37         67 Other           free memory                top uga heap         31880                1 000000000B0BC680 00
        37         67 SQL             miscellaneous                                   32728               93 00               00
        37         67 Other           kxsFrame16kPage            session heap         32880                2 0000002A979467C8 000000000B0BC680
        37         67 Other           Fixed Uga                  pga heap             39024                1 000000000B0B6C20 00
        37         67 Other           free memory                pga heap             47192                5 000000000B0B6C20 00
        37         67 PL/SQL          recursive addr reg file    koh-kghu sessi       51112                3 0000002A9810AA50 0000002A979467C8
        37         67 Other           permanent memory           session heap         52672                8 0000002A979467C8 000000000B0BC680
        37         67 Other           free memory                session heap         72944               23 0000002A979467C8 000000000B0BC680
        37         67 Other           miscellaneous                                  101120              178 00               00
        37         67 Other           free memory                top call heap       127936                3 000000000B0BC460 00
        37         67 Other           permanent memory           pga heap            175960               22 000000000B0B6C20 00
        37         67 Other           kxsFrame4kPage             session heap        195144               47 0000002A979467C8 000000000B0BC680

47 rows selected.
    • 3

  2. 您可以检查执行了哪个语句以及它的作用。可行的方法是从 v$session 获取 SQL_ID 并将其与 v$sqlstats 连接。

    您可以使用DBMS_XPLAN检查语句的执行计划。

    有了这些信息，您就有了一些使用哪些缓冲区的提示。

    另一种可能性是安装 perfstat，拍摄快照，启动会话，并在会话完成后拍摄另一个快照。perfstat 输出提供了数据库活动的详细信息。

    • 2

相关问题