[DB] Oracle SGA (System Global Area) - Shared Pool

Shared Pool

Shared Pool은 Oracle 데이터베이스 SGA 내 위치한 메모리 영역입니다. 모든 사용자 프로세스가 공유하는 캐시메모리로, DB 인스턴스 시작 시 할당되며 인스턴스가 종료될 때까지 유지됩니다.

 

비유를 통해 좀 더 쉽게 이해해 볼까요?

---

 

 

저는 패스트푸트점 롯x리아에서 아르바이트를 한 경험이 있습니다.

 

Shared Pool이 없는 상황

처음 아르바이트 할 때는 레시피가 익숙치 않아서 버거 주문이 들어올 때마다 레시피를 보고 만들었습니다. 그러다 보니 주문이 계속 밀리고 손님들에게 햄버거가 나가는 시간이 지체됐습니다.

 

Shared Pool이 있는 상황

한 달 정도 일을 하다 보니 레시피가 머릿속에 다 들어와 버린 상태가 됐습니다. 주문도 밀리지 않고, 주말 교회나 초등학교 등 점심 단체주문이 들어와도 매우 빠른 속도로 햄버거를 만들었습니다.

 

바로 이게 핵심인데요, 자주 만드는 햄버거의 레시피를 머릿속에 기억해 두는 것이 Shared Pool이 하는 일입니다.

 

 

Shared Pool 상세 구성 요소를 알아볼까요?

1. Library Cache

실제 서비스를 하는 상황을 예시로 들어봅시다.

-- 수강생이 내 강의 목록 조회
SELECT course_title, progress, last_watched
FROM enrollments
WHERE student_id = ?;

 

이 쿼리를 처음 실행하면 Hard Parsing이 일어납니다. 오라클은 쿼리의 문법을 확인하고, 테이블 구조와 최적 실행 순서를 만들어냅니다. 그 후에 Library Cache에 저장하게 됩니다.

 

이 쿼리를 나중에 또 실행하게 되면, Soft Parsing이 일어나고, 오라클은 기억하고 있던 쿼리를 꺼내 바로 실행하게 됩니다.

 

2. Data Dictionary  Cahce

특정 테이블에 어떤 컬럼이 있는지, 이 사용자가 쿼리를 실행할 권한이 있는지, 인덱스가 어떻게 되어있는지 이런 메타 정보들을 Data Dictonary Cache에 저장합니다.

---

💻 실제 서비스에서 일어나는 일

출퇴근 시간은 인스타그램 사용자가 몰리는 시간입니다.

여러 이용자가 동시에 인스타그램에서 피드를 조회하는 상황이라 가정해 봅시다.

-- 타임라인 조회 (초당 5000번 실행)
SELECT post_id, image_url, caption, like_count
FROM posts p, follows f
WHERE p.user_id = f.following_id 
AND f.follower_id = ?
ORDER BY p.created_at DESC;

 

첫 번째 조회 후 Library Cache에 저장되면, 나머지는 바로바로 빠르게 처리할 수 있게 됩니다.

 

'?'의 의미는 무엇일까?

 

바인드 변수 - '?'

만약 sql을 다음과 같이 짜게 되면?

// 검색어별로 새로운 SQL 생성
for(String keyword : trendingKeywords) {
    String sql = "SELECT COUNT(*) FROM searches WHERE keyword = '" + keyword + "'";
    // "BTS", "아이유", "날씨" 각각 다른 SQL로 인식!
}

 

 

검색어 1000개가 모두 다르다면, 1000개의 다른 SQL이 만들어지게 됩니다. 당연히 속도도 매우 저하되겠죠......

 

바인드 변수로 수정해 봅시다.

// 하나의 SQL 템플릿으로 재사용
String sql = "SELECT COUNT(*) FROM searches WHERE keyword = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
for(String keyword : trendingKeywords) {
    pstmt.setString(1, keyword);
    // 하나의 파싱된 SQL로 모든 검색어 처리!
}

 

1000개의 검색어를 Library Cache에 있는 SQL을 가져와 쓰게 됩니다. 그렇기 때문에 메모리 사용량이 매우 감소하고, 속도도 그만큼 빨라지게 됩니다.

 

요약하자면, Shared Pool은 메모리 기반 성능 최적화 메커니즘입니다.

• Library Cache - 파싱된 SQL 실행계획 재사용으로 CPU 오버헤드 제거
• Data Dictionary Cache - 메타데이터 메모리 캐싱으로 디스크 I/O 최소화
• 바인드 변수 - 동일 SQL 패턴 통합으로 메모리 효율성 극대화

PreparedStatement를 사용하여 바인드 변수를 구현하고, Library Cache Hit Ratio 95% 이상 유지하도록 모니터링하는 걸 권장하고 있습니다.

 

결과적으로 Shared Pool을 제대로 활용하면 동일한 하드웨어 자원으로 수십 배의 처리량 향상을 얻을 수 있다고 하네요!

 

실무에서는 바인드 변수 사용과 적절한 크기 설정 및 모니터링이 가장 중요한 역량인 듯합니다!