SGA에 대한 이해
==============

OS의 memory와 관련되어 ORACLE에서 말하는 SGA란 ORACLE instance의 data,
control 정보를 보관하고 있는 shared memory 구조를 말한다.
shared라는 의미는 instance에 연결된 여러 user들이 서로 공유한다는 뜻이므로
메모리 영역 중 여러 유저들이 서로 공유해야 하는 부분을 SGA 영역이라 보면
무난하다. 이런 SGA 영역도 여러 부분으로 나누어서 얘기할 수 있는데 대표적으로
database buffer cache, redo log buffer, shared pool 부분으로 나눌 수 있다.

이 곳에서는 SGA 각 부분을 control하는 parameter를 알아보며 현재 instance에
할당된 shared memory 영역 중 얼마나 사용되고 있는가를 보는 방법을 소개한다.


1. SGA의 크기와 크기에 영향을 주는 parameter

SGA의 크기는 instance가 기동될 때 얼마 만큼이 메모리에 할당되었는지 나타난다.
또한 V$SGA라는 dynamic dictionary에서도 조회가 가능하다.
이 때 크게 4부분으로 나누어져 조회되는데 각 부분은 다음과 같다.

1) Fixed size

이 부분은 백그라운드 프로세스가 access하는 데 필요한 일반적인 정보를
포함하고 있는 부분으로서 user data는 없으며 parameter로 크게 또는 작게 지정할
수 없다. 따라서 항시 instance 내에서 일정한 크기를 가지며 버젼별, O/S 별로
약간의 차이는 있다.

2) Variable size

이 size는 parameter file(initSID.ora)의 SHARED_POOL_SIZE에서 지정한 크기와
각종 파라미터로 지정한 값의 합으로 결정된다.
SHARED_POOL_SIZE는 byte 단위로 지정하며 OS의 shared memory segment보다는
작아야 한다.
initSID.ora 화일에는 instance와 관련된 여러 parameter가 지정되어 있는데,
이곳의 parameter의 지정 값에 따라서 SGA의 영역에 일정한 부분을 차지한다.
따라서 SGA의 크기에 영향을 주는 요소는 단순히 SHARED_POOL_SIZE 이외에
각종 parameter에 의해 점유되는 부분을 고려해야 한다. 일반적으로 각 parameter
값을 크게 할 수록 메모리 사용은 일정한 비율로 늘어나며 다음은 몇가지 예이다.

* DB_FILES - 10 증가 시 약 6K 소모
* DML_LOCKS - 100 증가 시 9.7K 소모
* PROCESSES - 10 증가 시 19.5K 소모
* SEQUENCE_CACHE_ENTRIES - 10 증가 시 약 1.17K 증가
* ROW_CACHE_ENQUEUES - 100 증가 시 약 3.5K 증가
* SESSIONS - 10 증가 시 약 5.3K 증가
:
:
현재 각 parameter에 의해 점유된 SGA 내의 점유된 메모리 영역의 크기는
V$SGASTAT에서 조회하여 볼 수 있다.
(select * from v$sgastat;)

3) Database Buffer Cache

SGA에서 disk의 data가 저장되는 곳으로서 performance에 큰 영향을 준다.
이곳의 size가 작으므로 발생할 수 있는 현상은 빈번한 disk I/O이다.
크기는 DB_BLOCK_BUFFERS로 지정하며 buffer의 갯수를 지정한다.
byte 산정은 DB_BLOCK_BUFFERS * DB_BLOCK_SIZE 로 산출된다.

4) Log Buffers

이것은 redo log 용도로 사용될 메모리 내의 log buffer size를 말한다.
크기는 byte 단위로 LOG_BUFFERS 로 지정한다.


2. SGA 사용 상태 확인.

다음은 SGA 사용 상태를 점검하는 방법에 대해 알아보자.
여러가지 dynamic dictionary에서 조회가 가능하다.
적당한 이름으로(test.sql) file을 열고 다음의 PL/SQL program을 작성한다.

declare
object_mem number;
shared_sql number;
cursor_mem number;
mts_mem number;
used_pool_size number;
free_mem number;
pool_size varchar2(512);
begin

-- Shared pool에 적재되어 있는 package, views(stored objects)의 크기 측정.
select sum(sharable_mem) into object_mem from v$db_object_cache;

-- Parsing되어 적재되어 있는 SQL문의 크기 측정
select sum(sharable_mem) into shared_sql from v$sqlarea;

-- open된 cursor가 차지하고 있는 memory 크기 측정(cursor 당 250byte)
select sum(250*users_opening) into cursor_mem from v$sqlarea;

-- 위 값은 user 당 필요한 open cursor를 구하여 user 수를 곱하여 계산할
수도 있다.
-- select (250 * value) bytes_per_user from v$sesstat s, v$statname n
-- where s.statistic# = n.statistic#
-- and n.name = 'opened cursors current'
-- and s.sid = 25; -- 25는 user의 session ID임.

-- MTS로 연결된 user의 memory 필요량을 측정함.
-- MTS가 아니면 필요 없음.
select sum(value) into mts_mem from v$sesstat s, v$statname n
where s.statistic#=n.statistic#
and n.name='session uga memory max';

-- SGA의 free memory size를 측정한다.
select bytes into free_mem from v$sgastat
where name = 'free memory';

--non-MTS인 경우 필요한 shared pool size를 계산. (overhead 30% 로 감안)
used_pool_size := round(1.3*(object_mem + shared_sql + cursor_mem));

--MTS인 경우 위에서 계산된 부분에 MTS memory를 감안해 줌.
--used_pool_size := round(1.3*(object_mem + shared_sql +
cursor_mem+mts_mem));

-- initSID.ora에 지정한 shared pool size 값을 측정함.
select value into pool_size from v$parameter where name=
'shared_pool_size';

-- Display results
dbms_output.put_line ('Object mem : '||to_char(object_mem)||' bytes');
dbms_output.put_line ('Shared sql : '||to_char(shared_sql)||' bytes');
dbms_output.put_line ('Cursors : '||to_char(cursor_mem)||' bytes');
dbms_output.put_line ('MTS session: '||to_char(mts_mem) ||' bytes');
dbms_output.put_line ('Free memory: '||to_char(free_mem) ||' bytes '
||'('|| to_char(round(free_mem/1024/1024,2))
|| 'M)');
dbms_output.put_line ('Shared pool utilization (total): '||
to_char(used_pool_size) || ' bytes ' || '(' ||
to_char(round(used_pool_size/1024/1024,2)) || 'M)');
dbms_output.put_line ('Shared pool allocation (actual):'||pool_size||'
bytes ' || '(' || to_char(round(pool_size/1024/1024,2)) || 'M)');
dbms_output.put_line ('Percentage Utilized: '||to_char
(round(used_pool_size/pool_size*100)) || '%');
end;
/



작성된 PL/SQL program을 다음과 같이 수행시킨다.

krrcsun# sqlplus system/manager
SQL*Plus: Release 3.3.2.0.2 - Production on Tue Apr 29 09:43:51 1997
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.

Connected to:
Oracle7 Server Release 7.2.2.3.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.2.2.3.0 - Production

SQL> set serveroutput on
SQL> @test
Object mem : 848700 bytes
Shared sql : 4318605 bytes
Cursors : 334500 bytes
MTS session: 5745056 bytes
Free memory: 3644464 bytes (3.48M)
Shared pool utilization (total): 7152347 bytes (6.82M)
Shared pool allocation (actual): 15000000bytes (14.31M)
Percentage Utilized: 48%

PL/SQL procedure successfully completed.