get_package_memory_utilization to expose memory usage of instantiated packages,
functions, triggers, or types so that the memory consumption of program units is revealed and the analysis of ORA-04030 becomes pretty straightforward.
It outputs 5 PL/SQL associative arrays, but nowadays Oracle people get used to v$ like views. Here is an example how to turn them into such a view by pipelined function.
create or replace package sess_mem_usage as
type t_rec is record (
owner varchar2(4000)
,unit varchar2(4000)
,type varchar2(40)
,used number
,free number
);
type t_rec_tab is table of t_rec;
function get return t_rec_tab pipelined;
end sess_mem_usage;
/
create or replace package body sess_mem_usage as
function map_type2name(p_type integer)
return varchar2
as
l_v varchar2(20);
begin
l_v := case p_type when 7 then '(procedure)'
when 8 then '(function)'
when 9 then '(package)'
when 11 then '(package body)'
when 12 then '(trigger)'
when 13 then '(type)'
when 14 then '(type body)'
else ''
end;
return rpad(to_char(p_type), 3) || l_v;
end map_type2name;
-- since Oracle 11.2.0.4.0
function get return t_rec_tab pipelined
is
l_desired_info dbms_session.integer_array;
l_owner_array dbms_session.lname_array;
l_unit_array dbms_session.lname_array;
l_type_array dbms_session.integer_array;
l_amounts dbms_session.big_integer_matrix;
l_used_array dbms_session.big_integer_array;
l_free_array dbms_session.big_integer_array;
l_rec t_rec;
begin
l_desired_info(1) := dbms_session.used_memory;
l_desired_info(2) := dbms_session.free_memory;
dbms_session.get_package_memory_utilization(l_desired_info, l_owner_array, l_unit_array, l_type_array, l_amounts);
for i in 1 .. l_owner_array.count loop
l_rec.owner := l_owner_array(i);
l_rec.unit := l_unit_array (i);
l_rec.type := map_type2name(l_type_array(i));
l_rec.used := l_amounts(1)(i);
l_rec.free := l_amounts(2)(i);
pipe row(l_rec);
end loop;
return;
end get;
function get_deprecated return t_rec_tab pipelined as
l_owner_array dbms_session.lname_array;
l_unit_array dbms_session.lname_array;
l_type_array dbms_session.integer_array;
l_used_array dbms_session.integer_array;
l_free_array dbms_session.integer_array;
l_rec t_rec;
begin
dbms_session.get_package_memory_utilization
(l_owner_array, l_unit_array, l_type_array,
l_used_array, l_free_array);
for i in 1..l_owner_array.count loop
l_rec.owner := l_owner_array(i);
l_rec.unit := l_unit_array(i);
l_rec.type := map_type2name(l_type_array(i));
l_rec.used := l_used_array(i);
l_rec.free := l_free_array(i);
pipe row(l_rec);
end loop;
return;
end get_deprecated;
end sess_mem_usage;
/
create or replace force view v$ora_sess_mem_usage as
select * from table(sess_mem_usage.get);
then you can access them by:
select * from v$ora_sess_mem_usage order by used desc;
Comparing to directly calling dbms_session.get_package_memory_utilization, order by and where condition are able to be flexibly applied instead of PL/SQL code.
The limitation with dbms_session.get_package_memory_utilization is that one can only watch its own memory usage. Another limitation is that memory amount is expressed in dbms_session.integer_array, which is a table of BINARY_INTEGER with maximum of 2147483647 (2 ^ 31 – 1, or 2GB). So if a package consumes more than 2GB, it will output a negative value. (This is probably similar as COUNT STOPKEY limitation of 2 ^ 32 – 1 reported in blog: http://blog.tanelpoder.com/2010/10/25/count-stopkey-operation-the-where-rownum)
Addendum (2016.01.31):
From Oracle 11.2.0.4.0, a new overloaded API:
dbms_session.get_package_memory_utilization(desired_info IN integer_array, ...)
is added, which measures memory usage in INTEGER (i.e. NUMBER(38,0)), instead of BINARY_INTEGER(sb4). That means memory can be represented till (10 ^ 38) instead of (2 ^ 32 – 1).
With the new created view, following program dumps the memory usage of current session.
create or replace package dump_mem_usage as
l_seq number := 0;
function get_next_seq return number;
function round_mb(p_bytes number) return number;
procedure run(p_name varchar2 := 'Test', p_dump_limit number := 2);
end;
/
create or replace package body dump_mem_usage as
function get_next_seq return number as
begin
l_seq := l_seq + 1;
return l_seq;
end;
function round_mb(p_bytes number) return number as
begin
return round(p_bytes/1024/1024, 2);
end;
procedure run(p_name varchar2, p_dump_limit number) as
c_max_pls_integer constant pls_integer := 2147483647;
l_v$process_mem varchar2(4000);
l_v$process_memory_mem varchar2(4000);
l_usage_list varchar2(2000);
l_sum_abs_mb number;
l_mysid number;
begin
dbms_output.put_line('-------- '||dump_mem_usage.get_next_seq||'. '||p_name||' --------');
select sid into l_mysid from v$mystat where rownum=1;
select '(SID='||l_mysid||')'||
'Used/Alloc/Freeable/Max='||
dump_mem_usage.round_mb(pga_used_mem) ||'/'||
dump_mem_usage.round_mb(pga_alloc_mem) ||'/'||
dump_mem_usage.round_mb(pga_freeable_mem)||'/'||
dump_mem_usage.round_mb(pga_max_mem)
into l_v$process_mem
from v$process where addr =
(select paddr from v$session where sid=l_mysid);
dbms_output.put_line('v$process: ' ||l_v$process_mem);
select 'Category/Alloc/Used/Max='||
listagg(Category||'/'||
dump_mem_usage.round_mb(allocated)||'/'||
dump_mem_usage.round_mb(used) ||'/'||
dump_mem_usage.round_mb(max_allocated)||',')
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=l_mysid));
dbms_output.put_line('v$process_memory: ' ||l_v$process_memory_mem);
select listagg(owner||'; '||unit||'; '||type||'; '||
dump_mem_usage.round_mb(used)||'; '||
dump_mem_usage.round_mb(free)||'; '||
alloc_abs_mb, chr(10))
within group (order by alloc_abs_mb desc) name_usage_list
,sum(alloc_abs_mb) sum_abs_mb
into l_usage_list
,l_sum_abs_mb
from (
select v.*
,dump_mem_usage.round_mb
(decode(sign(used), -1, c_max_pls_integer, used)
+ decode(sign(free), -1, c_max_pls_integer, free)) alloc_abs_mb
from v$ora_sess_mem_usage v
order by alloc_abs_mb desc
)
where rownum <= p_dump_limit;
dbms_output.put_line('[Owner; Unit; Type; Used; Free; sum_abs_mb(' || l_sum_abs_mb ||')]' || chr(10) ||
l_usage_list);
end;
end;
/
The above procedure can be simply integrated as part of memory usage monitoring.
As the name indicated, dbms_session.get_package_memory_utilization is restricted to package memory usage. It will not report memory consumption of PL/SQL functions and procedures as illustrated by the following example.
Calling "exec mem_use_kilo(100000)" will not list 100MB(at least) of l_tab memory.
create or replace procedure mem_use_kilo(p_kilo number) as
type t_tab is table of varchar2(1000);
l_tab t_tab := t_tab();
begin
select rpad('E', 1000, 'N')
bulk collect into l_tab
from dual connect by level <= p_kilo;
dump_mem_usage.run('mem_use_kilo Test');
end;
/
Normally PL/SQL table memory allocation is counted as 'session pga memory' in v$sesstat, and as category 'PL/SQL' in v$process_memory.
Session PGA memory usage is exposed in v$process_memory, v$process, v$sesstat(join with v$statname), as well as v$active_session_history, dba_hist_active_sess_history. But the values reported in v$sesstat, and v$active_session_history, dba_hist_active_sess_history is limited by 4GB (data overflow of 32 bits).
4GB limitation is fixed in Oracle 12.1.0.2.0, see:
Bug 15951898 - Session Statistics for PGA Memory are Incorrect in V$ACTIVE_SESSION_HISTORY / V$SESSTAT (Doc ID 15951898.8)
The original discussion about UNIX commands to watch PGA, SGA size is moved to Blog:
PGA, SGA memory usage watching from UNIX
