PL/SQL Multidimensional Collection Memory Usage and Performance

PL/SQL multidimensional collections are modeled by creating a collection whose elements are also collections.

We found that the memory usage and performance of Multidimensional Collections are dependent on the total number of branch nodes, which are determined by the data characteristics and the subscript indices ordering.

This Blog will try to demonstrate the findings with appended Test Code.

1. Know Your Data

At first, run two queries to understand the test data (see the Test Code):

SQL> select round(num_rows*avg_row_len/1024/1024) mb from dba_tables where table_name = 'MEM_TAB';

46 MB

SQL> select * 
  from (
  select count(distinct id) id, count(distinct name) name
        ,count(distinct num_1) num_1, count(distinct num_2) num_2
        ,count(distinct txt_1) txt_1, count(distinct txt_2) txt_2
        ,count(distinct num_1||'.'||num_2) num_1_2, count(distinct txt_1||'.'||txt_2) txt_1_2
    from mem_tab)
unpivot (distinct_count for count_name in
        (id as 'count(distinct id)', name as 'count(distinct name)'
        ,num_1 as 'count(distinct num_1)', num_2 as 'count(distinct num_2)'
        ,txt_1 as 'count(distinct txt_1)', txt_2 as 'count(distinct txt_2)' 
        ,num_1_2 as 'count(distinct num_1.num_2)'
        ,txt_1_2 as 'count(distinct txt_1.txt_2)'
        ));

COUNT_NAME                  DISTINCT_COUNT
--------------------------- --------------
count(distinct id)               1,000,000
count(distinct name)             1,000,000
count(distinct num_1)              500,000
count(distinct num_2)               50,000
count(distinct txt_1)              500,000
count(distinct txt_2)               50,000
count(distinct num_1.num_2)      1,000,000
count(distinct txt_1.txt_2)      1,000,000

Test table mem_tab is composed of 6 columns, contains 1,000,000 rows, and theoretical size is about 46 MB.

• It has 2 unique columns (id and name), and two composite unique columns ((num_1.num_2), (txt_1.txt_2)).
• num_1 and txt_1 have 500,000 distinct values each, branching factor = 2, hence 500,000 branch nodes when storing as array(num_1)(num_2);
• num_2 and txt_2 have 50,000 distinct values each, branching factor = 20, hence 50,000 branch nodes when storing as array(num_2)(num_1).

2. Create Test

Run command:

SQL> exec test_collection_dimm_mem.run(1000000);

0  Dimension 1 array(id)  , pga_used(MB) =   411, Elapsed(Second)= 5.44
1  Dimension 1 array(name), pga_used(MB) =   459, Elapsed(Second)= 6.21
2  Dimension 2 array(num_1)(num_2), pga_used(MB) =  2227, Elapsed(Second)= 8.54
3  Dimension 2 array(num_2)(num_1), pga_used(MB) =   536, Elapsed(Second)= 5.44
4  Dimension 2 array(txt_1)(txt_2), pga_used(MB) =  2739, Elapsed(Second)= 7.79
5  Dimension 2 array(txt_2)(txt_1), pga_used(MB) =   628, Elapsed(Second)= 5.96
6  Dimension 2 array(num_1)(txt_2), pga_used(MB) =  2719, Elapsed(Second)= 6.53
7  Dimension 2 array(num_2)(txt_1), pga_used(MB) =   626, Elapsed(Second)= 5.62
8  Dimension 2 array(txt_1)(num_2), pga_used(MB) =  2251, Elapsed(Second)= 9.12
9  Dimension 2 array(txt_2)(num_1), pga_used(MB) =   539, Elapsed(Second)= 6.07
   
10 Dimension 1 table(id), pga_used(MB) =   411, Elapsed(Second)= 6.25
11 Dimension 2 table(num_1)(num_2), pga_used(MB) =   889, Elapsed(Second)= 9.78
12 Dimension 2 table(num_2)(num_1), pga_used(MB) =   443, Elapsed(Second)= 7.86  

The above output shows:

1. One dimensional associative array has a factor 10 more memory usage compared to disk table size (46MB).
2. Comparing RUN 4 and RUN 5, two dimensional can have a factor 60 more memory usage (2739/46) when organized with 500,000 branch nodes in array(txt_1)(txt_2); but factor 14 (628/46) when organized with 50,000 branch nodes in array(txt_2)(txt_1) by simply exchanging the subscripts;
   More than a factor 4 of difference when exchanging the order of subscripts.
   Each branch node takes about 5KB to 13KB.
   (imagine the cases of organizations with 50% vs. 5% employees being managers)
3. One dimensional nested table and associative array are comparable.
4. Two dimensional nested tables have a factor of 19 (888/46), and 10 (443/46).
5. The performance is proportional to the memory usage.

3. Lookup Test

Once the data is stored in array/table, they can be used for in-memory search.
For example, in Test Code: test_collection_dimm_mem.run, reactivate the lookup snippet code:

    ---- lookup example ----
    l_start_time := dbms_utility.get_time;
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_node_rec := l_array_d2nn(c.num_1)(c.num_2);
    end loop;

Test output below shows that the performance difference is less than 30% among associative arrays.
But nested table is a factor 3 to 5 faster.

0  Dimension 1 array(id)  , pga_used(MB) =   411, Elapsed(Second)= 2.27
1  Dimension 1 array(name), pga_used(MB) =   459, Elapsed(Second)= 2.88
2  Dimension 2 array(num_1)(num_2), pga_used(MB) =  2227, Elapsed(Second)= 2.85
3  Dimension 2 array(num_2)(num_1), pga_used(MB) =   536, Elapsed(Second)= 2.51
4  Dimension 2 array(txt_1)(txt_2), pga_used(MB) =  2739, Elapsed(Second)= 3.3
5  Dimension 2 array(txt_2)(txt_1), pga_used(MB) =   628, Elapsed(Second)= 2.95
6  Dimension 2 array(num_1)(txt_2), pga_used(MB) =  2719, Elapsed(Second)= 2.77
7  Dimension 2 array(num_2)(txt_1), pga_used(MB) =   626, Elapsed(Second)= 2.58
8  Dimension 2 array(txt_1)(num_2), pga_used(MB) =  2251, Elapsed(Second)= 3.4
9  Dimension 2 array(txt_2)(num_1), pga_used(MB) =   539, Elapsed(Second)= 2.93
   
10 Dimension 1 table(id), pga_used(MB) =   411, Elapsed(Second)= .45
11 Dimension 2 table(num_1)(num_2), pga_used(MB) =   889, Elapsed(Second)= 1.09
12 Dimension 2 table(num_2)(num_1), pga_used(MB) =   443, Elapsed(Second)= .8

4. Heapdump Dump

Make heapdump for 3 Cases:

1. Dimension 1 array(id) in RUN 0
2. Dimension 2 array(num_1)(num_2) in RUN 2
3. Dimension 2 array(num_2)(num_1) in RUN 3

by:

alter session set events 'immediate trace name heapdump level 16';

respective "top uga heap" displays the main memory usage:

--- Dimension 1 array(id) ---
 HEAP DUMP heap name="top uga heap"  desc=0x7f4b2d56ada0
 Total heap size    =427532128
 
--- Dimension 2 array(num_1)(num_2) ---
 HEAP DUMP heap name="top uga heap"  desc=0x7f94fddf3da0
 Total heap size    =2331900936

--- Dimension 2 array(num_2)(num_1) ---
 HEAP DUMP heap name="top uga heap"  desc=0x7fb10cf26da0
 Total heap size    =558883712  

5. PGA Dump

Further make pga_detail_dump by:

alter session set events 'immediate trace name pga_detail_dump level 27';

which reveals corresponding details by categories:
(note 27 is Oracle process number (PID), only top categories are listed)

--- Dimension 1 array(id) ---
  220672920 bytes,13515 chunks: "pmuccst: adt/record       "  PL/SQL
            koh-kghu sessi  ds=0x7f4b27f791b8  dsprt=0x7f4b28eed740
  182716608 bytes,11196 chunks: "pl/sql vc2                "  PL/SQL
            koh-kghu sessi  ds=0x7f4b27f791b8  dsprt=0x7f4b28eed740
   18667040 bytes,1144 chunks: "pmucalm coll              "  PL/SQL
            koh-kghu sessi  ds=0x7f4b27f791b8  dsprt=0x7f4b28eed740
     973992 bytes,  66 chunks: "static frame of inst      "  PL/SQL
            koh-kghu sessi  ds=0x7f4b282a8580  dsprt=0x7f4b28eed740
  
--- Dimension 2 array(num_1)(num_2) ---        
  1633408272 bytes,100038 chunks: "pmucalm coll              "  PL/SQL
            koh-kghu sessi  ds=0x7f94f88121b8  dsprt=0x7f94f977d740
  282131512 bytes,17279 chunks: "pmuccst: adt/record       "  PL/SQL
            koh-kghu sessi  ds=0x7f94f88121b8  dsprt=0x7f94f977d740
  187843616 bytes,11510 chunks: "pl/sql vc2                "  PL/SQL
            koh-kghu sessi  ds=0x7f94f88121b8  dsprt=0x7f94f977d740
  117545272 bytes,7199 chunks: "pmucpcon: ipm             "  PL/SQL
            koh-kghu sessi  ds=0x7f94f88121b8  dsprt=0x7f94f977d740
   57776680 bytes,3539 chunks: "pmucpcon: tds             "  PL/SQL
            koh-kghu sessi  ds=0x7f94f88121b8  dsprt=0x7f94f977d740
   41783352 bytes,2559 chunks: "pmucpcon: cds             "  PL/SQL
            koh-kghu sessi  ds=0x7f94f88121b8  dsprt=0x7f94f977d740          
  
--- Dimension 2 array(num_2)(num_1) ---
  243923992 bytes,14939 chunks: "pmuccst: adt/record       "  PL/SQL
            koh-kghu sessi  ds=0x7fb1079451b8  dsprt=0x7fb1088ad740
  160771776 bytes,9852 chunks: "pl/sql vc2                "  PL/SQL
            koh-kghu sessi  ds=0x7fb1079451b8  dsprt=0x7fb1088ad740
  127166584 bytes,7789 chunks: "pmucalm coll              "  PL/SQL
            koh-kghu sessi  ds=0x7fb1079451b8  dsprt=0x7fb1088ad740
   10956088 bytes, 671 chunks: "pmucpcon: ipm             "  PL/SQL
            koh-kghu sessi  ds=0x7fb1079451b8  dsprt=0x7fb1088ad740
    5747456 bytes, 352 chunks: "pmucpcon: cds             "  PL/SQL
            koh-kghu sessi  ds=0x7fb1079451b8  dsprt=0x7fb1088ad740
    4416776 bytes, 271 chunks: "pmucpcon: tds             "  PL/SQL
            koh-kghu sessi  ds=0x7fb1079451b8  dsprt=0x7fb1088ad740   

In the above 3 Cases, "pmuccst: adt/record" and "pl/sql vc2" are almost similar, but "pmucalm coll" makes the difference.

1. "pl/sql vc2" are all involved varchar2 strings.
2. "pmuccst: adt/record" (ADT: Abstract Data Type) stores all the PL/SQL records, in this test, it is 1,000,000.
3. "pmucalm coll" looks like all the allocated collections, which represent the branch nodes. array(num_1)(num_2) takes 1,633,408,272 Bytes, whereas array(num_2)(num_1) 127,166,584 since the first one has 500,000 branch nodes, whereas second 50,000.

In Oracle applications, PL/SQL collections are often the cause of ORA-04030 when storing large number of elements (same type). Determining the categories helps pinpoint the main memory consumption.

• "pmuccst: adt/record" stands for leaf nodes, that is the number of elements.
• "pmucalm coll" represents branch nodes, that is the number of collections.

BTW, the abbreviation ds and dsprt are the shortcuts of "heap_descriptor" and "parent_heap_descriptor".

6. Populate process_memory_detail

Dump process_memory_detail into process_memory_detail_v by setting event PGA_DETAIL_GET, then query the result with:

column run_name format A40
column "NAME(MB, ALLOC_COUNT) List" format A200
column name format A30

select run, run_name, pid, round(sum(bytes)/1024/1024) mb, sum(allocation_count) allocation_count
from process_memory_detail_v 
group by run, run_name, pid 
order by run;

 RUN RUN_NAME                          PID      MB ALLOCATION_COUNT
---- -------------------------------- ---- ------- ----------------
   0 Dimension 1 array(id)              27     409           29,676
   1 Dimension 1 array(name)            27     458           32,824
   2 Dimension 2 array(num_1)(num_2)    27   2,220          145,969
   3 Dimension 2 array(num_2)(num_1)    27     534           37,721
   4 Dimension 2 array(txt_1)(txt_2)    27   2,730          178,767
   5 Dimension 2 array(txt_2)(txt_1)    27     626           43,632
   6 Dimension 2 array(num_1)(txt_2)    27   2,711          177,517
   7 Dimension 2 array(num_2)(txt_1)    27     624           43,511
   8 Dimension 2 array(txt_1)(num_2)    27   2,244          147,554
   9 Dimension 2 array(txt_2)(num_1)    27     537           37,895
  10 Dimension 1 table(id)              27     411           29,745
  11 Dimension 2 table(num_1)(num_2)    27     886           60,281
  12 Dimension 2 table(num_2)(num_1)    27     443           31,829
-------------------------------------------------------------------  

select run, run_name, pid, round(sum(bytes)/1024/1024) mb,
       (select listagg(rpad(name||'('||round(bytes/1024/1024)||', '||allocation_count||')', 40, chr(32)), '; ') 
               within group (order by bytes desc) agg
          from (select * from process_memory_detail_v c 
                 where c.run = v.run and c.pid = v.pid and bytes > 0
                 order by bytes desc) c 
         where rownum <= 3
         ) "NAME(MB, ALLOC_COUNT) List"
from process_memory_detail_v v
group by run, run_name, pid
order by run, run_name, pid;

RUN RUN_NAME                         PID     MB NAME(MB, ALLOC_COUNT) List
--- ------------------------------- ---- ------ ---------------------------------------------------------------------------------------------------
  0 Dimension 1 array(id)             27    409 pmuccst: adt/record(225, 14456) ; pl/sql vc2(161, 10311)                  ; pmucalm coll(17, 1084)
  1 Dimension 1 array(name)           27    458 pl/sql vc2(196, 12561)          ; pmuccst: adt/record(190, 12220)         ; pmucalm coll(65, 4196)
  2 Dimension 2 array(num_1)(num_2)   27  2,220 pmucalm coll(1560, 100167)      ; pmuccst: adt/record(271, 17379)         ; pl/sql vc2(179, 11488)
  3 Dimension 2 array(num_2)(num_1)   27    534 pmuccst: adt/record(231, 14827) ; pl/sql vc2(153, 9836)                   ; pmucalm coll(123, 7902)
  4 Dimension 2 array(txt_1)(txt_2)   27  2,730 pmucalm coll(2699, 173350)      ; pmuccst: adt/record(14, 882)            ; pl/sql vc2(10, 675)
  5 Dimension 2 array(txt_2)(txt_1)   27    626 pmucalm coll(229, 14676)        ; pmuccst: adt/record(207, 13302)         ; pl/sql vc2(176, 11272)
  6 Dimension 2 array(num_1)(txt_2)   27  2,711 pmucalm coll(2596, 166684)      ; pmuccst: adt/record(62, 3975)           ; pl/sql vc2(47, 2993)
  7 Dimension 2 array(num_2)(txt_1)   27    624 pmucalm coll(222, 14238)        ; pmuccst: adt/record(221, 14194)         ; pl/sql vc2(167, 10739)
  8 Dimension 2 array(txt_1)(num_2)   27  2,244 pmucalm coll(1570, 100838)      ; pmuccst: adt/record(273, 17546)         ; pl/sql vc2(185, 11904)
  9 Dimension 2 array(txt_2)(num_1)   27    537 pmuccst: adt/record(232, 14931) ; pl/sql vc2(155, 9946)                   ; pmucalm coll(123, 7870)
 10 Dimension 1 table(id)             27    411 pmuccst: adt/record(222, 14276) ; pl/sql vc2(163, 10488)                  ; pmucalm coll(17, 1089)
 11 Dimension 2 table(num_1)(num_2)   27    886 pmucalm coll(286, 18381)        ; pmuccst: adt/record(248, 15936)         ; pl/sql vc2(144, 9229)
 12 Dimension 2 table(num_2)(num_1)   27    443 pmuccst: adt/record(229, 14680) ; pl/sql vc2(155, 9951)                   ; pmucalm coll(30, 1905)
----------------------------------------------------------------------------------------------------------

select run, run_name, pid, category, name, heap_name, round(bytes/1024/1024) mb
      ,allocation_count, round(bytes/allocation_count) bytes_per_cnt
from process_memory_detail_v 
where name = 'pmucalm coll'
order by pid, category, name, heap_name, run, timestamp;

RUN RUN_NAME                         PID  CATEGORY  NAME           HEAP_NAME        MB      ALLOCATION_COUNT   BYTES_PER_CNT
--- ------------------------------- ---- ---------- ------------- ---------------- ------  -----------------  --------------
  0 Dimension 1 array(id)             27  PL/SQL    pmucalm coll   koh-kghu sessi      17              1,084          16,317
  1 Dimension 1 array(name)           27  PL/SQL    pmucalm coll   koh-kghu sessi      65              4,196          16,325
  2 Dimension 2 array(num_1)(num_2)   27  PL/SQL    pmucalm coll   koh-kghu sessi   1,560            100,167          16,328
  3 Dimension 2 array(num_2)(num_1)   27  PL/SQL    pmucalm coll   koh-kghu sessi     123              7,902          16,326
  4 Dimension 2 array(txt_1)(txt_2)   27  PL/SQL    pmucalm coll   koh-kghu sessi   2,699            173,350          16,328
  5 Dimension 2 array(txt_2)(txt_1)   27  PL/SQL    pmucalm coll   koh-kghu sessi     229             14,676          16,327
  6 Dimension 2 array(num_1)(txt_2)   27  PL/SQL    pmucalm coll   koh-kghu sessi   2,596            166,684          16,328
  7 Dimension 2 array(num_2)(txt_1)   27  PL/SQL    pmucalm coll   koh-kghu sessi     222             14,238          16,327
  8 Dimension 2 array(txt_1)(num_2)   27  PL/SQL    pmucalm coll   koh-kghu sessi   1,570            100,838          16,328
  9 Dimension 2 array(txt_2)(num_1)   27  PL/SQL    pmucalm coll   koh-kghu sessi     123              7,870          16,326
 10 Dimension 1 table(id)             27  PL/SQL    pmucalm coll   koh-kghu sessi      17              1,089          16,317
 11 Dimension 2 table(num_1)(num_2)   27  PL/SQL    pmucalm coll   koh-kghu sessi     286             18,381          16,327
 12 Dimension 2 table(num_2)(num_1)   27  PL/SQL    pmucalm coll   koh-kghu sessi      30              1,905          16,322

• The first query shows the memory usage per run.
• The second query lists the memory allocations of top 3 Heaps for each run.
• The third query reveals Heap "pmucalm coll" statistics of memory and allocation_count.
  The top 4 RUNs (2, 4, 6, 8) have 500,000 branch nodes, and hence more memory and allocation_count.
  Computing the average bytes per allocation, BYTES_PER_CNT shows almost a constant value of 16K.
  Probably generous reserving 16K (2x8K-Blocks) for each "pmucalm coll" conforms to the design strategy of preventing expensive branch node splitting.
  

7. Test Code

set numformat 999,999,999

drop table mem_tab;

create table mem_tab(id number, name varchar2(10) 
                    ,num_1 number, num_2 number 
                    ,txt_1 varchar2(10), txt_2 varchar2(10));

insert into mem_tab 
select level id, rpad(level, 10, 'X') name
   ,level num_1, ceil(level/20) num_2 
   ,rpad(level, 10, 'X') txt_1, rpad(ceil(level/20), 10, 'X') txt_2 
  from dual connect by level <= 500000
union all
select (level + 500000) id, rpad((level + 500000), 10, 'X') name
   ,level num_1, -ceil(level/20) num_2 
   ,rpad(level, 10, 'X') txt_1, rpad(-ceil(level/20), 10, 'X') txt_2 
  from dual connect by level <= 500000;

commit;

exec dbms_stats.gather_table_stats(null, 'MEM_TAB');

select round(num_rows*avg_row_len/1024/1024) mb from dba_tables where table_name = 'MEM_TAB';
-- 46 MB

select * 
  from (
  select count(distinct id) id, count(distinct name) name
        ,count(distinct num_1) num_1, count(distinct num_2) num_2
        ,count(distinct txt_1) txt_1, count(distinct txt_2) txt_2
        ,count(distinct num_1||'.'||num_2) num_1_2, count(distinct txt_1||'.'||txt_2) txt_1_2
    from mem_tab)
unpivot (distinct_count for count_name in
        (id as 'count(distinct id)', name as 'count(distinct name)'
        ,num_1 as 'count(distinct num_1)', num_2 as 'count(distinct num_2)'
        ,txt_1 as 'count(distinct txt_1)', txt_2 as 'count(distinct txt_2)' 
        ,num_1_2 as 'count(distinct num_1.num_2)'
        ,txt_1_2 as 'count(distinct txt_1.txt_2)'
        ));

create or replace package test_collection_dimm_mem as
  procedure run (p_cnt number := 1000000);
end;
/

drop table process_memory_detail_v;

create table process_memory_detail_v as 
  select 123 run, rpad('A', 40, 'X') run_name, timestamp'1998-02-17 11:22:00' timestamp
        ,234 session_id, 345 session_serial#, v.* 
  from v$process_memory_detail v where 1=2;

create or replace package body test_collection_dimm_mem as
  type t_rec is record (
    id            number
   ,name          varchar2(10)
   ,num_1         number
   ,num_2         number
   ,txt_1         varchar2(10)
   ,txt_2         varchar2(10)
  );
  type t_array_d1n  is table of t_rec      index by pls_integer;    -- array(number)
  type t_array_d1t  is table of t_rec      index by varchar2(30);    -- array(text)
  
  type t_array_d2nn is table of t_array_d1n index by pls_integer;    -- array(number)(number)
  type t_array_d2tt is table of t_array_d1t index by varchar2(30);   -- array(text)(text)
  
  type t_array_d2nt is table of t_array_d1t index by pls_integer;    -- array(number)(text)
  type t_array_d2tn is table of t_array_d1n index by varchar2(30);   -- array(text)(number)
  
  type t_nesttab_d1 is table of t_rec;
  type t_nesttab_d2 is table of t_nesttab_d1;
     
  l_node_rec       t_rec;
  l_array_d1n      t_array_d1n;                        -- array(number)
  l_array_d1t      t_array_d1t;                        -- array(text)
  l_array_d2nn     t_array_d2nn;                        -- array(number)(number)
  l_array_d2tt     t_array_d2tt;                        -- array(text)(text)
  l_array_d2nt     t_array_d2nt;                        -- array(number)(text)
  l_array_d2tn     t_array_d2tn;                        -- array(text)(number)
  l_nesttab_d1     t_nesttab_d1 := new t_nesttab_d1(); -- nested Table (number)
  l_nesttab_d2     t_nesttab_d2 := new t_nesttab_d2(); -- nested Table (number)(number)
  l_node_map_sep   varchar2(1) := '.';
  l_start_time     number;
  l_mb             varchar2(10);
  l_last_num       number;
  l_pid            number;
  l_sid            number;
  l_serial#        number;
  l_run            number := 0;
  l_pga_status     varchar2(10) := 'NOT';
  
  procedure reset as
  begin
    l_array_d1n.delete;
    l_array_d1t.delete;
    l_array_d2nn.delete;
    l_array_d2tt.delete;
    l_array_d2nt.delete;
    l_array_d2tn.delete;
    l_nesttab_d1.delete;
    l_nesttab_d2.delete;
    dbms_session.free_unused_user_memory;
    l_start_time := dbms_utility.get_time;
  end;

  procedure prt_and_reset_mem(p_name varchar2) as
  begin
    select lpad(round(pga_used_mem/1024/1024), 5, ' '), pid, s.sid, s.serial#
      into l_mb, l_pid, l_sid, l_serial#
      from v$process p, v$session s
     where p.addr = s.paddr and s.sid = sys.dbms_support.mysid and rownum = 1;
    dbms_output.put_line(rpad(l_run, 3, ' ')||p_name||', pga_used(MB) = '||l_mb||
             ', Elapsed(Second)= '||round((dbms_utility.get_time - l_start_time)/100, 2));
             
    execute immediate q'[alter session set events 'immediate trace name PGA_DETAIL_GET level ]'||l_pid||q'[']'; 
    -- wait status = COMPLETE when status = SCANNING
    while (true) loop
     select status into l_pga_status from v$process_memory_detail_prog where pid = l_pid;
     exit when l_pga_status = 'COMPLETE';
     dbms_lock.sleep(0.1);
    end loop;
    delete from process_memory_detail_v 
     where pid = l_pid and session_id = l_sid and session_serial# = l_serial# and run = l_run;
    insert into process_memory_detail_v select l_run, p_name, systimestamp, l_sid, l_serial#, v.* 
      from v$process_memory_detail v where pid = l_pid;
    -- outcomment to keep last PGA_DETAIL
    -- execute immediate q'[alter session set events 'immediate trace name PGA_DETAIL_CANCEL level ]'||l_pid||q'[']';  
    commit; 
    
    l_run := l_run + 1;
    reset;
  end;
  
  function rec(id number, name varchar2, num_1 number, num_2 number, txt_1 varchar2, txt_2 varchar2) 
    return t_rec as
  begin
    l_node_rec.id        := id;
    l_node_rec.name      := name;
    l_node_rec.num_1     := num_1;
    l_node_rec.num_2     := num_2;
    l_node_rec.txt_1     := txt_1;
    l_node_rec.txt_2     := txt_2;
    return l_node_rec;
  end;
    
  procedure run (p_cnt number := 1000000) as
  begin
    reset;
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d1n(c.id) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 1 array(id)  ');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d1t(c.name) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 1 array(name)');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d2nn(c.num_1)(c.num_2) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;

    ---- lookup example ----
    --l_start_time := dbms_utility.get_time;
    --for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
    --  l_node_rec := l_array_d2nn(c.num_1)(c.num_2);
    --end loop;

    prt_and_reset_mem('Dimension 2 array(num_1)(num_2)');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d2nn(c.num_2)(c.num_1) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 2 array(num_2)(num_1)');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d2tt(c.txt_1)(c.txt_2) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 2 array(txt_1)(txt_2)');   
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d2tt(c.txt_2)(c.txt_1) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 2 array(txt_2)(txt_1)');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d2nt(c.num_1)(c.txt_2) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 2 array(num_1)(txt_2)');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d2nt(c.num_2)(c.txt_1) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 2 array(num_2)(txt_1)');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d2tn(c.txt_1)(c.num_2) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 2 array(txt_1)(num_2)');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
      l_array_d2tn(c.txt_2)(c.num_1) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 2 array(txt_2)(num_1)');
    
    --- nested table. Note: specify p_cnt = 1000000 to select all rows for test ---
    dbms_output.put_line('');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab 
               where rownum <= p_cnt order by id) loop
     l_nesttab_d1.extend;
     l_nesttab_d1(l_nesttab_d1.last) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
    end loop;
    prt_and_reset_mem('Dimension 1 table(id)');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab 
               where rownum <= p_cnt order by num_1, num_2) loop
     if (c.num_1 != l_last_num) then
      l_nesttab_d2.extend;
      l_nesttab_d2(l_nesttab_d2.last) := l_nesttab_d1;
      l_nesttab_d1 := new t_nesttab_d1();
     end if;
     l_nesttab_d1.extend;
     l_nesttab_d1(l_nesttab_d1.last) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
     l_last_num := c.num_1;
    end loop;
    l_nesttab_d2.extend;
    l_nesttab_d2(l_nesttab_d2.last) := l_nesttab_d1;

    ---- lookup example ----
    --l_start_time := dbms_utility.get_time;
    --for i in 1..l_nesttab_d2.count loop
    -- l_nesttab_d1 := l_nesttab_d2(i);
    -- for j in 1..l_nesttab_d1.count loop
    --   l_node_rec := l_nesttab_d1(j);
    -- end loop;
    --end loop;

    prt_and_reset_mem('Dimension 2 table(num_1)(num_2)');
    
    for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab 
               where rownum <= p_cnt order by num_2, num_1) loop
     if (c.num_2 != l_last_num) then
      l_nesttab_d2.extend;
      l_nesttab_d2(l_nesttab_d2.last) := l_nesttab_d1;
      l_nesttab_d1 := new t_nesttab_d1();
     end if;
     l_nesttab_d1.extend;
     l_nesttab_d1(l_nesttab_d1.last) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
     l_last_num := c.num_2;
    end loop;
    l_nesttab_d2.extend;
    l_nesttab_d2(l_nesttab_d2.last) := l_nesttab_d1;
    prt_and_reset_mem('Dimension 2 table(num_2)(num_1)');    
  end;     
end;
/

-- set serveroutput on
-- exec test_collection_dimm_mem.run(1000000);

dbms_session.get_package_memory_utilization and limitations

Oracle 11g Release 2 extends dbms_session Package by introducing a new Procedure:
    get_package_memory_utilization
and 11.2.0.4.0 further enhanced with a second overloaded procedure to augment the measure from 2**31-1 up to 10**38.

It describes memory usage of PL/SQL package declare variables in either specification or body. but not memory usage of locally declared variables within functions, procedures, or anonymous blocks.

Oracle Documentation said:
    These procedures describe static package memory usage.
    The output collections describe memory usage in each instantiated package.

Probably "static package memory usage" stands for memory usage of package declared variables.

The second restriction is that it is bound to caller's session, and does not disclose heap allocation details.

This Blog till try to demonstrate such limitations.

See appended Test Code 1, 2 and its used dump_mem_usage in the Reference 3.

1. Test

SQL > exec pkg_mem_test.run_1(1000*1000);

-------- 1. PKG_MEM_TEST.run_1 START --------
v$process: (SID=854)Used/Alloc/Freeable/Max=6/8/1/11
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/7,PL/SQL/3/3/3,Other/3//3,Freeable/1/0/,
[Owner; Unit; Type; Used; Free; sum_abs_mb(1)]
S; PKG_MEM_TEST; 9  (package); 0; 0; 0
S; PKG_MEM_TEST; 11 (package body); 0; 0; 0

-------- 2. PKG_MEM_TEST.run_1 spec_rec_tab ALLOCATED --------
v$process: (SID=854)Used/Alloc/Freeable/Max=1282/1283/0/1283
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/47,PL/SQL/1215/1211/1215,Other/68//68,
[Owner; Unit; Type; Used; Free; sum_abs_mb(1207)]
S; PKG_MEM_TEST; 9  (package); 1177; 28; 1206
S; PKG_MEM_TEST; 11 (package body); 0; 0; 0

-------- 3. PKG_MEM_TEST.run_1 body_rec_tab ALLOCATED --------
v$process: (SID=854)Used/Alloc/Freeable/Max=2496/2497/0/2497
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/47,PL/SQL/2426/2420/2426,Other/71//71,
[Owner; Unit; Type; Used; Free; sum_abs_mb(2412)]
S; PKG_MEM_TEST; 11 (package body); 1177; 28; 1206
S; PKG_MEM_TEST; 9  (package); 1177; 28; 1206

-------- 4. PKG_MEM_TEST.run_1 local_rec_tab ALLOCATED --------
v$process: (SID=854)Used/Alloc/Freeable/Max=3710/3712/0/3712
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/47,PL/SQL/3638/3629/3638,Other/73//73,
[Owner; Unit; Type; Used; Free; sum_abs_mb(2412)]
S; PKG_MEM_TEST; 11 (package body); 1177; 28; 1206
S; PKG_MEM_TEST; 9  (package); 1177; 28; 1206

-------- 5. PROC_MEM_TEST START --------
v$process: (SID=854)Used/Alloc/Freeable/Max=3710/3712/0/3712
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/47,PL/SQL/3638/3629/3638,Other/73//73,
[Owner; Unit; Type; Used; Free; sum_abs_mb(2412)]
S; PKG_MEM_TEST; 11 (package body); 1177; 28; 1206
S; PKG_MEM_TEST; 9  (package); 1177; 28; 1206

-------- 6. PROC_MEM_TEST local_rec_tab ALLOCATED --------
v$process: (SID=854)Used/Alloc/Freeable/Max=4924/4926/0/4926
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/47,PL/SQL/4850/4838/4850,Other/76//76,
[Owner; Unit; Type; Used; Free; sum_abs_mb(2412)]
S; PKG_MEM_TEST; 11 (package body); 1177; 28; 1206
S; PKG_MEM_TEST; 9  (package); 1177; 28; 1206

-------- 7. PKG_MEM_TEST.run_2 START --------
v$process: (SID=854)Used/Alloc/Freeable/Max=4924/4926/0/4926
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/47,PL/SQL/4850/4838/4850,Other/76//76,
[Owner; Unit; Type; Used; Free; sum_abs_mb(2412)]
S; PKG_MEM_TEST; 11 (package body); 1177; 28; 1206
S; PKG_MEM_TEST; 9  (package); 1177; 28; 1206

-------- 8. PKG_MEM_TEST.run_2 local_rec_tab ALLOCATED --------
v$process: (SID=854)Used/Alloc/Freeable/Max=6138/6141/0/6141
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/47,PL/SQL/6062/6047/6062,Other/78//78,
[Owner; Unit; Type; Used; Free; sum_abs_mb(2412)]
S; PKG_MEM_TEST; 11 (package body); 1177; 28; 1206
S; PKG_MEM_TEST; 9  (package); 1177; 28; 1206

-------- 9. PROC_MEM_TEST END --------
v$process: (SID=854)Used/Alloc/Freeable/Max=4924/6141/0/6141
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/47,PL/SQL/4850/4838/6062,Other/1291//1291,
[Owner; Unit; Type; Used; Free; sum_abs_mb(2412)]
S; PKG_MEM_TEST; 11 (package body); 1177; 28; 1206
S; PKG_MEM_TEST; 9  (package); 1177; 28; 1206

-------- 10. PKG_MEM_TEST.run_1 END --------
v$process: (SID=854)Used/Alloc/Freeable/Max=3710/6141/0/6141
v$process_memory: Category/Alloc/Used/Max=SQL/0/0/47,PL/SQL/3638/3629/6062,Other/2503//2503,
[Owner; Unit; Type; Used; Free; sum_abs_mb(2412)]
S; PKG_MEM_TEST; 11 (package body); 1177; 28; 1206
S; PKG_MEM_TEST; 9  (package); 1177; 28; 1206

In Step 8, the total used memory is;
     6138 MB
but only 2412 from
    PKG_MEM_TEST; 11 (package body); 1177
  PKG_MEM_TEST; 9  (package); 1177
are listed.

The 3 local allocations in Steps:
  4. PKG_MEM_TEST.run_1 local_rec_tab

  6. PROC_MEM_TEST local_rec_tab

  8. PKG_MEM_TEST.run_2 local_rec_tab

are not reported.

2. Populate process_memory_detail

dbms_session.get_package_memory_utilization is restricted to report memory usage of caller's session, aggregated by each package (spec and body) name, which is a point of view in applications, and does not point out the details of allocated heap details.

V$PROCESS_MEMORY_DETAIL (Oracle 10.2 introduced) lists PGA memory usage by category, heap_name, and component name for each Oracle process, and summarized in V$PROCESS_MEMORY by category. They expose memory usage in the dimension of heap components, a point of view in program code.

Test Code 2 provides a V$PROCESS_MEMORY_DETAIL sampling tool to collect and monitor memory changes.

Run:

exec pga_sampling(p_sid => 234);

Query memory usage per sampling and display memory deviation by:

select v.*, mb - lag(mb) over(order by run) mb_delta 
  from(
    select run, timestamp, session_id, session_serial#, pid
          ,round(sum(bytes)/1024/1024) mb
          ,sum(allocation_count) allocation_count
    from process_memory_detail_v 
    group by run, timestamp, session_id, session_serial#, pid) v
order by run;     

List top 3 heap memory usages for each sampling by:

select run, timestamp, session_id, pid, round(sum(bytes)/1024/1024) mb,
       (select listagg(rpad(name||'('||round(bytes/1024/1024)||', '||allocation_count||')', 40, chr(32)), '; ') 
               within group (order by bytes desc) agg
          from (select * from process_memory_detail_v c 
                 where c.run = v.run and c.pid = v.pid and bytes > 0
                 order by bytes desc) c 
         where rownum <= 3
         ) "NAME(MB, ALLOC_COUNT) List"
from process_memory_detail_v v
group by run, timestamp, session_id, pid
order by run, timestamp, session_id, pid;

Join above query with v$active_session_history (or dba_hist_active_sess_history), one can approximately locate the memory usage related to PL/SQL program unit. Starting from this PL/SQL program unit, one can use 12c UTL_CALL_STACK (or BMS_UTILITY.FORMAT_CALL_STACK) to backtrace all call_stack involved units.

with sq as (
  select run, timestamp, v.session_id, session_serial#, pid, round(sum(bytes)/1024/1024) mb
        ,(select listagg(rpad(name||'('||round(bytes/1024/1024)||', '||allocation_count||')', 40, chr(32)), '; ') 
                 within group (order by bytes desc) agg
            from (select * from process_memory_detail_v c 
                   where c.run = v.run and c.pid = v.pid and bytes > 0
                   order by bytes desc) c 
           where rownum <= 3
           ) "NAME(MB, ALLOC_COUNT) List"
  from process_memory_detail_v v
  group by run, timestamp, v.session_id, session_serial#, pid)
select p.*
      ,(select owner||'.'||object_name||
               case when procedure_name is not null then '.' || procedure_name end
         from dba_procedures
        where object_id = s.plsql_entry_object_id and subprogram_id = s.plsql_entry_subprogram_id)
       plsql_entry
      ,s.sample_time, round(s.pga_allocated/1024/1024) pga_allocated_mb
  from sq p, v$active_session_history s
where p.session_id = s.session_id
  and p.session_serial# = s.session_serial#
  and p.timestamp between s.sample_time and s.sample_time + interval'1' second
 order by run, timestamp, p.session_id, p.session_serial#, pid, s.sample_time;

3. PGA memory internals

Look output of the first query in above Section, pick one RUN with some peak memory usage, for example, 4, Run query:

select run, category, name, heap_name, depth, path
      ,round(sum(bytes/1024)) kb, sum(allocation_count) alloc_count
      ,heap_descriptor, parent_heap_descriptor, cycle
from (
  select v.*, (level-1) depth
        ,sys_connect_by_path('('||category||' , '||name||' , '||heap_name||')', ' -> ') path
        ,connect_by_iscycle as cycle
  from process_memory_detail_v v
  where lower(name) like '%recursive addr reg file%'
  start with parent_heap_descriptor = '00' and run = 4
  connect by nocycle prior heap_descriptor = parent_heap_descriptor and prior run = run
) 
--where lower(name) like '%recursive addr reg file%'
group by run, category, name, heap_name, heap_descriptor, parent_heap_descriptor, depth, path, cycle
--having sum(bytes/1024) > 1024
order by run, category, name, heap_name, depth, kb;

• It depicts the PGA heap tree structure by connecting heap_descriptor with parent_heap_descriptor.
• We can observe that only "Other" Category has depth 0 (root) nodes, and all other Categories are subtrees to "Other".
  (One exception is when CATEGORY = 'PL/SQL' and NAME='miscellaneous', both heap_descriptor and parent_heap_descriptor equal to '00', causing cycle)
• One time we noticed certain high PGA memory consumptions. By running above query, it turns out that the main contribution is due to "recursive addr reg file". Further searching Oracle MOS, it is documented as something related to PLSQL anonymous blocks.
  (MOS Bug 9478199 - Memory corruption / ORA-600 from PLSQL anonymous blocks (Doc ID 9478199.8)).
• dbms_session.get_package_memory_utilization is hard to inject into existing code, let alone Oracle background processes. However, populating process_memory_detail opens a tiny door to peer Oracle internals, even for background processes, for example, PMON, DBWx, CJQ0, MMON.

4. Test Code 1

create or replace package pkg_mem_test is
  type t_rec        is record (id number, text varchar2(1000));
  type t_rec_tab    is table of t_rec index by pls_integer;
  spec_rec_tab      t_rec_tab;
  
  procedure run_1(p_cnt number);
  procedure run_2(p_cnt number);
end;
/

create or replace procedure proc_mem_test(p_cnt number) as
  type t_rec      is record (id number, text varchar2(1000));
  type t_rec_tab  is table of t_rec index by pls_integer;
  local_rec_tab   t_rec_tab;
begin
  dump_mem_usage.run('PROC_MEM_TEST START');

  select level id, rpad('ABC', 1000, 'X') text 
  bulk collect into local_rec_tab
  from dual connect by level <= p_cnt;
  
  dump_mem_usage.run('PROC_MEM_TEST local_rec_tab ALLOCATED');
  
  pkg_mem_test.run_2(p_cnt);
  
  dump_mem_usage.run('PROC_MEM_TEST END');
end;
/

create or replace package body pkg_mem_test is
  body_rec_tab      t_rec_tab;
  
  procedure run_1(p_cnt number) as
    local_rec_tab   t_rec_tab;
  begin
    dump_mem_usage.run('PKG_MEM_TEST.run_1 START');
  
    select level id, rpad('ABC', 1000, 'X') text 
    bulk collect into spec_rec_tab
    from dual connect by level <= p_cnt;
  
    dump_mem_usage.run('PKG_MEM_TEST.run_1 spec_rec_tab ALLOCATED');
    
    select level id, rpad('ABC', 1000, 'X') text 
    bulk collect into body_rec_tab
    from dual connect by level <= p_cnt;
  
    dump_mem_usage.run('PKG_MEM_TEST.run_1 body_rec_tab ALLOCATED');
    
    select level id, rpad('ABC', 1000, 'X') text 
    bulk collect into local_rec_tab
    from dual connect by level <= p_cnt;
  
    dump_mem_usage.run('PKG_MEM_TEST.run_1 local_rec_tab ALLOCATED');
    
    proc_mem_test(p_cnt);
    
    dump_mem_usage.run('PKG_MEM_TEST.run_1 END');
  end;
  
  procedure run_2(p_cnt number) as
    local_rec_tab    t_rec_tab;
  begin
    dump_mem_usage.run('PKG_MEM_TEST.run_2 START');
  
    select level id, rpad('ABC', 1000, 'X') text 
    bulk collect into local_rec_tab
    from dual connect by level <= p_cnt;
  
    dump_mem_usage.run('PKG_MEM_TEST.run_2 local_rec_tab ALLOCATED');  
    end;
end;
/

-- exec pkg_mem_test.run_1(1000*1000);

5. Test Code 2

drop table process_memory_detail_v;

create table process_memory_detail_v as 
  select 123 run, rpad('A', 40, 'X') run_name, timestamp'1998-02-17 11:22:00' timestamp
        ,234 session_id, 345 session_serial#, v.* 
  from v$process_memory_detail v where 1=2;
  
-- sampling for p_dur seconds in p_interval seconds interval.
create or replace procedure pga_sampling(p_sid number, p_dur number := 120, p_interval number := 1) as
  l_start_time     number := dbms_utility.get_time;
  l_sample_time    number;
  l_sleep_time     number;
  l_pid            number;
  l_sid            number;
  l_serial#        number;
  l_run            number := 0;
  l_pga_status     varchar2(10) := 'NOT';
  l_run_name       varchar2(40) := 'PGA Sampling('||p_sid||', '||p_dur||', '||p_interval||')';
begin
  select pid, s.sid, s.serial#
    into l_pid, l_sid, l_serial#
    from v$process p, v$session s
   where p.addr = s.paddr and s.sid = p_sid and rownum = 1;  
               
  l_sample_time := dbms_utility.get_time;
  while ((l_sample_time - l_start_time)/100 < p_dur) loop 
    execute immediate q'[alter session set events 'immediate trace name PGA_DETAIL_GET level ]'||l_pid||q'[']'; 
    -- wait status = COMPLETE when status = SCANNING, or ENABLED but elapsed time is over duration
    while (true) loop
     select status into l_pga_status from v$process_memory_detail_prog where pid = l_pid;
     exit when l_pga_status = 'COMPLETE' or ((dbms_utility.get_time - l_start_time)/100 > p_dur);
     dbms_lock.sleep(0.1);
    end loop;
    delete from process_memory_detail_v 
     where pid = l_pid and session_id = l_sid and session_serial# = l_serial# and run = l_run;
    insert into process_memory_detail_v select l_run, l_run_name, systimestamp, l_sid, l_serial#, v.* 
      from v$process_memory_detail v where pid = l_pid;
    -- outcomment to keep last PGA_DETAIL
    -- execute immediate q'[alter session set events 'immediate trace name PGA_DETAIL_CANCEL level ]'||l_pid||q'[']';  
    commit; 
    l_run := l_run + 1;    
    l_sleep_time := p_interval - (dbms_utility.get_time - l_sample_time)/100;
    if l_sleep_time > 0 then
     dbms_lock.sleep(l_sleep_time);
    end if;
    l_sample_time := dbms_utility.get_time;
  end loop;
end;
/
    
-- exec pga_sampling(p_sid => 234);

References

1. Oracle MOS Notes: How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)
2. Oracle Memory Troubleshooting, Part 4: Drilling down into PGA memory usage with V$PROCESS_MEMORY_DETAIL 
3. dbms_session package_memory_utilization