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

Script

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 ('__shared_pool_size', 'large_pool_size', 'pga_aggregate_target', '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;

Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session