Oracle: how to find out how much space (on RAM) is used by a function table?-CodePudding

I'm thinking about implementing a function table. As the table could have around 100 000 result sets I wish to know the memory usage of it (on the RAM). Can I check (if possible) with toad or pl/sql developer how much memory is allocated for my result sets?

Thanks in advance!

CodePudding user response：

Your session PGA holds the data and thus you can query your session statistics to see how much is consumed, eg

SQL> select s.name, st.value
  2  from   v$statname s, v$mystat st
  3  where st.statistic# = s.statistic#
  4  and s.name like '%pga%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory                                                  5025416
session pga memory max                                              5025416

SQL> declare
  2    type t is table of char(1000) index by pls_integer;
  3    r t;
  4  begin
  5    for i in 1 .. 10000
  6    loop
  7      r(i) := rpad('x',1000);
  8    end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select s.name, st.value
  2  from   v$statname s, v$mystat st
  3  where st.statistic# = s.statistic#
  4  and s.name like '%pga%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory                                                  4370056
session pga memory max                                             16297608