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.
