6 Replies Latest reply: Jan 29, 2013 12:04 AM by Girish Sharma RSS

    session PGA memory

    Manjusha Muraleedas
      Hi,

      I am trying to find out the used PGA memory
      CREATE OR REPLACE TYPE T_KEY_VALUE_DATA IS OBJECT (
      KEY VARCHAR2 (50),
      VALUE VARCHAR2 (4000)
      );
      CREATE OR REPLACE TYPE T_KEY_VALUE_TABLE IS TABLE OF T_KEY_VALUE_DATA;
       
      CREATE OR REPLACE TYPE T_VARCHAR_TABLE IS TABLE OF VARCHAR2 (4000);
      / 
       
      create or replace function get_memory_status return t_key_value_table
           pipelined is
       
      begin
           for i in (SELECT n.name name, to_char(s.VALUE) VALUE
                                         FROM v$sesstat s,
                                                    v$statname n,
                                                    (SELECT *
                                                             FROM v$session
                                                         WHERE audsid = USERENV('SESSIONID')) my_session
                                     WHERE s.statistic# = n.statistic#
                                          AND s.sid = my_session.sid
                                          and n.name like 'session pga memory%') loop
                pipe row(t_key_value_data(I.NAME, I.VALUE));
           end loop;
           return;
      exception
           when others then
                null;
                return;
      end get_memory_status;
      / 
       
       
      SQL> DECLARE L_VARCHAR_TAB t_varchar_table := t_varchar_table();
        2  BEGIN
        3  DBMS_OUTPUT.PUT_LINE('BEFORE:'); FOR I IN (SELECT * FROM TABLE(get_memory_status)) LOOP DBMS_OUTPUT.PUT_LINE(I.KEY || '-' || I.VALUE);
        4  END LOOP;
        5  
        6  SELECT CAST(COLLECT(OB.Object_Name) AS
        7  t_varchar_table) INTO L_VARCHAR_TAB FROM User_Objects OB; DBMS_OUTPUT.PUT_LINE('AFTER:'); FOR I IN (SELECT * FROM TABLE(get_memory_status)) LOOP DBMS_OUTPUT.PUT_LINE(I.KEY || '-' || I.VALUE);
        8  END LOOP;
        9  END;
       10  / 
       
       
      out out

      BEFORE:
      session pga memory-777812
      session pga memory max-1433172
      AFTER:
      session pga memory-1105492
      session pga memory max-1433172

      So how can I find out the used PGA memory ?
      1105492 -777812 =337680
      ??

      'session pga memory' means session pga memory being used or filled???

      Edited by: Manjusha Muraleedas on २९ जनवरी, २०१३ १०:०८ पूर्वाह्न