Wednesday, June 26, 2013

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.