PGA – PROGRAM GLOBAL AREA

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid FORMAT 999 HEADING ‘SID’
COLUMN oracle_username FORMAT a12 HEADING ‘Oracle User’ JUSTIFY right
COLUMN os_username FORMAT a9 HEADING ‘O/S User’ JUSTIFY right
COLUMN session_program FORMAT a18 HEADING ‘Session Program’ TRUNC
COLUMN session_machine FORMAT a8 HEADING ‘Machine’ JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING ‘PGA Memory’
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING ‘PGA Memory Max’
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING ‘UGA Memory’
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING ‘UGA Memory MAX’

SELECT s.SID SID, LPAD (s.username, 12) oracle_username,
LPAD (s.osuser, 9) os_username, s.program session_program,
LPAD (s.machine, 8) session_machine,
(SELECT ss.VALUE FROM v$sesstat ss, v$statname sn
WHERE ss.SID = s.SID
AND sn.statistic# = ss.statistic#
AND sn.NAME = ‘session pga memory’) session_pga_memory,
(SELECT ss.VALUE FROM v$sesstat ss, v$statname sn
WHERE ss.SID = s.SID AND sn.statistic# = ss.statistic#
AND sn.NAME = ‘session pga memory max’) session_pga_memory_max,
(SELECT ss.VALUE
FROM v$sesstat ss, v$statname sn
WHERE ss.SID = s.SID
AND sn.statistic# = ss.statistic#
AND sn.NAME = ‘session uga memory’) session_uga_memory,
(SELECT ss.VALUE FROM v$sesstat ss, v$statname sn
WHERE ss.SID = s.SID AND sn.statistic# = ss.statistic# AND sn.NAME = ‘session uga memory max’) session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC

select sum(Trunc(b.value/1024)) AS memory_kb_total , a.username, a.machine,count(*) as “nu_sessoes”
from
gv$session a,
gv$sesstat b,
gv$statname c
WHERE a.sid = b.sid
AND a.inst_id = b.inst_id
AND b.statistic# = c.statistic#
AND b.inst_id = c.inst_id
AND c.name = ‘session pga memory’
–AND a.program IS NOT NULL
–and program like ‘%SQL%’
group by a.inst_id,a.username, a.machine
ORDER BY memory_kb_total DESC;

Teste estas também…

A unica fez na vida que usei essas querys, foi para provar que conexões via ferramenta Client TOAD consome mais MEM do que as demais.

E num relatóri gerencial para aprovar proibir ferramentas client em base de prod, pois consomem memôria do servidor mesmo sem rodar nada.

Tuning de memória da intância existem outros métodos mas eficazes de ser fazer, alêm de análisar quanto cada sessão consome .