(V7.3 ~ V8.X) SESSION 별 PGA, UGA MEMORY 사용량 산출하는 스크립트

출처 :  https://kr.forums.oracle.com/forums/thread.jspa?threadID=464020





PURPOSE

이 자료는 특정 session에서 memory를 과도하게 사용하고 있을 때 현재 어떤 프로그램을 수행하고 있는
session에서 memory를 얼마나 사용하고 있는지 확인할 수 있는 스크립트이다.




Explanation

이 스크립트는 session 별로 pga, uga memory 사용량의 합계와 peak time 때의 max 값을 확인할 수 있는 SQL이다.
즉, 다음과 같은 순서로 스크립트의 결과를 볼 수 있다.

1. Current pga, uga session memory
2. Sum of current pga, uga session memory
3. Max(peak) pga, pga session memory
4. Sum of max(peak) pga, uga session memory


***********************************************************************
rem This script will show the current PGA, UGA memory size per session.

set pagesize 66
set pause on
set verify off
set feed off
clear screen

column sid heading 'sid' format 999
column username heading 'username' format a8
column pgm heading 'program' format a25
column terminal heading 'terminal' format a8
column pga heading 'PGA session memory' format a11
column uga heading 'UGA session memory' format a11
column pga_sum heading 'SUM PGA mem' format a12
column uga_sum heading 'SUM UGA mem' format a12
column pga_max heading 'Max PGA session memory' format a15
column uga_max heading 'Max UGA session memory' format a15
column pga_m_sum heading 'Sum Max PGA session memory' format a11
column uga_m_sum heading 'Sum Max UGA session memory' format a11

spool sess.txt

ttitle '**********< Program Global Area >**********'
ttitle '1. Current pga, uga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,
max(decode(c.name, 'session pga memory', trunc(value/1000)||'K', 0)) pga,
max(decode(c.name, 'session uga memory', trunc(value/1000)||'K', 0)) uga
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%'
group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '2. Sum of current pga, uga session memory'

select 'Current PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory', trunc(value/1000),0))||'K' pga_sum,
sum(decode(c.name, 'session uga memory', trunc(value/1000),0))||'K' uga_sum
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%';

ttitle '3. Max(peak) pga, pga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,
max(decode(c.name, 'session pga memory max', trunc(value/1000)||'K', 0)) pga_max,
max(decode(c.name, 'session uga memory max', trunc(value/1000)||'K', 0)) uga_max
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%'
group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '4. Sum of max(peak) pga, uga session memory'

select 'Max(peak) PGA, UGA session memory SUM:' as sum,
sum(decode(c.name, 'session pga memory max', trunc(value/1000), 0))||'K' pga_m_sum,
sum(decode(c.name, 'session uga memory max', trunc(value/1000), 0))||'K' uga_m_sum
from v$session a, v$sesstat b, v$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and c.name like 'session%';

spool off
exit




Example

none





Reference Documents

<Note:1070975.6>
Oracle8i Reference Release 8.1.5