-- 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 99999990.90
column C2 head 'AVG|[kbyte]' format 99999990.90
column C3 head 'MIN|[kbyte]' format 99999990.90
column C4 head 'MAX|[kbyte]' format 99999990.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
/

hostgator