How to analyse process memory growing

可以通过V$PROCESS_MEMORY and V$PROCESS_MEMORY_DETAI视图去分析
V$PROCESS_MEMORY_DETAIL视图需要通过以下的方法激活
            SQL> alter session set events'immediate trace name PGA_DETAIL_GET level ';
       From ORADEBUG:
       SQL> ORADEBUG SETMYPID;
       SQL> ORADEBUG DUMP PGA_DETAIL_GET ;
    - To remove all rows in the view run following command:
       SQL> alter session set events'immediate trace name PGA_DETAIL_CANCEL level ';
       From ORADEBUG:
       SQL> ORADEBUG DUMP PGA_DETAIL_CANCEL ;
Step 1
    首先确定哪个session的内存在不断的增长
     SQL> COLUMN alme     HEADING "Allocated MB" FORMAT 99999D9
   SQL> COLUMN usme     HEADING "Used MB"      FORMAT 99999D9
   SQL> COLUMN frme     HEADING "Freeable MB"  FORMAT 99999D9
   SQL> COLUMN mame     HEADING "Max MB"       FORMAT 99999D9
   SQL> COLUMN username                        FORMAT a15
   SQL> COLUMN program                         FORMAT a22
   SQL> COLUMN sid                             FORMAT a5
   SQL> COLUMN spid                            FORMAT a8
   SQL> SET LINESIZE 300
   SQL> SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
               SUBSTR(s.program,1,22) program , s.process pid_remote,
               ROUND(pga_used_mem/1024/1024) usme,
               ROUND(pga_alloc_mem/1024/1024) alme,
               ROUND(pga_freeable_mem/1024/1024) frme,
               ROUND(pga_max_mem/1024/1024) mame
        FROM  v$session s,v$process p
        WHERE p.addr=s.paddr
        ORDER BY pga_max_mem,logon_time ;

            You will get output:

        USERNAME  SID   SPID     LOGON_TIM PROGRAM  PID_REMOTE    Used MB Allocated MB Freeable MB   Max MB
        --------- ----- -------- --------- -------- ------------ -------- ------------ ----------- --------
        TEST       141   3095     08-MAY-09 test    3080             14.0         15.0          .0     15.0
        
    稍等一会儿后再次执行该语句看看那个sid内存增加。这里假设是sid 141内存增长较快
    
Setp 2
通过v$Process_memory来确定哪个Category增长最快
  SQL> COLUMN category      HEADING "Category"
   SQL> COLUMN allocated     HEADING "Allocated bytes"
   SQL> COLUMN used          HEADING "Used bytes"
   SQL> COLUMN max_allocated HEADING "Max allocated bytes"
   SQL> SELECT pid, category, allocated, used, max_allocated
        FROM   v$process_memory
        WHERE  pid = (SELECT pid
                      FROM   v$process
                      WHERE  addr= (select paddr
                                    FROM   v$session
                                    WHERE  sid = 141));

   PID        Category        Allocated bytes Used bytes Max allocated bytes
   ---------- --------------- --------------- ---------- -------------------
           22 SQL                      191888     160792             3500976
           22 PL/SQL                    35448      27912               35448
           22 Freeable                 262144          0      
           22 Other                  30846677                       30846677
     这里显示是“Other"增长较快，可以多执行几次看看增长情况。并且确认PID 是 22

Step 3
    激活V$PROCESS_MEMORY_DETAIL视图
    SQL> alter session set events'immediate trace name PGA_DETAIL_GET level 22'

            or:
    
  SQL> ORADEBUG SETMYPID;
    SQL> ORADEBUG DUMP PGA_DETAIL_GET 22;

  将当前的数据存储到临时表tab1中
  SQL> CREATE TABLE tab1 AS
         SELECT category, name, heap_name, bytes, allocation_count,
                heap_descriptor, parent_heap_descriptor
         FROM   v$process_memory_detail
         WHERE  pid      = 22
         AND    category = 'Other';
         
  等一段时间再次收集数据
  SQL> alter session set events'immediate trace name PGA_DETAIL_GET level 22'

            or:
    
  SQL> ORADEBUG SETMYPID;
    SQL> ORADEBUG DUMP PGA_DETAIL_GET 22;
    
    将当前的数据存储到临时表tab2中
     SQL> CREATE TABLE tab2 AS
         SELECT category, name, heap_name, bytes, allocation_count,
                heap_descriptor, parent_heap_descriptor
         FROM   v$process_memory_detail
         WHERE  pid      = 22
         AND    category = 'Other';

Step 4 通过比较临时表确定哪个区域内存增加最多
  SQL> COLUMN category      HEADING "Category"
    SQL> COLUMN name          HEADING "Name"
    SQL> COLUMN heap_name     HEADING "Heap name"
    SQL> COLUMN q1            HEADING "Memory 1st"  Format 999,999,999,999
    SQL> COLUMN q2            HEADING "Memory 2nd"  Format 999,999,999,999
    SQL> COLUMN diff          HEADING "Difference"  Format S999,999,999,999
    SQL> SET LINES 150

    SQL> SELECT tab2.category, tab2.name, tab2.heap_name, tab1.bytes q1, tab2.bytes q2, tab2.bytes-tab1.bytes diff
         FROM   tab1, tab2
         WHERE  tab1.category  =  tab2.category
         AND    tab1.name      =  tab2.name
         AND    tab1.heap_name =  tab2.heap_name
         AND    tab1.bytes     <> tab2.bytes
         ORDER BY 6 DESC;

Category  Name                     Heap name         Memory 1st   Memory 2nd   Difference
--------- ------------------------ --------------- ------------ ------------ ------------
    Other     permanent memory         kolaGetRfcHeap    30,059,544   36,416,968   +6,357,424
    Other     free memory              session heap       6,475,848    7,638,528   +1,162,680
    Other     free memory              kolaGetRfcHeap       479,832      586,856     +107,024
    Other     kolasl: kolaslCreateCtx  koh dur heap d       441,304      528,600      +87,296
    Other     kolaGetRfcHeap:sheap     koh dur heap d       402,152      481,688      +79,536
    Other     kolraloc-1               kolr heap ds i       206,800      247,728      +40,928
    Other     free memory              kolr heap ds i         8,608       35,640      +27,032
    Other     free memory              pga heap              96,192      112,648      +16,456
    Other     free memory              top uga heap          54,392       63,752       +9,360
    Other     free memory              lpxHeap subhea       198,088      200,912       +2,824
    Other     free memory              Alloc server h         7,416        8,928       +1,512
    Other     permanent memory         qmxtkAggCrtAgg         4,016        4,120         +104
    Other     free memory              koh dur heap d       178,120      175,840       -2,280
    Other     free memory              koh-kghu call         17,456        3,120      -14,336
    Other     kollalo2                 koh-kghu sessi        41,032        8,216      -32,816

 The query show that the largest memory increases is in kolaGetRfcHeap.

The output from view V$PROCESS_MEMORY_DETAIL can be compared with heapdump.
Output from view V$PROCESS_MEMORY_DETAIL:

SQL> COLUMN heap_name        HEADING "heap name"
SQL> COLUMN name             HEADING "Type"
SQL> COLUMN allocation_count HEADING "Count"
SQL> COLUMN bytes            HEADING "Sum"
SQL> COLUMN avg              HEADING "Average" FORMAT 99999D99
SQL> SELECT heap_name, name, allocation_count, bytes,
     bytes/allocation_count avg
     FROM   tab2
     WHERE  heap_name = 'kolaGetRfcHeap';

heap name       Type                            Count        Sum   Average
--------------- -------------------------- ---------- ---------- ---------
kolaGetRfcHeap  free memory                      5886     586856     99.70
kolaGetRfcHeap  permanent memory                 8949   36416968   4069.38