CREATE OR REPLACE PACKAGE plsql_memory AUTHID DEFINER 
/*
Dependencies:

    SELECT privileges required on:
       v$mystat
       v$statname

    Here are the statements you should run:

    GRANT SELECT ON v$mystat TO schema;
    GRANT SELECT ON v$statname TO schema;

Example of use:

CREATE OR REPLACE PACKAGE plsql_memory_globals
IS
   TYPE strings_aat
   IS
      TABLE OF varchar2 (10000)
         INDEX BY pls_integer;

   g_list_of_strings   strings_aat;
END plsql_memory_globals;

DECLARE
   l_strings   plsql_memory_globals.strings_aat;

   PROCEDURE run_my_application
   IS
   BEGIN
      FOR i IN 1 .. 10000
      LOOP
         FOR j IN 1 .. 10
         LOOP
            l_strings (i + j * 100000 - 1) := TO_CHAR (i);

            plsql_memory_globals.g_list_of_strings (i + j * 100000 - 1) := TO_CHAR (i);
         END LOOP;
      END LOOP;
   END run_my_application;
BEGIN
   plsql_memory.start_analysis;
   run_my_application;
   plsql_memory.show_memory_usage;
   
   plsql_memory_globals.g_list_of_strings.delete;
END plsql_memory_demo;

*** End example
  
*/
IS 
   PROCEDURE start_analysis; 
 
   PROCEDURE show_usage; 
END; 

CREATE OR REPLACE PACKAGE BODY plsql_memory 
IS 
   g_pga_start   PLS_INTEGER; 
 
   FUNCTION statval ( statname_in IN VARCHAR2 ) 
      RETURN NUMBER 
   IS 
      l_memory   PLS_INTEGER; 
   BEGIN 
      SELECT s.VALUE 
        INTO l_memory 
        FROM v$mystat s, 
             v$statname n 
       WHERE      s. statistic# = n. statistic# 
             AND n.name = statname_in; 
 
      RETURN l_memory; 
   END statval; 
 
   PROCEDURE start_analysis 
   IS 
   BEGIN 
      DBMS_SESSION.free_unused_user_memory ; 
      g_pga_start := statval ( 'session pga memory max'); 
   END start_analysis ; 
 
   FUNCTION memory_usage 
      RETURN INTEGER 
   IS 
      l_pga_usage   PLS_INTEGER; 
   BEGIN 
      l_pga_usage := statval ( 'session pga memory max'); 
 
      RETURN l_pga_usage - g_pga_start; 
   END; 
 
   PROCEDURE show_usage 
   IS 
   BEGIN 
      DBMS_OUTPUT.put_line ( 'PGA=' || memory_usage ()); 
   END; 
END plsql_memory;