Oracle Memory Consumption – Identify Memory Usage from a Specific Session

oracle

We are using Oracle 11g standard, without any options (diag/tunning/perf).

I can identify one session with the SQL bellow which having a high consumption (1.4 GB) of memory on the past hours… (pga and uga)

There is someway to get details about this consumption?
Whether it is the use of temporary tables or something else?

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
 ;

Best Answer

After some research I've discovered the view V$PROCESS_MEMORY_DETAIL and how use it.
By default it's always empty and need some commands to "enable" it (oradebug or alter session).

References I found:

This quote from Oracle Article says everything :

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>;

and a useful 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

Here is the informations detailed...
Just needs digging the mysterious names . 

  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.
Related Question