Wednesday, April 27, 2011

dbms_session package_memory_utilization

Oracle 11g Release 2 extends dbms_session Package by introducing a new Procedure:
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);
  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;
  begin
    dbms_output.put_line('-------- '||dump_mem_usage.get_next_seq||'. '||p_name||' --------');
    select '(SID='||sys.dbms_support.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=sys.dbms_support.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=sys.dbms_support.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 space usage watching from UNIX