Script To Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over Time

set echo off;
 set feedback off;
 set heading off;
 set linesize 128;
 set show off;
 set pagesize 55;
 set trimspool on;
 set verify off;
 column "SID AND SERIAL#" FORMAT A19
 col SNAP_COLUMN new_value SNAP_TIME
 col SNAP_EOF_NAME new_value EOF_NAME
 col SNAP_HOST_NAME new_value THE_HOST_NAME
 col SNAP_INSTANCE_NAME new_value THE_NAME_OF_THE_INSTANCE
 col SNAP_RDBMS_VERSION new_value THE_RDBMS_VERSION
 set term off;
 select to_char(sysdate,'YYYYMMDD_HH24MISS') "SNAP_COLUMN" from dual;
 select trim(host_name) "SNAP_HOST_NAME" from v$instance;
 select trim(instance_name) "SNAP_INSTANCE_NAME" from v$instance;
 select trim(version) "SNAP_RDBMS_VERSION" from v$instance;
 select '&THE_NAME_OF_THE_INSTANCE'||'_'||'&SNAP_TIME'||'.LST' "SNAP_EOF_NAME" from dual;
 drop table maxpgauga;
 create table maxpgauga as select s.sid,
                                  s2.serial#,
                                  n.name,
                                  s.value,
                                  decode(s2.username,null,s2.program,s2.username) "USERNAME",
                                  s2.logon_time
                           from   v$statname n,
                                  v$sesstat s,
                                  v$session s2
                           where  n.statistic# = s.statistic# and
                                  (s.sid = s2.sid) and
                                  name like 'session%memory max%';
 drop table curpgauga;
 create table curpgauga as select s.sid,
                                  s2.serial#,
                                  n.name,
                                  s.value,
                                  decode(s2.username,null,s2.program,s2.username) "USERNAME",
                                  s2.logon_time
                           from   v$statname n,
                                  v$sesstat s,
                                  v$session s2
                           where  n.statistic# = s.statistic# and
                                  (s.sid = s2.sid) and
                                  name like 'session%memory' and
                                  name not like 'session%memory max%';
 
 set term on;
 
 spool ORACLE_MEMORY_USAGE_SNAPSHOT_&EOF_NAME
 
 select 'Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session' from dual;
 select 'Host........: '||'&THE_HOST_NAME' from dual;
 select 'Name........: '||'&THE_NAME_OF_THE_INSTANCE' from dual;
 select 'Version.....: '||'&THE_RDBMS_VERSION' from dual;
 select 'Startup Time: '||to_char(min(logon_time),'YYYY-MM-DD HH24:MI:SS') from curpgauga;
 select 'Current Time: '||to_char(sysdate,'YYYY.MM.DD-HH24:MI:SS') from dual;
 select 'Worst possible value of concurrent PGA + UGA memory usage per session:' from dual;
 set heading on
 select   trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
          username "USERNAME OR PROGRAM",
          sum(value),
          to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
 from     maxpgauga
 group by sid,
          serial#,
          username,
          logon_time
 order by sum(value) desc;
 set heading off
 select 'Worst possible total and average values of concurrent PGA + UGA memory usage:' from dual;
 select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||trunc(count(*)/2)||' sessions.' from maxpgauga;
 select 'Approximate value of current PGA + UGA memory usage per session:' from dual;
 set heading on
 select   trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
          username "USERNAME OR PROGRAM",
          sum(value),
          to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
 from     curpgauga
 group by sid,
          serial#,
          username,
          logon_time
 order by sum(value) desc;
 set heading off
 select 'Current total and average values of concurrent PGA + UGA memory usage:' from dual;
 select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||trunc(count(*)/2)||' sessions.' from curpgauga;
 select 'Maximum value of PGA memory usage per session:' from dual;
 set heading on
 select   trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
          username "USERNAME OR PROGRAM",
          value,
          to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
 from     maxpgauga
 where    name like 'session pga memory max%'
 order by value desc, sid desc;
 set heading off
 select 'Worst possible total and average values of concurrent PGA memory usage:' from dual;
 select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session pga memory max%';
 select 'Maximum value of UGA memory usage per session:' from dual;
 set heading on
 select   trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
          username "USERNAME OR PROGRAM",
          value,
          to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
 from     maxpgauga
 where    name like 'session uga memory max%'
 order by value desc, sid desc;
 set heading off
 select 'Worst possible total and average values of concurrent UGA memory usage:'  from dual;
 select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session uga memory max%';
 select 'Current value of PGA memory usage per session:' from dual;
 set heading on
 select   trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
          username "USERNAME OR PROGRAM",
          value,
          to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
 from     curpgauga
 where    name like 'session pga memory%'
 order by value desc, sid desc;
 set heading off
 select 'Current total and average values of concurrent PGA memory usage:' from dual;
 select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session pga memory%';
 select 'Current value of UGA memory usage per session:' from dual;
 set heading on
 select   trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
          username "USERNAME OR PROGRAM",
          value,
          to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
 from     curpgauga
 where    name like 'session uga memory%'
 order by value desc, sid desc;
 set heading off
 select 'Current total and average values of concurrent UGA memory usage:' from dual;
 select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session uga memory%';
 select 'Current SGA structure sizings:' from dual;
 show sga
 select 'Some initialization parameter values at instance startup:' from dual;
 select trim(name)||'='||value
 from v$parameter
 where name in ('java_pool_size',
                'large_pool_size',
                'olap_page_pool_size',
                'pga_aggregate_target',
                'sga_max_size',
                'sga_target',
                'shared_pool_size',
                'sort_area_size',
                'streams_pool_size') order by name;
 select 'Current Time: '||TO_CHAR(sysdate,'YYYY.MM.DD-HH24:MI:SS') from dual;
 spool off
 set feedback on;
 set heading on;
 set linesize 80;
 set pagesize 14;
 set verify on;
 set echo on;