Oracle PL/SQL/System Tables Views/v sgastat

compute sum of bytes on pool

   
SQL> break on pool skip 1
SQL> select pool, name, bytes
  2   from v$sgastat
  3   where rownum < 20
  4   order by pool, name;
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  AW SGA                             24
             KGX                            107568
             branch                          14880
             call                            22048
             dummy                            3276
             kcrrny                          25320
             kfasga                           1048
             kfdsga                             44
             kffsga                             48
             kfmsg                            3088
             kksss                            3840
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  knlsg                              80
             kohsg                               4
             kzull                              80
             kzulu                              64
             list                             3584
************                            ----------
sum                                         184996
             buffer_cache                171966464
             fixed_sga                     1287016
             log_buffer                    2904064
POOL         NAME                            BYTES
------------ -------------------------- ----------
************                            ----------
sum                                      176157544

19 rows selected.
SQL>
SQL>
SQL>

Query v$sgastat

  

SQL>
SQL> declare
  2   x varchar2(32767);
  3   begin
  4       for i in 1 .. 10 loop
  5           x := x || " procedure X"||i||";";
  6       end loop;
  7       execute immediate "create or replace package MEMTEST is "||x||" end;";
  8       x := replace(x,";"," is y number; begin y := 0; end;");
  9       execute immediate "create or replace package body MEMTEST is "||x||" end;";
 10   end;
 11  /
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> exec memtest.x1;
PL/SQL procedure successfully completed.
SQL> select * from v$sgastat where name like "PL/SQL MPCODE";
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  PL/SQL MPCODE                   24456
1 row selected.
SQL> exec memtest.x2;
PL/SQL procedure successfully completed.
SQL> select * from v$sgastat where name like "PL/SQL MPCODE";
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  PL/SQL MPCODE                   24456
1 row selected.
SQL> exec memtest.x3;
PL/SQL procedure successfully completed.
SQL> select * from v$sgastat where name like "PL/SQL MPCODE";
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  PL/SQL MPCODE                   24456
1 row selected.
SQL> exec memtest.x4;
PL/SQL procedure successfully completed.

SQL>
SQL>