Oracle PL/SQL Collection Memory Reclaim

Oracle PL/SQL collection includes associative array, nested table and varray array, which are often used to store large amount of data. Consequently certain garbage collectors have to be developed in such applications to manually reclaim no longer used memory in order to reduce PGA memory consumption and eventually avoid:

    ORA-04030: out of process memory

In this Blog, we will demonstrate different approaches and their effects.

Test Code:

create or replace package mem_reclaim_test as
  procedure rpt(l_name varchar);
  procedure nullify;
  procedure empty;
  procedure empty_no_init;
  procedure empty_aa;
  procedure empty_aa_no_init;
  procedure delete;
  procedure copy;
  procedure copy_and_touch;
  procedure app_dealloc;
  procedure app_sparse;
end mem_reclaim_test;
/

create or replace package body mem_reclaim_test as
  type t_tab_type is table of char(1000);
  p_tab              t_tab_type := t_tab_type();  -- nested table
  p_tab_empty        t_tab_type := t_tab_type();
  p_sid              number     := sys.dbms_support.mysid;
 
  type t_tab_aa is table of char(1000) index by pls_integer;
  p_tab_aa           t_tab_aa;          -- associative array

  -------------------------------------------
 procedure rpt(l_name varchar) as
   l_v$process_mem            varchar2(4000);
    l_v$process_memory_mem     varchar2(4000);
 begin
  select 'Used/Alloc/Freeable/Max >>> '||
          round(pga_used_mem/1024/1024)    ||'/'||round(pga_alloc_mem/1024/1024)||'/'||
            round(pga_freeable_mem/1024/1024)||'/'||round(pga_max_mem/1024/1024)
      into l_v$process_mem
      from v$process where addr = (select paddr from v$session where sid = p_sid);
     
   select 'Category(Alloc/Used/Max) >>> '||
            listagg(Category||'('||round(allocated/1024/1024)||'/'||
                    round(used/1024/1024)||'/'||round(max_allocated/1024/1024)||') > ')
    within group (order by Category desc) name_usage_list
      into l_v$process_memory_mem
      from v$process_memory
      where pid = (select pid from v$process
                    where addr = (select paddr from v$session where sid = p_sid));
 
   dbms_output.put_line(rpad(l_name, 30)||' > '||rpad(l_v$process_mem, 50)||
                             ' --- '||l_v$process_memory_mem);
 end rpt;
 -------------------------------------------
 procedure allo(p_name varchar2) as
 begin
  rpt(p_name);
  select 'M' bulk collect into p_tab from dual connect by level <= 1000*1000;
  rpt('Allo');
 end allo;

 -------------------------------------------
 procedure allo_aa(p_name varchar2) as
 begin
  rpt(p_name);
  select 'M' bulk collect into p_tab_aa from dual connect by level <= 1000*1000;
  rpt('Allo');
 end allo_aa;
 -------------------------------------------
 procedure free as
 begin
  dbms_session.free_unused_user_memory;
  rpt('Free');
 end free;
 -------------------------------------------
 procedure nullify as
 begin
  allo('--- 1. Nullify');
  p_tab := null;               
  rpt('Nullify');
  free;                 
 end nullify;
 -------------------------------------------
 procedure empty as
  l_tab_temp      t_tab_type := t_tab_type();
 begin
  allo('--- 2_1. Empty_'||
     (case when l_tab_temp is null then 'null' else to_char(l_tab_temp.count) end));
  p_tab := l_tab_temp;
  rpt('Empty');
  free;                
 end empty;
 -------------------------------------------
 procedure empty_no_init as
  l_tab_temp      t_tab_type;
 begin
  allo('--- 2_2. Empty_no_init_'||
     (case when l_tab_temp is null then 'null' else to_char(l_tab_temp.count) end));
  p_tab := l_tab_temp;
  rpt('Empty_no_init');
  free;                
 end empty_no_init;

  -------------------------------------------
 procedure empty_aa as
  l_tab_temp      t_tab_aa;
 begin
  l_tab_temp(1) := 'Empty_aa';  -- init with one element
  l_tab_temp.delete(1);         -- delete it
  allo_aa('--- 2_3. Empty_aa_'||
   (case when l_tab_temp is null then 'null' else to_char(l_tab_temp.count) end));
  p_tab_aa := l_tab_temp;
  rpt('Empty_aa');
  free;                
 end empty_aa;
 -------------------------------------------
 procedure empty_aa_no_init as
  l_tab_temp      t_tab_aa;
 begin
  allo_aa('--- 2_4. Empty_aa_no_init_'||
   (case when l_tab_temp is null then 'null' else to_char(l_tab_temp.count) end));
  p_tab_aa := l_tab_temp;
  rpt('Empty_aa_no_init');
  free;                
 end empty_aa_no_init;

 -------------------------------------------
 procedure delete as
 begin
  allo('--- 3. Delete');
  p_tab.delete;
  rpt('Delete');
  free;                
 end delete;
 -------------------------------------------
 procedure copy as
  l_tab_temp      t_tab_type := t_tab_type();
 begin
  allo('--- 4. Copy');
  l_tab_temp := p_tab;
  rpt('Copy');
  free;                 
 end copy;
 -------------------------------------------
 procedure copy_and_touch as
  l_tab_temp      t_tab_type := t_tab_type();
    l_x             char(1000);
 begin
  allo('--- 5. Copy_and_Touch');
  l_tab_temp := p_tab;
  rpt('Copy');
  l_x := l_tab_temp(19);
  rpt('Touch');
  free;                      
 end copy_and_touch;
 -------------------------------------------
 procedure app_dealloc as
  l_tab_temp      t_tab_type := t_tab_type();
 begin
  allo('--- 6. App_dealloc');
  l_tab_temp := p_tab;
  rpt('Copy');
  p_tab     := p_tab_empty;
  rpt('Empty');
  p_tab     := l_tab_temp;
  rpt('Dealloc');
  free;                       
 end app_dealloc;
 -------------------------------------------
 procedure app_sparse as
  l_tab_temp      t_tab_type := t_tab_type();
 begin
  allo('--- 7. App_sparse');
  for i in 1..p_tab.count/4 loop
   p_tab.delete(i*4);
  end loop;
  rpt('New cnt='||p_tab.count);
  l_tab_temp := p_tab;
  rpt('Copy');
  p_tab     := p_tab_empty;
  rpt('Empty');
  p_tab     := l_tab_temp;
  rpt('Dealloc');
  free;                     
 end app_sparse;
end mem_reclaim_test;
/

set lines 200
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.nullify;
exec mem_reclaim_test.rpt('--- End');
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.empty;
exec mem_reclaim_test.rpt('--- End');
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.empty_no_init;
exec mem_reclaim_test.rpt('--- End');
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.empty_aa;
exec mem_reclaim_test.rpt('--- End');
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.empty_aa_no_init;
exec mem_reclaim_test.rpt('--- End');
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.delete;
exec mem_reclaim_test.rpt('--- End');
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.copy;
exec mem_reclaim_test.rpt('--- End');
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.copy_and_touch;
exec mem_reclaim_test.rpt('--- End');
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.app_dealloc;
exec mem_reclaim_test.rpt('--- End');
exec dbms_session.reset_package;
set serveroutput on;
exec mem_reclaim_test.app_sparse;
exec mem_reclaim_test.rpt('--- End');

Output

SQL> exec mem_reclaim_test.nullify;
--- 1. Nullify       > Used/Alloc/Freeable/Max >>> 2/3/1/2296
Allo                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Nullify              > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Free                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
--- End              > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296

SQL> exec mem_reclaim_test.empty;
--- 2_1. Empty_0     > Used/Alloc/Freeable/Max >>> 2/3/1/2296
Allo                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Empty                > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Free                 > Used/Alloc/Freeable/Max >>> 2/1180/1178/2296
--- End              > Used/Alloc/Freeable/Max >>> 2/3/1/2296

SQL> exec mem_reclaim_test.empty_no_init;
--- 2_2. Empty_no_init_null > Used/Alloc/Freeable/Max >>> 5/6/1/2297       
Allo                        > Used/Alloc/Freeable/Max >>> 1180/1181/0/2297 
Empty_no_init               > Used/Alloc/Freeable/Max >>> 1180/1181/0/2297 
Free                        > Used/Alloc/Freeable/Max >>> 1180/1181/0/2297 
--- End                     > Used/Alloc/Freeable/Max >>> 1180/1181/0/2297 

SQL> exec mem_reclaim_test.empty_aa;
--- 2_3. Empty_aa_0 > Used/Alloc/Freeable/Max >>> 5/6/1/2297
Allo                > Used/Alloc/Freeable/Max >>> 1180/1181/0/2297
Empty_aa            > Used/Alloc/Freeable/Max >>> 1180/1181/0/2297
Free                > Used/Alloc/Freeable/Max >>> 5/1181/1176/2297
--- End             > Used/Alloc/Freeable/Max >>> 5/6/1/2297

SQL> exec mem_reclaim_test.empty_aa_no_init;
--- 2_4. Empty_aa_no_init_0 > Used/Alloc/Freeable/Max >>> 5/6/1/2297
Allo                        > Used/Alloc/Freeable/Max >>> 1180/1181/0/2297
Empty_aa_no_init            > Used/Alloc/Freeable/Max >>> 1180/1181/0/2297
Free                        > Used/Alloc/Freeable/Max >>> 5/1181/1176/2297
--- End                     > Used/Alloc/Freeable/Max >>> 5/6/1/2297

SQL> exec mem_reclaim_test.delete;
--- 3. Delete        > Used/Alloc/Freeable/Max >>> 2/3/1/2296
Allo                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Delete               > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Free                 > Used/Alloc/Freeable/Max >>> 2/1180/1178/2296
--- End              > Used/Alloc/Freeable/Max >>> 2/3/1/2296

SQL> exec mem_reclaim_test.copy;
--- 4. Copy          > Used/Alloc/Freeable/Max >>> 2/3/1/2296
Allo                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Copy                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Free                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
--- End              > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296

SQL> exec mem_reclaim_test.copy_and_touch;
--- 5. Copy_and_Touc > Used/Alloc/Freeable/Max >>> 2/3/1/2296
Allo                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Copy                 > Used/Alloc/Freeable/Max >>> 2295/2296/0/2296
Touch                > Used/Alloc/Freeable/Max >>> 2295/2296/0/2296
Free                 > Used/Alloc/Freeable/Max >>> 2295/2296/0/2296
--- End              > Used/Alloc/Freeable/Max >>> 1179/1181/1/2296

SQL> exec mem_reclaim_test.app_dealloc;
--- 6. App_dealloc   > Used/Alloc/Freeable/Max >>> 2/3/1/2296
Allo                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Copy                 > Used/Alloc/Freeable/Max >>> 2295/2296/0/2296
Empty                > Used/Alloc/Freeable/Max >>> 2295/2296/0/2296
Dealloc              > Used/Alloc/Freeable/Max >>> 2295/2296/0/2296
Free                 > Used/Alloc/Freeable/Max >>> 2295/2296/0/2296
--- End              > Used/Alloc/Freeable/Max >>> 1179/1181/1/2296

SQL> exec mem_reclaim_test.app_sparse;
--- 7. App_sparse    > Used/Alloc/Freeable/Max >>> 2/3/1/2296
Allo                 > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
New cnt=750000       > Used/Alloc/Freeable/Max >>> 1179/1180/0/2296
Copy                 > Used/Alloc/Freeable/Max >>> 2016/2017/0/2296
Empty                > Used/Alloc/Freeable/Max >>> 2016/2017/0/2296
Dealloc              > Used/Alloc/Freeable/Max >>> 2016/2017/0/2296
Free                 > Used/Alloc/Freeable/Max >>> 1722/2017/294/2296
--- End              > Used/Alloc/Freeable/Max >>> 885/886/1/2296

Analysis

Based on the above output, we can conclude:

 1. nullify:                   Memory not released.
 2. empty:                   In case of nested table, when making empty with an initialized nested table, memory released; 
                                   when making empty with a non-initialized nested table, memory not released;

                                   In case of associative array, memory always released, irrelevant of initialization,
                                   because associative array is always initialized with 0 element, even only declared without initialization.

                                   The difference is probably that nested table is an object, in OO paradigm, non-initialized variable is NULL.
                                   So making empty with a non-initialized nested table, is same as Nullify.
 3. delete:                   Memory released.
 4. copy:                     If not touched, memory stays same.
                   2013Sep05 Updated:
                          One Reader pointed out that this behavior is true when plsql_optimize_level >= 2(Default);
                          for 0 and 1, it is the same as copy_and_touch.
 5. copy_and_touch:  If touched, a real copy created, memory doubled.
                                  This is already noticed in Step: Touch, that means a forward allocation.
 6. app_dealloc:         Memory doubled.
 7. app_sparse:          Deleting 1/4 of entries (294=1179*(1/4)), the sparse nested table is 885=1179*(3/4) .
                                  Therefore, the total aloocated memory of 2017 is around the sum of two nested tables
                                  (p_tab=1179 plus l_tab_temp=885).

                                  At the end, the 1/4 deleted entries are garbage collected, and remaining 3/4 entries amounts to 885.

                                  That was shown by the line:
                                       Free  > Used/Alloc/Freeable/Max >>> 1722/2017/294/2296
                                  where (not very exact):
                                      1722(Used) = 2017(Alloc) - 294(Freeable)

One interesting result is the difference between testcase: copy and copy_and_touch. As long as the memory copy is not touched, the real memory is not allocated. This is probably the Copy-on-write optimization in PL/SQL.

Nested Table Operators

declare
  type      doubly_linked_list is table of number;
  l_dl_list doubly_linked_list := doubly_linked_list();
begin
  l_dl_list := new doubly_linked_list();
  l_dl_list.extend(4);
  l_dl_list(1) := -1;
  l_dl_list(2) := -2;
  l_dl_list(3) := -3;
  l_dl_list(4) := -4;

  dbms_output.put_line('start.count  = '||l_dl_list.count||', last = '||l_dl_list.last);
  l_dl_list.delete(4);  
  dbms_output.put_line('delete.count = '||l_dl_list.count||', last = '||l_dl_list.last);
  l_dl_list.extend;
  dbms_output.put_line('extend.count = '||l_dl_list.count||', last = '||l_dl_list.last);
  
  if l_dl_list.exists(5) then 
    if l_dl_list(5) is null then 
       dbms_output.put_line('  exists(5) true: '||'null');
    else
       dbms_output.put_line('  exists(5) true: '||l_dl_list(5));
    end if;
  else
    dbms_output.put_line('  exists(5) false ');
  end if;
  
  if l_dl_list.exists(4) then 
    if l_dl_list(4) is null then 
       dbms_output.put_line('  exists(4) true: '||'null');
    else
       dbms_output.put_line('  exists(4) true: '||l_dl_list(4));
    end if;
  else
    dbms_output.put_line('  exists(4) false ');
  end if;
  
  l_dl_list.trim;
  dbms_output.put_line('trim_1.count = '||l_dl_list.count||', last = '||l_dl_list.last);
  l_dl_list.trim;
  dbms_output.put_line('trim_2.count = '||l_dl_list.count||', last = '||l_dl_list.last);
  l_dl_list.trim;
  dbms_output.put_line('trim_3.count = '||l_dl_list.count||', last = '||l_dl_list.last);
  l_dl_list.delete;
  dbms_output.put_line('delall.count = '||l_dl_list.count||', last = '||l_dl_list.last);
  l_dl_list.extend;
  dbms_output.put_line('extend.count = '||l_dl_list.count||', last = '||l_dl_list.last);
end;
/  

start.count  = 4, last = 4
delete.count = 3, last = 3
extend.count = 4, last = 5  -- extend not use deleted index
  exists(5) true: null
  exists(4) false
trim_1.count = 3, last = 3  -- trim last
trim_2.count = 3, last = 3  -- trim deleted index
trim_3.count = 2, last = 2
delall.count = 0, last =    -- delete all, and trim to zero
extend.count = 1, last = 1

1. extend and trim are physical space operators.
2. assignment, delete, exists, first/last, count, prior/next are logical operators. They ignore deleted elements.
3. delete without parameters frees the entire memory allocated to the collection, and hence a physical space operator.

See Oracle 12c Collection Methods in PL/SQL Collections and Records of Database PL/SQL Language Reference.

dbms_alert lock hash collision

dbms_alert package calls dbms_lock to synchronize alert senders and receivers based on lockid generated by the name in dbms_alert_info table by a function call:

   dbms_utility.get_hash_value(name, 2000000000, 2048)

which can have a high hash collision probability, and hence occasionally false UL lock contention.

For example, both 'warning_1828' and 'alarm_1828' are hashed to the same value: 2000000389.

 select dbms_utility.get_hash_value(upper('warning_1828'), 2000000000, 2048) lockid_1,
        dbms_utility.get_hash_value(upper('alarm_1828'),   2000000000, 2048) lockid_2 
 from dual;


Here is a testcase.

At first create a helper function:

 create or replace procedure dbms_alert_lock_collision(p_name varchar2) as
   l_message varchar2(1800);
   l_status  pls_integer;
   l_name    varchar2(30);
 begin
   dbms_alert.register(p_name);
   dbms_alert.waitany(l_name, l_message, l_status);
   dbms_output.put_line('Received Name='||l_name||', Message='||l_message||', Status:='||to_char(l_status));
 end;

then open 4 Sqlplus sessions, perform the test steps as follows:

(1). Session-1 at T1
         exec dbms_alert_lock_collision('warning_1828');

(2). Session-2  at T2
         exec dbms_alert_lock_collision('alarm_1828');

(3). Session-3 at T3
         exec dbms_alert.signal('warning_1828', 'warning_1828_msg');

 Firing signal wakes up the waiter session, which changes its state from "pipe get" (wait_class Idle)
 to "enq: UL - contention" (wait_class Application).

(4). Session-4 at T4
         exec dbms_alert.signal('alarm_1828', 'alarm_1828_msg');

(5). Session-3 at T5, wait Session-4 returns (this can take up to 32 seconds), then:
          commit;

 even Session-3 committed, Session-1 is still blocked by 'UL'lock. You can see it by:

  select * from v$lock where type = 'UL';

  select * from v$wait_chains;

(6). Session-4 at T6

         commit;

 Session-4's commit de-locks both Session-1 and Session-2.


If Session-3 at T5 commits before Session-4 at T4 returns, the commit of Session-3 at T5 can probably de-lock Session-1. However the behavior is dependent on the UL lock time sequence.

When signal is sent by the signalling session, but before commit, dbms_alert.waitany requests a UL lock with a timeout: 1, 2, 4, 8, 16, 32 seconds for the first 6 times, and afterwards always with timeout: 32 seconds.

If Session-4 at T4 waits less than 32 seconds, it can be blocked by Session-1 at T1. In this case, Session-3 holds S_MODE(4) lock and blocks Session-1's SX_MODE(3) lock Request, and Session-1's SX_MODE(3) lock Request blocks Session-4's S_MODE(4) lock Request (Session-4 is queued after Session-1). When Session-1 renews its lock request after maximum 32 seconds, Session-4 got its S_MODE(4) lock. And then both Session-3 and Session-4 hold S_MODE(4) lock, whereas both Session-1 and Session-2 are waiting SX_MODE(3) lock. Therefore when Session-3 at T5 commits (and hence release its S_MODE(4) lock), Session-4 is still blocking Session-1 and Session-2.

Following query can help to detect the hash collision names:

  select lockid, count(*) cnt, listagg(name, '; ') within group (order by name)
    from (
      select name, dbms_utility.get_hash_value(upper(name), 2000000000, 2048) lockid
        from (select distinct name from sys.dbms_alert_info) v
    )
  group by lockid
  having count(*) > 1
  order by cnt desc; 

If there are more than 100 waiting sessions like Session-1 and Session-2, the system can be disturbed by the heavy UNDO activities. Here the query to monitor them:

 select n.name, s.sid, s.value
   from v$statname n, v$sesstat s
  where s.statistic# = n.statistic#
    and name in ('data blocks consistent reads - undo records applied'
                ,'rollback changes - undo records applied')

By the way, in 14 May 2013, a first rank result on twin prime conjecture was reported: "Bounded gaps between primes".