[Oracle 튜닝] 오라클 SGA(System Global Area) 튜닝 방법

SGA(System Global Area)란?

SGA는 오라클 데이터베이스의 핵심 메모리 구조로, 여러 프로세스가 공유하는 메모리 영역입니다. 이는 데이터베이스 버퍼 캐시, 공유 풀, 라지 풀 등 다양한 컴포넌트로 구성되어 있습니다. SGA를 효과적으로 튜닝하면 데이터베이스 성능을 크게 향상 시킬 수 있습니다.

SHARED POOL

Shared Pool의 주요 목적은 SQL 구문 분석(Parsing)을 최적화하고 데이터베이스 성능을 향상시키는 것입니다. 이는 자주 사용되는 SQL 문장과 실행 계획을 캐싱하여 Hard Parsing을 줄이고 Soft Parsing의 비율을 높임으로써 달성됩니다.

1. Library Cache

• SQL 문장, PL/SQL 코드, 실행 계획 등을 저장합니다.
• LRU(Least Recently Used) 알고리즘으로 관리됩니다.
• Soft Parsing 시 활용되어 성능을 향상시킵니다.

2. Data Dictionary Cache

• 테이블, 인덱스 등의 오브젝트 정보를 저장합니다.
• 실행 계획 수립 시 사용되는 주요 딕셔너리 정보를 캐싱합니다.

3. Result Cache

• 자주 실행되는 쿼리의 결과를 저장하여 재사용합니다.
• Parsing 없이 빠르게 결과를 반환할 수 있습니다.

4. Reserved Pool

• 대형 오브젝트(5KB 이상)를 위한 예약 공간입니다.
• ORA-4031 에러(메모리 부족) 방지에 도움을 줍니다.

데이터베이스 버퍼 캐시

데이터베이스 버퍼 캐시는 디스크에서 읽어온 데이터 블록을 저장하는 메모리 영역입니다. 이 캐시는 디스크 I/O를 줄이고 쿼리 성능을 향상시키는 데 중요한 역할을 합니다.

리두 로그 버퍼

리두 로그 버퍼는 데이터베이스 변경 사항을 임시로 저장하는 영역입니다. 이 버퍼는 변경 내용을 물리적인 리두 로그 파일에 기록하기 전에 캐싱하여 성능을 향상시킵니다.

대형 풀

대형 풀은 병렬 쿼리나 백업 작업과 같은 대규모 I/O 요청을 처리하기 위한 선택적 메모리 영역입니다.

자바 풀

자바 풀은 Java Virtual Machine(JVM) 내의 세션별 Java 코드와 데이터를 위한 메모리 영역입니다.

스트림 풀

스트림 풀은 Oracle Streams 기능에서 사용되는 메모리 영역으로, 데이터 복제 등의 작업에 활용됩니다.

결과 캐시

결과 캐시는 쿼리 결과를 버퍼링하는 영역으로, 자주 실행되는 쿼리의 성능을 향상시킵니다.

이러한 SGA 구성요소들은 오라클 데이터베이스의 성능과 효율성을 높이는 데 중요한 역할을 합니다. 각 구성요소의 크기는 데이터베이스 워크로드와 사용 패턴에 따라 자동으로 또는 수동으로 조정될 수 있습니다.

SGA 튜닝의 중요성

SGA 튜닝은 다음과 같은 이유로 중요합니다.

1. 디스크 I/O 감소 : 더 많은 데이터를 메모리에 유지함으로써 디스크 읽기 횟수를 줄입니다.
2. 쿼리 성능 향상 : 자주 사용되는 데이터와 SQL 실행 계획을 메모리에 캐싱하여 응답 시간을 개선 합니다.
3. 리소스 경합 감소 : 적절한 메모리 할당으로 프로세스 간 메모리 경합을 최소화 합니다.

SGA 튜닝 방법

1.자동 공유 메모리 관리 사용

오라클 10g 이후 버전에서는 자동 공유 메모리 관리(Automatic Shared Memory Management, ASMM)를 사용할 수 있습니다. 이 기능을 활성화하려면 'SGA_TARGET' 파라미터를 설정하면 됩니다.

ALTER SYSTEM SET SGA_TARGET = 72G SCOPE=SPFILE;

ASMM을 사용하면 오라클이 자동으로 SGA 컴포넌트의 크기를 조정하여 최적의 성능을 제공 합니다.

2.SGA  컴포넌트 수동 조정

ASMM을 사용하지 않는 경우, 각 SGA 컴포넌트를 수동으로 조정해야 합니다.

데이터베이스 버퍼 캐시 : 'DB_CACHE_SIZE' 파라미터로 설정

1. 공유 풀 : 'SHARED_POOL_SIZE' 파라미터로 설정
2. 라지 풀 : 'LARGE_POOL_SIZE' 파라미터로 설정
3. 자바 풀 : 'JAVA_POOL_SIZE' 파라미터로 설정

3.SGA 어드바이저 활용

오라클은 SGA 크기를 조정을 위한 어드바이저를 제공 합니다. Enterprise Manager나 SQL 쿼리를 통해 이 정보에 접근 할 수 있습니다.

SELECT * FROM V$SGA_TARGET_ADVICE;

이 쿼리를 다양한 SGA 크기에 대한 성능 영향을 보여 줍니다.

4.워크로드 분석

AWR(Automatic Workload Repository) 리포트를 분석하여 SGA 관련 대기 이벤트와 히트 비율을 확인합니다. 예를 들어, 'db file sequential read' 대기 이벤트가 많다면 버퍼 캐시 크기 증가를 고려해볼 수 있스니다.

5.메모리 할당 가이드라인

일반적으로 전체 시스템 메모리의 75%-80%를 오라클 데이터 베이스에 할당하는 것이 좋습니다. 이 중 60%는 SGA에, 30%는 PGA에 할당할 수 있습니다.

SGA_TARGET = (Total Physical Memory * 80%) * 60%
PGA_AGGREGATE_TARGET = (Total Physical Memory * 80%) * 30%

6.정기적인 모니터링 조정

'V$SGASTAT' 뷰를 사용하여 SGA 컴포넌트의 메모리 사용량을 모니터링 합니다. 워크로드 변화에 따라 주기적으로 SGA설정을 재검토 하고 조정합니다.

주의 사항

SGA 크기 증가가 항상 성능 향상을 보장하지는 않습니다. 과도한 메모리 할당은 오히려 성능 저하를 초래 할 수 있습니다.

SGA 크기 변경 후에는 반드시 데이터 베이스를 재시작해야 합니다.

'SGA_MAX_SIZE' 파리미터를 설정하여 SGA의 최대 크기를 제한할 수 있습니다. 이는 런타임에 변경할 수 없으므로 신중히 설정해야 합니다.

결론

SGA 튜닝은 오라클 데이터 베이스 성능 최적화의 핵심 요소 입니다. 자동 메모리 관리 기능을 활용하거나, 필요에 따라 수동으로 세밀하게 조정할 수 있습니다. 워크로드 특성을 이해하고, 정기적인 모니터링 분석을 통해 최적의 SGA 구성을 찾아내는 것이 중요 합니다.

 

SGA 튜닝은 단순히 메모리 크기를 늘리는 것이 아니라, 데이터베이스 워크로드에 맞춰 효율적으로 메모리를 할당하고 관리하는 과정 입니다. 지속적인 관심과 조정을 통해 데이터베이스 성능을 최적의 상태로 유지할 수 있습니다.