[AWS] EC2 설치형 ORACLE DB - EC2 스펙 변경 시 DB 검토 사항

EC2 설치형 ORACLE DB - EC2 스펙 변경 시 DB 검토 사항 

 

환경 : EC2에 설치형 Oracle DB 

스펙 : 현재 m4.xlarge (4cpu 16GB)  > 검토 m4.large (2cpu 8GB)

 

1. 현재 메모리 구성 확인

- 현재 SGA 구성 확인 쿼리

select
    name   "Area",
    max(value)  "Size"
from  (
    select  1  ordering,
        'Block Size'  name,
        lpad(to_char(value/1024)||'K',20,' ') value
    from  v$parameter
    where  name='db_block_size'
    union all
    select  2  ordering,
        'Buffer Cache'  name,
        lpad(to_char(sum(decode(name,'db_block_buffers',value*db_block_size,value))/1048576,'99,999,990')||'M',20,' ')  value
    from  v$parameter, (select value db_block_size from v$parameter where name='db_block_size') v
    where  name in ('db_block_buffers','db_cache_size','db_2k_cache_size','db_4k_cache_size',
                    'db_8k_cache_size','db_16k_cache_size','db_32k_cache_size','db_keep_cache_size','db_recycle_cache_size')
    union all
    select  3  ordering,
        'Shared Pool'  name,
        lpad(decode(to_char(sign(instr(value,'M'))||sign(instr(value,'K'))),'00',to_char(to_number(value)/1048576,'99,999,990')||'M',value),20,' ') value
    from  v$parameter
    where  name='shared_pool_size'
    union all
    select  4  ordering,
        'Log Buffer'  name,
        lpad(to_char(value/1048576,'999,990.99')||'M',20,' ')  value
    from  v$parameter
    where  name='log_buffer'
    union all
    select  5  ordering,
        'Java Pool'  name,
        lpad(decode(to_char(sign(instr(value,'M'))||sign(instr(value,'K'))),'00',to_char(to_number(value)/1048576,'99,999,990')||'M',value),20,' ') value
    from  v$parameter
    where  name='java_pool_size'
    union all
    select  6  ordering,
        'Large Pool'  name,
        lpad(decode(to_char(sign(instr(value,'M'))||sign(instr(value,'K'))),'00',to_char(to_number(value)/1048576,'99,999,990')||'M',value),20,' ') value
    from  v$parameter
    where  name='large_pool_size'
    union all
    select  7  ordering,
        'SGA Total'    name,
        lpad(to_char(sum(value)/1048576,'99,999,990')||'M',20,' ')  value
    from  v$sga
    union all
    select  8  ordering,
        'SGA Max'    name,
        lpad(decode(to_char(sign(instr(value,'M'))||sign(instr(value,'K'))),'00',to_char(to_number(value)/1048576,'99,999,990')||'M',value),20,' ') value
    from  v$parameter
    where  name in ('sga_max_size')
    union all
    select  9  ordering,
        'SGA Target'    name,
        lpad(decode(to_char(sign(instr(value,'M'))||sign(instr(value,'K'))),'00',to_char(to_number(value)/1048576,'99,999,990')||'M',value),20,' ') value
    from  v$parameter
    where  name in ('sga_target')
    )
group by ordering,name
order by ordering
;

 

현재 SGA 크기 : 5,616Mb

 

2. 서버 전체 메모리 대비 DB 메모리 검토

서버 전체 메모리 크기 대비 30 - 40% 를 DB SGA 영역에 할당하는 것을 권고함 

 

- 현재 서버 전체 메모리 대비 DB 메모리

5,616Mb (현재 DB SGA 영역 크기) / 16Gb (EC2 서버 메모리 크기) * 100 = 34.3%

 

- EC2 스펙 변경 시 권고 DB 메모리 크기

8Gb 의 40% = 3,276Mb

EC2 스펙 변경 시 SGA 크기를 3,276Mb 수준으로 축소해야한다.

 

 

3. SGA Memory Hit 율 확인

 

Memory Hit 율 80% 이상 수준이어야 양호

 

- SGA Memory Hit율 확인 쿼리

select name,
       value
from   (SELECT 'Buffer Cache Hit Ratio' as name,
               ROUND(((1-(SUM(DECODE(name, 'physical reads', value, 0)) / (SUM(DECODE(name, 'db block gets', value, 0))
               + (SUM(DECODE(name, 'consistent gets', value, 0))))))*100), 2) value
        FROM   V$SYSSTAT
        union all
SELECT 'Library Cache Hit Ratio' as name,
               round((1 - SUM (reloads)/SUM(pins))*100, 2) value
        From   V$LIBRARYCACHE
        union all
SELECT 'Data Dictionary Cache Hit Ratio' as name,
               round((1 - SUM(getmisses)/SUM(gets))*100, 2) value
        FROM   V$ROWCACHE );

 

현재 buffer cache hit 율이 46%로 낮은 수준임.

 

 

4. DB 메모리영역 크기 변경 시 성능 예상

Oracle에서 제공하는 Memory Advisory 기능으로 메모리 영역 크기 변경 시 예상 성능지표 확인

* Memory Advisory 에서 제공하는 값은 예측 값으로 절대적인 것이 아님. 참고용으로 사용.

 

- 각 메모리 영역 별 Memory Advisory 확인 쿼리

--[SGA_Target_Advisory]
  select SGA_SIZE "SGA Target Size(M)", SGA_SIZE_FACTOR "Size Factor", 
  ESTD_DB_TIME "Est DB Time (s)", ESTD_PHYSICAL_READS "Est Physical Reads"
  from v$sga_target_advice;
  
--[Buffer_Pool_Advisory]
  select SIZE_FOR_ESTIMATE "Buffer Cache Size (M)", SIZE_FACTOR "Size Factor", 
  BUFFERS_FOR_ESTIMATE "Est Buffers", ESTD_PHYSICAL_READS "Est phy reads"
  from V$DB_CACHE_ADVICE;
  
--[Shared_Pool_Advisory]
  select SHARED_POOL_SIZE_FOR_ESTIMATE "Shared Pool Size(M)", 
  SHARED_POOL_SIZE_FACTOR "Size Factor", 
  ESTD_LC_TIME_SAVED "Est LC Time Saved (s)", 
  ESTD_LC_MEMORY_OBJECT_HITS "Est LC Mem Obj Hits (K)"
  from v$shared_pool_advice;

--[PGA_Memory_Advisory]
  select round(pga_target_for_estimate/1024/1024) "PGA Target size(MB)",
  pga_target_factor "Size Factor",
  estd_pga_cache_hit_percentage "Estd PGA Cache Hit %",
  estd_overalloc_count "Estd PGA Overalloc Count"
  from v$pga_target_advice;

--[Java_Pool_Advisory]
  select JAVA_POOL_SIZE_FOR_ESTIMATE, JAVA_POOL_SIZE_FACTOR "Factor", ESTD_LC_LOAD_TIME, 
  ESTD_LC_TIME_SAVED,  ESTD_LC_SIZE, ESTD_LC_MEMORY_OBJECTS, ESTD_LC_MEMORY_OBJECT_HITS
  from v$java_pool_advice;
  
--[Streams_Pool_Advisory]
  select  STREAMS_POOL_SIZE_FOR_ESTIMATE, STREAMS_POOL_SIZE_FACTOR "Factor", 
  ESTD_SPILL_COUNT,  ESTD_SPILL_TIME, ESTD_UNSPILL_COUNT, ESTD_UNSPILL_TIME
  from v$streams_pool_advice;

 

- Buffer pool Advisory 

estimate phycal reads 지표가 현재 크기보다 1.2배 정도 되는 부분에서 급상승한다.

해당 영역이 현재도 넉넉하지 않은 상황으로 해석됨

 

- Shared pool Advisory

shared pool 영역 축소하여도 성능에 이슈 없을 것으로 예측됨

 

 

5. 현재 자원 사용량 확인

CPU 추이 확인 : 특이사항 없음

 

6. 사용자 인터뷰

개발팀 운영팀 등 담당자 인터뷰

 

7. 스펙 변경 가능 여부 판단

1-6 을 바탕으로 스펙 변경 가능 여부 판단

	검토 사항	결과
1	현재 메모리 구성 확인
2	서버 전체 메모리 대비 DB 메모리 검토	8Gb 의 40% = 3,276Mb
3	SGA Memory Hit 율 확인	buffer cache hit 율이 46%로 낮은 수준임
4	DB 메모리영역 크기 변경 시 성능 예상	buffer pool 축소하지 않는 것이 좋을 것으로 보임 shared pool 축소 가능 ...
5	현재 자원 사용량 확인	특이사항 없음
6	사용자 인터뷰	개발팀에서 쿼리 성능이 느리다고 함

 

검토 결과 : 스펙다운 하지 않는 것이 좋을 것 같다고 판단됨

 

8. DB 메모리 구성 변경안 작성

스펙다운 결정 시 1-6 을 바탕으로 DB 메모리 구성 변경안 작성

 

- 구성안 예시 1.

 

- 구성안 예시 2.

 

9. DB 메모리 변경 작업

10. EC2 스펙 변경 작업