이전 포스트: [Oracle] Oracle 저장 구조
다음 포스트: [Oracle] 사용자 관리
1. 9i 버전에서의 메모리 관리 기법(Dynamic SGA)
8i 이전 버전까지는 SGA와 PGA는 DBA가 수동으로 설정해서 관리했습니다. 어떤 설정 값이 변경되면 인스턴스를 재시작해야 했기에 아주 불편했습니다. 그러다가 9i 버전부터 alter system set 명령어를 이용해 파라미터 값을 인스턴스 재시작 없이 변경할 수 있는 Dynamic 기술이 등장했습니다.
주의할 점은 각 영역에 할당된 크기를 모두 합친 값이 SGA_MAX_SIZE에 설정된 값보다 커질 수 없다는 것입니다. 예를 들어, Shared Pool 사이즈 값을 100MB에서 200MB로 변경할 경우 다른 파라미터의 값을 줄인 후에 Shared Pool 값을 증가시켜야만 이 값이 적용됩니다.
한편, 그래뉼은 메모리를 할당할 때 쓰는 단위로 9i의 경우 SGA의 전체 크기가 128MB 이하이면 1Granule이 4MB이고, 128MB를 초과하면 16MB입니다. 즉, 메모리를 할당할 때 1MB씩 할당하는 것이 아니라 1Granule 단위로 할당한다는 뜻입니다. 10g부터는 128MB 기준이 아닌 1G 기준으로 변경되었습니다.
SGA 총 크기, 1Granule의 크기를 확인하는 방법은 아래와 같습니다.
-- SGA 총 크기
SQL> select name, bytes/1024/1024 MB
2 from v$sgainfo
3 where name='Maximum SGA Size';
-- 1Granule의 크기
SQL> select name, bytes/1024/1024 MB
2 from v$sgainfo
3 where name='Granule Size';
그래뉼 크기로 각 Background Process는 메모리를 할당 받아 사용합니다.
각 Background Process가 실제 사용하는 내역을 조회해 보겠습니다.
SQL> pmap `pgrep -f pmon`
WORKAREA_SIZE_POLICY 파라미터를 AUTO로 설정할 경우(기본값) PGA 전체 크기 범위(PGA_MAX_SIZE) 안에서 Oracle이 자동으로 PGA 값을 관리하게 됩니다.
- 직렬 작업: min(5% PGA_AGGREGATE_TARGET, 100MB)
- 병렬 작업: min(30% PGA+AGGREGATE_TARGET/DOP, _pga_max_size)
만약 파라미터를 Manual로 설정할 경우 이전 버전과 같이 PGA를 구성하는 각종 파라미터의 값을 수동으로 지정할 수 있습니다.
_pga_max_size: 1개의 Process가 사용할 수 있는 최대 PGA의 크기(Byte 단위, 기본값 200MB)
= _smm_max_size + _smm_px_max_size
_smm_max_size: 1개의 Process가 직렬처리 시 사용할 수 있는 최대 PGA의 값(KB 단위)
_smm_px_max_size: 1개의 Process가 병렬처리 시 사용할 수 있는 최대 PGA의 값(KB 단위)
만약 대량의 Sort나 Hash가 발생하는 작업을 할 경우 WORKAREA_SIZE_POLICY의 값을 Manual로 설정한 후 SORT_AREA_SIZE나 HASH_AREA_SIZE 값을 수동으로 직접 지정해 주는 것이 성능 향상에 큰 도움이 됩니다. 역시나 이 값들을 모두 합친 값은 전체 PGA 할당량인 PGA_AGGREGATE_TARGET 값을 초과할 수 없기 때문에 먼저 PGA_AGGREGATE_TARGET의 값을 크게 설정해야 성능 향상의 효과를 얻을 수 있습니다. PGA 용량이 부족할 경우 ORA-04030 에러가 발생합니다.
현재 작동중인 Parallel Query의 busy, idle 상태 및 server start, shutdown 상태를 조회할 수 있습니다.
SQL> select * from v$pg_sysstat where statistic='Servers Busy';
2. 10g 버전에서의 메모리 관리 기법(ASMM)
10g 부터는 SGA의 구성 요소들의 크기를 Oracle이 자동으로 관리해 주는 기능인 ASMM(Automatic Shared Memory Management)가 추가되었습니다. Redo Log Buffer를 제외한 나머지 구성 요소들의 크기를 상황에 맞게 Oracle이 자동으로 변경합니다. 이를 담당하는 Background Process는 MMAN입니다.
MMAN은 SGA의 Workload(MMON가 sysaux Tablespace에 저장해 두는 통계정보와 각종 Advisor들이 실시간으로 보내주는 정보)를 보고 메모리의 부족함과 충분함을 판단하는 역할을 합니다. MMAN의 분석을 토대로 ASMM이 메모리가 충분한 영역에서 메모리가 부족한 영역으로 메모리를 재배치합니다. MMAN은 SGA_TARGET에 설정된 용량의 범위 안에서 메모리를 할당하고 관리합니다.
SGA_TARGET 값은 SGA_MAX_SIZE를 초과할 수 없습니다. SGA_TARGET 값 내에서 SGA의 각 영역들이 자동 튜닝됩니다. 예를 들어, SGA_TARGET=2G로 설정한 후 SHARED_POOL_SIZE를 100M으로 설정했다면 100M가 최소값으로 인식됩니다.
자동 튜닝되는 파라미터: DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, STREAMS_POOL_SIZE
수동 튜닝되는 파라미터: DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, SGA_TARGET
SQL> show parameter sga_target;
SGA_TARGET이 0이라는 뜻은 자동 튜닝이 되지 않는다는 의미입니다. 값을 할당해주면서 자동 튜닝 기능을 활성화합니다.
SQL> alter system set sga_target=100M;
SQL> show parameter sga_target;
자동으로 할당된 세부 내역을 아래와 같이 조회해서 확인 가능합니다.
SQL> select component, current_size/1024/1024 curr_MB, min_size/1024/1024 min_MB, user_specified_size/1024/1024 user_MB
2 from v$sga_dynamic_components;
만약 SGA_TARGET 값을 SGA_MAX_SIZE - log buffer size를 초과해서 할당하면 어떻게 될까요?
SQL> alter system set sga_target=370M;
SGA_MAX_SIZE를 보는 방법은 아래와 같습니다.
SQL> show parameter sga_max_size;
남아있는 용량을 조회하는 방법은 아래와 같습니다.
SQL> select current_size/1024/1024 MB
2 from v$sga_dynamic_free_memory;
테스트
값이 정말 자동으로 튜닝되는지 테스트 해보겠습니다.
SQL> create table assm_test
2 as
3 select * from dba_tables;
SQL> insert into assm_test
2 select * from dba_tables;
SQL> /
SQL> /
SQL> /
SQL> /
SQL> /
SQL> /
SQL> /
SQL> select component, current_size/1024/1024 curr_MB, min_size/1024/1024 min_MB, user_specified_size/1024/1024 user_MB
2 from v$sga_dynamic_components;
Default Buffer Cache Size가 자동으로 변경된 것을 확인할 수 있습니다.
변경 내역을 조회하려면 아래 명령을 수행합니다.
SQL> select component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", TARGET_SIZE/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", status
2 from v$sga_resize_ops;
자동 튜닝으로 설정한 후에도 수동으로 값을 변경할 수 있습니다.
SQL> alter system set shared_pool_size=200M;
SQL> select component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", TARGET_SIZE/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", status
2 from v$sga_resize_ops;
shared pool size가 변경되면서 default buffer cache size가 축소된 것이 확인됩니다.
3. 11g 버전에서 메모리 관리 기법(AMM)
11g 버전에서는 SGA 뿐만 아니라 PGA까지 한꺼번에 자동으로 관리하는 기술인 AMM(Automatic Memory Management)이 등장합니다.
AMM 기능을 사용하기 위해서는 2가지 파라미터가 있습니다.
-
MEMORY_TARGET
: AMM 기능으로 관리할 메모리의 총량을 지정할 수 있습니다. 기본값은0이며 사용하지 않는다는 의미입니다. 이 값을 0보다 크게 지정하면 그 범위 내에서SGA와 PGA모두를 관리합니다. -
MEMORY_MAX_TARGET
:MEMORY_TARGET값이 최대로 증가될 값을 의미합니다. 기본값은MEMORY_TARGET와 동일합니다.
AMM 기능을 사용하게 되면 SGA_TARGET, PGA_AGGREGATE_TARGET 값을 지정할 필요가 없습니다. 만약 지정한다면 그 값을 최소값으로 인식하게 됩니다. 원활한 AMM 기능 사용을 위해 SGA_TARGET, PGA_AGGREGATE_TARGET 값은 0으로 설정하길 권장합니다.
AMM 기능을 사용하기 위해서는 물리적 메모리의 공간이 충분히 있어야 합니다. 만약 공간이 부족할 경우 에러가 발생합니다.
SQL> startup;
ORA-00845: MEMORY_TARGET not supported on this system
리눅스에서 OS 메모리를 확인하는 방법은 아래와 같습니다.
# df -h /dev/shm
기본적으로 MEMORY_TARGET = MEMORY_MAX_TARGET이어야 합니다. 그렇지 않으면 에러가 발생합니다.
-- 에러 발생
SQL> alter system set memory_target=300M;
-- memory_max_target 값 확인
SQL> show parameter memory_max_target;
-- memory_max_target 값을 초과하면 에러 발생
SQL> alter system set memory_target=450M;
SQL> alter system set memory_target=404M;
System altered.
적절한 MEMORY_TARGET 값은 SGA와 PGA를 더한 값입니다.
SQL> select name, value/1024/1024 MB
2 from v$parameter
3 where name in ('pga_aggregate_target', 'sga_target');
SQL> select 'maximum PGA allocated' name, value/1024/1024 value
2 from v$pgastat
3 where name = 'maximum PGA allocated';
-- 두 값의 합계
SQL> select round((s.value + GREATEST(p.value, mp.value))/1024/1024) memory_target
2 from (select value from v$parameter where name='sga_target') s,
3 (select value from v$parameter where name='pga_aggregate_target') p,
4 (select value from v$parameter where name='maximum PGA allocated') mp;
AMM의 상태를 조회하기 위한 view들은 v$memory_dynamic_components, v$memory_current_resize_ops, v$memory_resize_ops가 있습니다.
v$memory_dynamic_components를 통해서 각 영역별 실제 메모리 사용 내역을 조회할 수 있습니다.
SQL> select component, current_size/1024/1024 CURR_MB, min_size/1024/1024 MIN_MB, max_size/1024/1024 MAX_MB
2 from v$memory_dynamic_components
3 where current_size != 0;
v$memory_current_resize_ops, v$memory_resize_ops view를 조회하면 변경 내역들을 자세하게 조회할 수 있습니다.
SQL> select * from v$memory_current_resize_ops;
MEMORY_TARGET 값이 적절한 지 확인할 수 있는 방법은 아래와 같습니다.
SQL> select * from v$memory_target_advice order by memory_size;
4. 요약
9i: Dynamic SGA10g: ASMM -> SGA 자동 튜닝11g: AMM -> SGA + PGA 자동 튜닝
참고
- <오라클 관리 실무> - 서진수
