[Oracle 튜닝] 오라클 공유 풀(Shared Pool) 관리 방안

오라클 데이터베이스를 효율적으로 운영하기 위해서는 시스템 글로벌 영역(SGA)의 중요한 구성 요소인 공유 풀(Shared Pool)을 적절히 관리하는 것이 매우 중요합니다. 공유 풀은 SQL 문장, PL/SQL 코드, 시스템 매개변수, 데이터 사전 정보 등을 캐싱하여 데이터 베이스 성능을 크게 향상 시킵니다. 이번 블로그에서는 공유 풀 관리를 위한 주요 방안들을 살펴보겠습니다.

공유 풀 크기 조정

공유 풀 크기를 적절히 설정하는 것은 성능 최적화의 첫 걸음 입니다.

자동 공유 메모리 관리(ASMM) 활용

Oracle 10g 이상 버전에서는 자동 공유 메모리 관리(ASMM)를 사용할 수 있습니다. ASMM을 활성화하면 Oracle이 워크로드에 따라 공유 풀 크기를 동적으로 조정합니다.

ALTER SYSTEM SET SGA_TARGET = 1G;

이 명령어로 ASMM을 활성화하고 전체 SGA 크기를 1GB로 설정할 수 있습니다. Oracle이 공유 풀을 포함한 각 메모리 영역의 크기를 자동으로 조정 합니다.

수동 크기 조정

ASMM을 사용하지 않는 경우, SHARED_POOL_SIZE 파라미터를 통해 수동으로 크기를 설정할 수 있습니다.

ALTER SYSTEM SET SHARED_POOL_SIZE = 300M SCOPE=BOTH;

이 명령은 공유 풀 크기를 300MB로 설정 합니다. 프로덕션 시스템의 경우 최소 50MB 이상으로 설정하는 것이 좋습니다.

성능 모니터링

공유 풀의 성능을지속적으로 모니터링하는 것이 중요 합니다.

주요 성능 지표

라이브러리 캐시 히트율 : 이 비율이 100%에 가까울수록 공유 풀이 효율적으로 사용되고 있음을 의미 합니다.

데이터 사전 캐시 품질  : 프로덕션 시스템에서는 이 값이 90% 이상이여야 합니다.

모니터링 도구

• 자동 워크로드 리포지토리(AWR) : 성능 통계를 캡쳐하고 저장합니다.
• V$뷰 : V$SGASTAT, V$LIBRARYCACHE 등의 뷰를 쿼리하여 공유 풀 사용 정보를 얻을 수 있습니다.

공유 풀 최적화 기법

바인드 변수 사용

바인드 변수를 사용하면 SQL 문장의 재사용성이 높아져 공유 풀의 효율성이 증가 합니다.

패키지와 프로시저 고정

자주 사용되는 패키지, 프로시저, 트리거를 DBMS_SHARED_POOL 패키지의 KEEP 프로시저를 사용하여 메모리에 고정할 수 있습니다.

EXECUTE DBMS_SHARED_POOL.KEEP('package_name');

세션 커서 제어

OPEN_CURSORS 파라미터를 적절히 설정하여 과도한 커서 사용을 방지합니다.

공유 풀 예약 크기 최적화

SHARED_POOL_RESERVED_SIZE 파라미터를 설정하여 대형 객체를 위한 공간을 확보할 수 있습니다.

공유 풀 단편화 관리

공유 풀의 단편화는 성능 저하의 원인이 될 수 있습니다.

단편화 해결 방법

1. 공유 풀 크기 증가 : 단편화가 감지되면 공유 풀 크기를 늘려 연속된 메모리 할당 가능성을 높입니다.
2. 공유 풀 플러싱 : 극단적인 경우 ALTER SYSTEM PLUSH SHARED_POOL 명령어를 사용할수 있지만, 신중히 사용해야 합니다.
3. SQL 문 최적화  : 비효율적인 SQL 문은 단편화를 악화시킬 수 있으므로 정기적인 SQL 튜닝이 필요 합니다.

주의사항

1. 공유 풀 크기를 무조건 크게 설정하는 것은 바람직하지 않습니다. 특히 바인드 변수를 사용하지 않는 경우, 오히려 성능이 저하될 수 있습니다.
2. 공유 풀 크기 변경 후에는 데이터베이스 인스턴스를 재시작해야 변경사항이 적용됩니다.
3. 데이터 사전 캐시는 Oracle 시작 시 비어있으므로, 정상 운영 상태에 도달한 후 성능을 평가해야 합니다.

결론

공유 풀의 효율적인 관리는 Oracle 데이터베이스의 전반적인 성능 향상에 큰 영향을 미칩니다. 자동 공유 메모리 관리를 활용하거나 수동으로 세심하게 조정하고, 지속적인 모니터링과 최적화를 통해 데이터 베이스 성능을 최상으로 유지할 수 있습니다. 각 환경에 맞게 적절한 전략을 선택하고 적용하여 데이터 베이스 성능을 극해화 하시기 바랍니다.