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

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 username like '%CANOPY%'
       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;