실전오라클, 튜닝, 모델링, SQL, PLSQL
인기강좌 --> SQL초보에서전문가까지, 오라클초보에서전문가까지, Unix&Pro*C
www.oraclejava3.co.kr 오라클 강좌에서 확인하세요.
BULLETIN CATEGORY
BULLETIN TOPIC : RDBMS
: SGA에 대한 이해
--------------------------------------------------------------------------------
OS의 메모리와 관련되어 ORACLE에서 말하는 SGA란 ORACLE 인스턴스의 data, control정보를 보관하고 있는 shared memory 구조를 말한다. shared라는 의미는 인스턴스에 연결된 여러 user들이 서로 공유한다는 뜻이므로 메모리 영역중 여러 유저들이 서로 공유 해야 하는 부분을 SGA영역이라 보면 무난하다. 이런 SGA영역도 여러 부분으로 나누어서 얘기 할 수 있는데 대표적으로 database buffer cache, redo log buffer, shared pool부분으로 나눌 수 있다.
공유풀(shared pool)의 크기는 파라미터 파일의 SHARED_POOL_SIZE(바이트단위) 로 결정한다. 특이한 것은 오라클9I 버전에 SGA의 크기를 다이나믹하게 변경할 수 있게 된 것이다. 즉, SGA_MAX_SIZE 파라미터가 생겼습니다. 이 범위 안에서 다이나믹하게 변경될 수 있는 것입니다. 공유풀의 크기는 그래뉼-GRANULE(가상메모리의 연속된 공간,dynamic SGA 모델에서 할당할 수 있는 최소 단위이다.) 크기의 정수이고 공유풀의 크기는 V$SQLAREA를 살펴봄으로써 알 수 있다.
이번엔 버퍼 캐시에 대해서 알아보도록 하겠는데 오라클9I 에서 새로운 기술이 도입된 것이 SGA 크기를 다이나믹하게 조절할 수 있다는 것이다. 그 이전의 버전은 SGA의 크기를 변경하기 위해서 오라클을 ShutDown 시킨다음 초기화 파라미터 파일인 init.ora 파일의 파라미터의 값을 고쳐주고, 다시 오라클의 기동시켰다. 이러한 것은 가용성에 영향을 주는 것이었고 오라클 9i는 이러한 점을 감안하여 새로운 메커니즘을 도입한 것이다.
중요한 것은 DBA가 데이터베이스를 시작하기 전에 SGA의 크기를 데이터의 양과 사용패턴등에 대한 정확한 조사를 한후 이에 맞게 SGA의 최대 크기를 정해야 한다는 것이다. 이 최대 크기는 이전에 나온 SGA_MAX_SIZE의 값을 설정 한다.
또한 SGA의 값을 다이나믹하게 변경하기 위해서 오라클은 Granule이라는 새로운 유닛을 할당한다. Granule의 크기를 결정하기 위해서 SGA의 전체 크기를 확인한고 만약 SGA의 크기가 128MB 보다 작다면 각각의 Granule 크기는 4MB 이어야 하며 128MB 보다 크다면 Granule의 크기는 기본값으로 16MB까지 가질 수 있는 것이다.
버퍼 캐시는 Granule에 따라 커지거나 작아질 수 있다. SGA의 Buffer Cache, Shared Pool, Large Pool 등의 파라미터는 이 granule 단위로 늘어나거나 줄어들 수 있다. (현재 dynamic SGA를 사용할 수 있는 SGA 관련 파라미터는 Buffer Cache, Shared Pool, Large Pool 세 가지이다.)
이곳에서는 SGA 각 부분을 control하는 parameter를 알아보며 현재 인스턴스에 할당된 shared memory 영역 중 얼마나 사용 되고 있는 가를 보는 방법을 소개한다.
1.SGA의 크기와 크기에 영향을 주는 PARAMETER
SGA의 크기는 인스턴스가 기동 될 때 얼마 만큼이 메모리에 할당 되었는지 나타난다. 또한 v$sga라는 dynamic dictionary에서도 조회가 가능하다. 이 때 크게 4부분으로 나누어져 조회되는 데 각 부분은 다음과 같다.
1)fixed size
이 부분은 백그라운드 프로세스가 access하는데 필요한 일반적인 정보를 포함하고 있는 부분으로서 user data는 없으며 parameter로 크게 또는 작게 지정 할 수 없다.따라서 항시 인스턴스 내에서 일정한 크기를 갖으며 버젼별, OS 별로 약간의 차이는 있다.
2)variable size
이 size는 parameter file(init<SID>.ora)의 shared_pool_size에서 지정한 크기와 각종 파라미터로 지정한 값의 합으로 결정된다. shared_pool_size는 byte단위로 지정하며 OS의 shared memory segment보다는 작아야 한다. Init<SID>.ora에는 인스턴스와 관련된 여러 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가 작으므로 발생 할 수 있는 현상은 빈번한 디스크 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) 화일을 열고 다음의 PL/SQL 프로그램을 작성한다.
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가 차지하고 있는 메모리 크기 측정(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의 메모리 필요량을 측정함.
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 메모리를 감안해줌.
--used_pool_size := round(1.3 * (object_mem + shared_sql + cursor_mem + mts_mem));
--init<SID>.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 (actual): '|| to_char(used_pool_size) || ' bytes ' || '(' || to_char(round(used_pool_size / 1024 / 1024,2)) || 'M)');
dbms_output.put_line ('Shared pool allocation (total): '|| 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;
/
작성된 프로그램을 다음과 같이 수행시킨다.
$sqlplus system/manager
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%