Identify in details the consumption of memory from a specific session

Problem

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
 ;

Solution

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:

How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)

Oracle Memory Troubleshooting, Part 4: Drilling down into PGA memory usage with V$PROCESS_MEMORY_DETAIL

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

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 0

Code Snippets

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

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           permanen

Context

StackExchange Database Administrators Q#61178, answer score: 3

Revisions (0)

No revisions yet.