다수의 사용자가 매번 디스크에서 직접 데이터를 추출한다고 가정해 보자. 이 경우 디스크I/O가 과도하게 발생해 데이터베이스의 성능을 보장할 수 없을 것이다.

디스크I/O 최소화 방편의 하나로 데이터베이스는 메모리에 자주 사용되는 데이터를 임시로 저장하고 재활용한다. 오라클의 데이터베이스도 그러하다. SGA(System Global Area) 안에 버퍼 영역을 두고 사용 빈도가 높은 데이터를 메모리 내에서 운용하며 재활용한다.

그런데 버퍼캐시가 메모리 내에 존재하다 보니 디스크 대비 데이터를 저장 가능한 용량이 상대적으로 작다. 대용량 데이터를 조회하는 쿼리나 대량의 DML 등이 자주 사용될 경우 버퍼캐시에 저장된 데이터들이 수시로 변경된다.

이는 데이터의 재활용 효율이 낮아지는 결과를 초래한다. 결국에는 디스크I/O를 수반하기 때문에 데이터베이스 성능 저하를 일으키고 만다. 그렇다면 이러한 버퍼캐시는 어떻게 최적화해야 할까? 이에 앞서 버퍼캐시의 구조를 먼저 살펴보자.

오라클 데이터베이스의 버퍼캐시는 Keep, Default, Recycle 총 세 영역의 다중 버퍼캐시를 제공한다.

• [그림1] 데이터베이스의 버퍼캐시
• 

• 다중 버퍼캐시 구조
• 1. Keep : 사용빈도가 높은 데이터를 위한 버퍼캐시
• 2. Default : 일반적인 버퍼캐시 영역
• 3. Recycle : 데이터 제거 우선순위가 높은 데이터가 저장되는 영역

• 다중 버퍼캐시 관련 매개변수
• 1. db_cache_size : 버퍼캐시 영역의 전체 크기
• 2. db_keep_cache_size : KEEP 영역의 크기
• 3. db_recycle_cache_size : RECYCLE 영역의 크기

오라클 데이터베이스의 버퍼캐시는 3개의 매개변수를 제공한다. 이 매개변수를 통해 메모리 내의 다중 버퍼캐시 영역을 생성하고 사용 가능하다. 이를 위해서는 세그먼트(테이블 또는 인덱스)를 생성할 때 Storage 구문에 별도로 옵션을 지정해야 한다.

참고로 BUFFER_POOL 항목의 기본값은 Default다. 물론 생성 이후에도 Alter 명령어를 통해 옵션을 변경할 수 있다([리스트 1] 참조).

• [리스트 1] ALTER TABLE 명령어 예

• ALTER TABLE table_name storage(buffer_pool keep); 
  ALTER TABLE table_name storage(buffer_pool default); 
  ALTER TABLE table_name storage(buffer_pool recycle); 
  

그렇다면 KEEP, RECYCLE 영역은 언제 사용되는 것일까? OLTP 환경에서 매우 빈번히 액세스가 일어나는 세그먼트가 있는 경우 KEEP 사용을 고려할 수 있다.

앞서 설명했듯 RECYCLE 영역의 경우 자주 사용되지 않으면서 재활용 가능성이 적은 데이터들이 저장되는데, 대용량 데이터일 경우 Direct path 방식으로 액세스하는 것도 고려할 수 있다. 마지막으로 배치성 쿼리라면 해당 운용시간을 피해서 수용하는 것이 바람직할 것이다.

사용빈도가 높은 데이터를 위한 버퍼캐시인 KEEP을 이용할 때에는 적절한 크기 선정에 특히 유의해야 한다. 당연한 말이지만 KEEP의 크기는 적어도 이 영역에 저장될 세그먼트보다는 커야한다.

만약 KEEP 영역의 용량이 부족하면 DEAFULT 영역에 데이터가 저장돼 SQL 성능에 영향을 끼치게 된다. 반면 지나치게 많은 용량을 할당할 경우 DEFAULT 영역의 크기가 적정보다 너무 작아 데이터베이스 성능이 오히려 더 나빠질 수 있다.

이러한 문제를 방지하고 최적의 크기를 선정하는 방안으로 BUFFER HIT Ratio나 KEEP BUFFER HIT Ratio의 모니터링이 주로 이용된다. 버퍼캐시 크기에 따른 데이터 적중률을 체크함으로써 적정 용량을 판단하는 방식이다.

만약 Hit Ratio 수치가 낮다면 쿼리 튜닝이나 버퍼캐시 크기 증가시켜야 한다. 또한 CR블록까지 고려한다면 KEEP 영역을 사용하는 세그먼트보다는 크기가 더 커야 할 것이다.

• [리스트 2] BUFFER HIT Ratio SQL

• SELECT 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), 1)||' %' 
         "BUFFER_CACHE_HIT_RATIO(%)"
    FROM V$SYSSTAT;
  

• [리스트 3] KEEP BUFFER HIT Ratio SQL

• SELECT name, block_size,
        (1-(physical_reads/ DECODE(db_block_gets+consistent_gets, 
        0, physical_reads, db_block_gets+consistent_gets)))*100 "KEEP_BUFFER_HIT_RATIO"
    FROM V$BUFFER_POOL_STATISTICS
   WHERE name = 'KEEP';
  

다중 버퍼캐시를 사용하면서 SGA를 ASMM(Automatic Shared Memory Management)로 관리하는 경우 한 가지 주의할 점이 있다. 바로 이 경우에는 KEEP, RECYCLE 영역이 ASMM 관리 대상에서 제외된다는 점이다.

ASMM에 의해 관리되지 않기 때문에 ASMM으로 관리하는 메모리 크기가 적정 크기보다 작을 수 있다. 이로 인해 메모리 할당이 유동적으로 이루어지지 않게 된다.

많은 이들이 멀티 버퍼캐시를 SQL 성능 향상의 한 방편이라고 여기고 있다. 그러나 멀티 버퍼캐시가 데이터베이스 성능을 반드시 향상시킨다는 보장은 그 어디에도 없다.

오히려 여건이 된다면 SQL 튜닝이나 어느 정도 버퍼캐시 영역을 늘리는 것이 더 좋은 효과를 발휘하는 경우도 있다. 그러므로 운영 시스템을 고려해 적절한 세그먼트를 선정하고, 각 영역에 적정 용량을 할당하는 게 가장 바람직하다고 할 수 있다.