[Oracle] 오라클 메모리 사용량 관련

위의 키워드로 인터넷을 뒤지다 유용한(?) 내용을 발견해서 정리하여 둔다.
나중에 혹시나 도움이 될까봐... 근데 써먹을 정도의 실력이 아직은 안되는갑다...
조회는 되는데 이걸 어찌 활용해야 하는지 잘 모르겠다... 헐''

작성날짜 : 2004-04-26

PROCESS 의 MEMORY 사용량
========================

PURPOSE

 

--------------------------------------------------------------------------------
Process의 Memory 사용량의 추정치를 구해본다.

Explanation

--------------------------------------------------------------------------------

오라클 환경에서 process가 사용하는 physical memory를 SVR4 계열에서
구하기란 거의 불가능하다.
다만 "sar -r"을 사용하여 추측하는 것이 현재까지 알려진 유일한 방법이다.

Example

 

--------------------------------------------------------------------------------

process의 memory 사용량

1) "ps -efly"를 사용

RSS -> resident set size in kilobytes, RSS는 process가 수행되면서
page fault가 발생한 것을 기초로 작성됨.
그러나 page fault가 발생한다 하더라도 이미 다른 procee가
사용하는 영역이면(text, shared memory)
reference count만 증가시키고 실제 memory는 공유되므로
오라클인 경우 계산하기 불가능함.
SZ -> the virtual address space size of the process in kilobytes,
SZ는 process의 virtual address space

또는 ps -o user, pid, osz,vsz,rss,args -el|grep oracle 사용

2) "sar -r 1 100"을 사용

1의 수행과 동시에 sar -r 1 100을 사용한 결과

13:30:19 36959 33781
13:30:20 36675 33403
13:30:21 36669 33397

freemem의 변화치는 290 * 4 = 1160K 이다.

Reference Documents

 

--------------------------------------------------------------------------------
none
--------------------------------------------------------------------------------
 
아래는 제가 이전에 OTN 포럼에 올렸던 자료인데 아래를 수행해서
찾아보세요.

1. 우선 아래 쿼리로 어느 부분에서 메모리를 많이 사용하는지 봅니다.

session 별 pga, uga memory 사용량 산출하는 스크립트
==================================================

특정 session에서 memory를 과도하게 사용하고 있을 때 현재 어떤 프로그램을
수행하고 있는 session에서 memory를 얼마나 사용하고 있는지 확인할 수 있는
스크립트이다.
이 스크립트는 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
set pause off

2. 혹시 하나의 세션에서 많은 cursor를 open해놓고 close하지는 않는지
봅니다.
current cursor가 혹시 몇백몇천을 가지지는 않는지 확인

DOC

file: usercurs.sql

The following script lists user session cursor usage.
The opened cursors figure should ideally be less than
the INIT.ORA OPEN_CURSORS parameter.
You need about 250 bytes of shared pool memory per user for
each cursor that the user has open.

#

ttitle 'Per Session Current Cursor Usage '

col username format a15
column "Recursive Calls" format 999,999,999;
column "Opened Cursors" format 99,999;
column "Current Cursors" format 99,999;

SELECT ss.username username, se.sid SID_NUM , p.spid SPID_NUM,
sum(decode(name,'recursive calls',value)) "Recursive Calls",
sum(decode(name,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(name,'opened cursors current',value)) "Current Cursors"
FROM v$session ss, v$sesstat se, v$statname sn, v$process p
WHERE se.statistic# = sn.statistic#
AND ( name like '%opened cursors current%'
OR name like '%recursive calls%'
OR name like '%opened cursors cumulative%')
AND se.sid = ss.sid
and ss.paddr=p.addr
AND ss.username is not null
GROUP BY ss.username , se.sid , p.spid
order by "Current Cursors" asc
/
clear columns
ttitle off

3. pga영역에는 아래와 같은 구성요소가 있습니다.
- session의 variable, array
- cursor 실행 메모리
- persistent 영역
- run-time 영역
특히 run-time영역이 SQL수행을 위한 정보를 가집니다. SQL문장의
복잡도에 따라서 , 처리한 데이터 양에 따라서 이 영역의 크기가 달라집니다.
이 영역은 SQL문장이 수행이 끝나면 OS로 메모리가 반환됩니다.

그렇다면 SQLAREA에서 메모리를 가장 많이 사용하는 top 10실행영역이
어떤 것인지 확인할 수 있습니다.

 

--------------------------------------------------------------------------------
V$SQL_WORKAREA 뷰를 통해서 다음과 같이 SQL 실행 메모리를 가장 많이 필요로 하는
top 10 실행 영역을 구할 수 있다.

SELECT *
FROM ( SELECT workarea_address, operation_type, policy, estimated_optimal_size
FROM v$sql_workarea
ORDER BY estimated_optimal_size DESC )
WHERE ROWNUM <= 10;
--------------------------------------------------------------------------------
 
4. V$SQL_WORKAREA_ACTIVE, V$SQL_WORKAREA, V$SQL 뷰를 이용하여
현재 시스템에서 할당된 실행 영역 중 top 10을 찾습니다.

SQL> SELECT c.sql_text, w.operation_type, top_ten.wasize
FROM ( SELECT *
FROM ( SELECT workarea_address, actual_mem_used wasize
FROM v$sql_workarea_active
ORDER BY actual_mem_used desc)
WHERE ROWNUM <= 10 ) top_ten,
v$sql_workarea w, v$sql c
WHERE w.workarea_address = top_ten.workarea_address
AND c.address = w.address
AND c.child_number = w.child_number
AND c.hash_value = w.hash_value;