메모리 튜닝(Memory Tuning)은 시스템의 여유 메모리를 Oracle의 다양한 메모리 구성 요소에 분배하거나 재할당하는 작업입니다. 주로 Oracle 쿼리 성능 최적화에 활용되며, 오늘날 많은 기업에게 데이터베이스 성능은 핵심 과제로 떠오르고 있습니다. 이 글에서는 데이터베이스 메모리를 효과적으로 튜닝하여 고성능 데이터베이스 환경을 구축하는 방법을 설명합니다.
메모리 튜닝에는 여러 가지 접근 방식이 있습니다. OS 레벨 프로세스 튜닝, CPU 튜닝, RAM 튜닝, 데이터베이스 튜닝 등이 대표적입니다. 이 글에서는 그중에서도 데이터베이스 메모리 튜닝에 대해 집중적으로 다루겠습니다.
데이터베이스 메모리 튜닝이란?
데이터베이스 메모리 튜닝은 곧 SGA(System Global Area) 튜닝이라고 할 수 있습니다. SGA 튜닝과 관련된 주요 구성 요소는 다음과 같습니다.
- Keep Pool
- Default Pool, Recycle Pool, Stream Pool
- DB 버퍼 캐시(DB Buffer Cache): 프리 버퍼, 수정된 버퍼, 고정된 버퍼
- 공유 풀(Shared Pool)
Keep Pool
매일 사용되는 패키지가 있다면, 성능 향상을 위해 해당 패키지를 SGA에 상주시키는 것이 좋습니다. Keep Pool을 활용하면 자주 사용하는 패키지를 메모리에 지속적으로 유지할 수 있습니다.
단, 인스턴스가 종료되면 SGA가 RAM에서 해제되므로 Keep Pool에 보관했던 패키지도 함께 사라집니다. 데이터베이스를 재시작한 후 패키지를 다시 실행하면, 다시 종료하기 전까지 Keep Pool에 계속 유지됩니다. 이렇게 하면 디스크 I/O를 크게 줄일 수 있습니다.
히트 비율(hit ratio)을 확인하면 파싱 코드와 실행 계획이 라이브러리 캐시에서 플러시되고 있는지 파악할 수 있습니다. 라이브러리 캐시의 히트 비율은 85% 이상을 유지해야 합니다.
Stream Pool은 Data Pump 작업을 위한 버퍼로 사용됩니다.
매일 반복적으로 조회되는 소규모 테이블은 성능 향상을 위해 SGA의 Keep Pool에 보관하는 것이 좋습니다. 이렇게 하면 디스크에서 데이터를 읽지 않고 메모리에서 바로 가져오기 때문에 성능이 크게 향상됩니다. 반면 대용량 테이블에는 Recycle Pool을 활용합니다.
기본 설정에서는 테이블을 블록 단위로 조회할 때 데이터 버퍼 캐시에 적재되며 Default Pool에 저장됩니다. Default Pool은 다른 테이블을 조회하면 기존 내용이 밀려나 플러시됩니다. 버퍼에 충분한 공간이 있다면 문제없지만, 공간이 부족하면 새 테이블을 수용하기 위해 이전 테이블이 제거됩니다.
따라서 성능을 높이려면 자주 사용하는 소규모 테이블을 Default Pool 대신 Keep Pool에 배치하는 것이 좋습니다. 데이터 버퍼 캐시의 히트 비율은 95% 이상이어야 하며, 95% 미만이라면 DB 버퍼 캐시 크기를 조정해야 합니다.
메모리 튜닝의 핵심은 DB 버퍼 캐시와 공유 풀입니다. 이 두 영역은 메모리에 충분한 데이터를 유지하는 데 중요한 역할을 합니다. SGA 튜닝을 위해서는 DB 버퍼 캐시의 기본 동작 원리를 반드시 이해해야 합니다. 글 마지막에서는 히트 비율을 계산하여 메모리 튜닝에 활용할 수 있는 샘플 SQL 쿼리도 함께 소개합니다.
프리 버퍼(Free Buffers)
수정 전 데이터는 디스크에서 블록을 읽어 데이터 버퍼 캐시로 복사한 후 변경 작업을 거칩니다. 아직 수정되지 않아 재사용 가능한 상태의 버퍼를 프리 버퍼(Free Buffer)라고 합니다. 예를 들어 디스크의 값 1000과 메모리의 값 1000이 동일한 상태라면 해당 버퍼는 프리 버퍼입니다.
수정된 버퍼(Modified Buffers)
수정된 버퍼는 더티 버퍼(Dirty Buffer)라고도 불립니다. 디스크의 데이터 이미지와 데이터 버퍼 캐시 내 이미지가 서로 달라졌지만, 아직 디스크에 기록되지 않은 버퍼를 의미합니다.
고정된 버퍼(Pinned Buffers)
DB 버퍼 캐시의 데이터는 항상 변화합니다. 서버 프로세스(Server Process)는 추가 트랜잭션 처리를 위해 수정된 데이터를 선택하는데, 이렇게 선택된 데이터를 고정된 데이터 또는 핀 버퍼(Pinned Buffer)라고 합니다.
물리적 읽기(Physical Reads): Oracle이 I/O 작업을 통해 디스크에서 직접 읽어온 데이터 블록을 의미합니다.
논리적 읽기(Logical Reads): Oracle이 데이터베이스 버퍼 캐시 자체만으로 요청을 충족할 수 있는 경우의 읽기를 말합니다.
DB 블록 겟(DB Block Gets): Oracle이 필요한 데이터를 데이터베이스 버퍼 캐시에서 찾으면, 해당 데이터가 커밋되었는지 확인한 후 커밋된 경우 버퍼에서 데이터를 가져옵니다. 이런 버퍼를 DB 블록 겟이라고 합니다.
일관된 읽기(Consistent Reads): 데이터베이스 버퍼 캐시에 존재하지만 수정 후 아직 커밋되지 않은 블록의 경우, 언두(Undo) 데이터 파일에서 데이터를 가져와야 합니다.
DB 버퍼 캐시의 목표는 논리적 읽기를 늘리는 것입니다.
소프트 파싱(Soft Parsing): 라이브러리 캐시에 이미 실행 계획이 존재한다면 디스크에 접근하지 않고 기존 실행 계획을 재사용하여 파싱 코드를 생성합니다. 이를 소프트 파싱이라고 합니다.
하드 파싱(Hard Parsing): 파싱 코드를 생성할 때 먼저 실행 계획이 존재하는지 확인합니다. 실행 계획이 있으면 소프트 파싱을 진행하고, 없다면 하드 파싱을 수행하게 되는데, 이는 디스크까지 접근한다는 의미입니다.
공유 풀(Shared Pool): 공유 풀은 라이브러리 캐시(Library Cache)와 데이터 딕셔너리 캐시(Data Dictionary Cache)의 결합체입니다.
라이브러리 캐시(Library Cache): 라이브러리 캐시의 목표는 소프트 파싱을 늘리는 것입니다.
목표 달성 방법
- 바인드 변수(Bind Variable) 사용
- 저장 프로시저(Stored Procedure) 작성
- 히트 비율 관리 (85% 이상 유지, 미달 시 공유 풀 크기 증가)
- DBMS_SHARED_POOL 패키지 활용
SQL 쿼리를 활용한 메모리 튜닝에서 히트 비율은 매우 중요한 역할을 합니다.
데이터 딕셔너리 캐시(Data Dictionary Cache): 로우 캐시(Row Cache)라고도 불립니다. 데이터 딕셔너리 캐시의 히트 비율 역시 85% 이상이어야 하며, 미달 시 공유 풀 크기를 늘려야 합니다.
히트 비율 계산 샘플 쿼리
select (sum(pinhits)/sum(pins))*100 as lchitratio from v$librarycache;
select namespace, pins, pinhits, reloads from v$librarycache order by namespace;
DBMS_SHARED_POOL 패키지를 설치하려면 @$ORACLE_HOME/rdbms/admin/dbmspool.sql을 실행하세요.
select namespace, kept, locks, executions from v$db_object_cache where type like '%PROC%';
데이터 딕셔너리 캐시 히트 비율
select (sum(gets-getmisses-fixed))/sum(gets) as "ddchitratio" from v$rowcache;
리두 로그 버퍼 캐시(Redo Log Buffer Cache)
select name, value from v$sysstat where name like 'redo%';
위에서 설명한 내용들이 Oracle 데이터베이스 메모리 튜닝에 도움이 되기를 바랍니다. 이제 여러분도 데이터베이스 메모리를 손쉽게 튜닝할 수 있을 것입니다.
궁금한 점이나 피드백이 있다면 피드백 탭을 이용해 의견을 남겨주세요. 언제든지 문의를 환영합니다.