Memory

PGA Memory Used per Session

-- PGA Memory Used per Session
col name for a20
col machine for a15
break on report
compute sum label "total pga mem" of value on report
SELECT s.sid,
       username,
       machine,
       ss.status,
       name,
       value
FROM   v$statname n,
       v$sesstat s,
       v$session ss
WHERE  n.STATISTIC# = s.STATISTIC#
       AND s.sid = ss.sid
       AND n.name ='session pga memory'
 ORDER BY value DESC;

PGA Memory Usage Summary by Username

-- PGA Memory Usage Summary by Username
SELECT username,
       RTRIM(TO_CHAR(SUM(value), '999,999,999,999')) as TotalMemUsed,
       RTRIM(TO_CHAR(AVG(value), '999,999,999,999')) as AvgMemUsed,
       COUNT(*) as NumSessions
FROM   v$statname n,
       v$sesstat s,
       v$session ss
WHERE  n.STATISTIC# = s.STATISTIC#
       AND s.sid = ss.sid
       AND n.name like 'session pga memory'
 GROUP BY USERNAME
 ORDER BY AVG(VALUE) DESC;

AWE Memory Map Activity Percent (8i, 9i)
If you are using AWE Memory Windowing on Windows operating system, this can tell you how much action the Memory Window is seeing.

-- AWE Memory Map Activity Percent 
select (select value from v$sysstat where statistic# = 155) / 
       (select value from v$sysstat where statistic# = 154) * 100  as percent 
from dual;