-- #############################################################################################
--
-- %Purpose: Monitor Private SQL Areas and PL/SQL space in the UGA and SGA
--
--           PL/SQL allocates most memory from the UGA which is
--           located in the SGA when shared servers are used
--
--           UGA = User Global Area
--           SGA = System Global Area
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
set termout on;

column  NA          head 'STATISTIC'    format a29
column  NR_SESS     head '#USERS'       format 9999
column  C1          head 'SUM|[kbyte]'  format 999990.90
column  C2          head 'AVG|[kbyte]'  format 999990.90
column  C3          head 'MIN|[kbyte]'  format 999990.90
column  C4          head 'MAX|[kbyte]'  format 999990.90

ttitle  left   'Monitor Private SQL Areas and PL/SQL space'    skip 2

spool   monitor_private_SQL_areas.log

select  rpad (B.NAME, 29, '.')  as NA,
        COUNT(*) as NR_SESS,
        SUM(A.VALUE)/1024.0 as C1,
        AVG(A.VALUE)/1024.0 as C2,
        MIN(A.VALUE)/1024.0 as C3,
        MAX(A.VALUE)/1024.0 as C4
  from  V$SESSTAT  A,
        V$STATNAME B
 where  A.STATISTIC# = B.STATISTIC#
   and  (B.NAME like '%pga%'
    or  B.NAME like '%uga%'
    or  B.NAME like '%stored%')
 group  by B.NAME
/