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
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);
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;
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;
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
- Oracle MOS Notes: How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)
- Oracle Memory Troubleshooting, Part 4: Drilling down into PGA memory usage with V$PROCESS_MEMORY_DETAIL
- dbms_session package_memory_utilization
