PostgreSQL vs Oracle: 실행 계획 캐싱 전략과 통계 수집의 민감도 비교 분석

※ PostgreSQL vs Oracle: A Comparative Analysis of Execution Plan Caching Strategies and Sensitivity to Statistics Collection.

 

안녕하세요. 듀스트림입니다.

 

Oracle에서 PostgreSQL로 전환하는 프로젝트 시 자주 나오는 질문들이 있습니다.

 

오늘 포스팅은 그 중 하나인 실행 계획 캐싱에 대한 내용을 주제로 다뤄봤습니다.

---

특히, OLTP 시스템에서 데이터베이스 성능은 실행 계획의 적절성에 크게 의존합니다.
실행 계획이 캐시되고 재사용되는 방식과 통계 수집 작업이 계획 무효화에 미치는 영향은 성능 최적화 및 예측 가능성 확보에 있어 핵심적인 이슈입니다.

이 글은 PostgreSQL과 Oracle의 실행 계획 전략, 통계 수집, 계획 캐싱과 무효화 메커니즘을 비교한 글입니다.

---

1. 실행 계획 캐싱: 구조의 차이

1.1 PostgreSQL: 세션 단위 Plan 캐싱

• Prepared Statement 기반 캐싱: SQL을 미리 파싱/계획 수립 후 재사용 가능한 실행 단위로 등록하는 PostgreSQL의 기능입니다.
  • PREPARE, EXECUTE 구문을 직접 사용할 수도 있습니다.
  • 대부분의 PostgreSQL 클라이언트 (JDBC, psycopg2, libpq 등)는 자동 prepared statement 사용 가능합니다.

-- 실행 계획 캐싱
PREPARE my_stmt (int) AS
SELECT * FROM employees WHERE department_id = $1;

-- 실행
EXECUTE my_stmt(10);

• 두 가지 플랜 방식: Prepared Statement 사용 시, PostgreSQL은 다음 두 가지 방식 중 하나로 실행계획을 선택합니다.
  
  • Custom Plan: 바인드 파라미터 값을 기준으로 매번 실행 계획을 생성합니다.
  • Generic Plan: 쿼리 구조와 평균 통계 정보를 기반으로 실행 계획을 생성하고, 이를 최적화하여 일반화된 공통 실행 계획을 선택한 후 재사용합니다.
• 플랜 선택 기준: 어떤 방식을 사용할지는 내부 비용 비교 또는 설정(plan_cache_mode)에 따라 결정됩니다.
• 기본 동작: 기본값인 auto 설정에서는 처음 5번의 실행에서 Custom Plan을 사용하여 정확한 계획을 만드는 것을 목표로 하고, 이후 Generic Plan의 비용이 Custom Plan의 평균 비용보다 낮으면 Generic Plan을 사용합니다.
  → 이 동작은 src/backend/utils/cache/plancache.c 의 ChooseCustomPlan() 로직에서 결정됩니다.
• 설정 예시: plan_cache_mode를 force_custom_plan으로 설정하면 항상 Custom Plan을 사용하도록 강제할 수 있습니다.

-- 세션 단위 설정
SET plan_cache_mode = 'force_custom_plan';

-- 데이터베이스 단위 설정
ALTER DATABASE dbname SET plan_cache_mode = ...;

-- 사용자(롤) 단위 설정
ALTER ROLE ... SET plan_cache_mode = ...;

---

1.2 Oracle: 글로벌 Plan 캐싱 (Shared Pool)

• 공유 풀 활용: SQL 문장이 같다면 바인드 변수 유무와 관계없이 계획을 공유합니다.
• Bind Peeking: 바인드 변수에 최초 전달된 값을 "엿보고(peek)" 히스토그램을 통해 선택도를 추정하여 실행 계획을 수립합니다.
  
  • 문제점: Bind Peeking의 경우 최초 SQL 하드 파싱 시점의 바인드 변수 값을 기준으로 실행 계획을 수립하고, 이후 SQL은 소프트 파싱하기 때문에 하드 파싱 시점과는 맞지 않는 비효율적인 실행 계획으로 수행될 수 있습니다.
  • 해결책: 실행 계획이 동적으로 변해야 하는 경우에는 Bind Peeking을 비활성화하거나, Adaptive Cursor Sharing(ACS) 기능을 활용하여 다양한 실행 계획을 관리합니다.
    • Bind Peeking 비활성화 시 단순히 SQL 문장만을 기준으로 실행 계획을 생성합니다.
      (SQL 문 자체와 관련된 일반적인 최적화(통계, 테이블 구조, 인덱스 등)를 기준으로 생성)
• 공유 풀 저장: 이후에도 같은 계획을 재사용하며, 바인드 변수 값이 달라도 실행 계획은 바뀌지 않습니다. 이러한 실행 계획은 Library Cache에 저장되며, 글로벌로 공유되며 여러 세션에서 사용할 수 있습니다.

---

2. plan_cache_mode vs Bind Peeking

PostgreSQL의 plan_cache_mode는 Oracle의 Bind Peeking과 유사한 동작 특성을 가집니다.
(단, PostgreSQL은 바인드 값을 받을 시 실행 계획을 매번 재생성합니다.)

 

2.1 PostgreSQL의 plan_cache_mode

값	설명
auto (기본값)	PostgreSQL이 실행 통계를 기반으로 자동 판단
force_generic_plan	항상 Generic Plan 사용 (바인드 값 무시, 캐시 재사용 최우선)
force_custom_plan	항상 Custom Plan 사용 (바인드 값 기반 계획 수립 강제)

 

2.2 기능 비교

기능	PostgreSQL	Oracle
바인드 변수 최적화	Custom Plan (plan_cache_mode)	Bind Peeking
공유된 실행 계획	세션 단위로 실행 계획을 캐싱	글로벌 공유 풀(Shared Pool)을 통해 실행 계획을 공유
최초 실행 시 바인드 값 반영	✅ (Custom Plan일 때)	✅ (Bind Peeking 사용 시)
이후 실행 시 계획 재사용	✅ (Generic Plan 시)	✅
바인드 값에 따른 실행 계획 변경	❌	✅ (Adaptive Cursor Sharing 사용 시)

---

3. 실행 계획 무효화: 통계 변경의 영향

3.1 PostgreSQL: ANALYZE 이후

• ANALYZE 명령 또는 auto-analyze가 실행되면 통계가 변경됩니다.
• 이로 인해 해당 테이블을 사용하는 Generic Plan이 무효화되며, 다음 실행 시 새로운 계획이 생성됩니다.
• Custom Plan은 매번 새로 생성되므로 통계 변경의 영향을 직접적으로 받지 않지만, 통계 정보는 여전히 선택도 추정에 사용됩니다.

PREPARE

---

3.2 Oracle: DBMS_STATS 이후

• DBMS_STATS.GATHER_TABLE_STATS 실행 시 옵티마이저 통계가 업데이트됩니다.
• Shared Pool에 커서가 존재하는 경우, 해당 SQL은 여전히 기존 실행계획을 재사용하며 Oracle Optimizer는 Hard Parse 시에만 최신 통계를 참조하여 실행계획을 수립합니다.
• 이를 보완하기 위한 Adaptive Cursor Sharing, SQL Plan Management(SQL Plan Baseline) 등의 기능이 있습니다.

SQL Tuning Guide

---

4. 유지 관리 작업: VACUUM, ANALYZE vs REORG, DBMS_STATS

항목	PostgreSQL	Oracle
Dead Tuple 정리	VACUUM, VACUUM FULL	필요 없음 - Undo 방식
통계 수집	ANALYZE, auto-analyze	DBMS_STATS.GATHER_*
테이블 재정렬	VACUUM FULL	REORG (Segment 재작성)
통계 변경 후 플랜 무효화	✅ Generic Plan 무효화	🟨 명시적인 무효화 작업 필요 (다시 하드 파싱되는 경우 재생성)
운영 중 리스크	auto-analyze 타이밍 예측 어려움 (Plan 변경 발생)	Bind Peeking에 의한 성능 급변

 

※ 추가 설명

• PostgreSQL:
  
  • Dead Tuple 정리: PostgreSQL은 MVCC(Multi-Version Concurrency Control) 아키텍처를 사용하여 업데이트나 삭제된 행을 "dead tuple"로 처리합니다. VACUUM 명령은 이러한 dead tuple을 정리하여 공간을 회수합니다. VACUUM FULL은 테이블을 재작성하여 공간을 완전히 회수하지만, 테이블에 대한 독점 잠금을 요구합니다.
  • 통계 수집: ANALYZE 명령은 테이블의 통계를 수집하여 쿼리 최적화에 사용됩니다. auto-analyze 기능은 자동으로 통계를 수집하지만, 타이밍을 예측하기 어려워 실행 계획의 예측 가능성에 영향을 줄 수 있습니다.
• Oracle:
  
  • Dead Tuple 정리: Oracle은 Undo 테이블스페이스를 사용하여 이전 데이터를 보존합니다. 삭제된 데이터는 즉시 물리적으로 제거되지 않으며, Undo 정보를 통해 복구할 수 있습니다. 따라서 별도의 dead tuple 정리 작업이 필요하지 않습니다.
  • 통계 수집: DBMS_STATS.GATHER_* 명령은 테이블 및 인덱스의 통계를 수집하여 옵티마이저가 최적의 실행 계획을 선택하는 데 사용됩니다. 통계 변경 후 실행 계획이 자동으로 무효화되지 않으므로, 명시적인 무효화 작업이 필요합니다.

---

5. Adaptive Plan 기능: Oracle vs PostgreSQL

기능	PostgreSQL	Oracle
Adaptive Cursor Sharing	❌ 미지원	✅ 바인드 값 따라 다른 플랜 사용
SQL Plan Baseline	❌ 미지원 (pg_store_plans 등 확장 필요)	✅ 안정된 실행계획 관리 가능
힌트 기반 강제 계획	❌ 미지원 (pg_hint_plan 확장 필요)	✅ /*+ HINT */

---

6. 운영자 관점 요약

항목	PostgreSQL	Oracle
플랜 예측 가능성	Custom Plan은 높음, Generic Plan은 ANALYZE에 민감	Bind Peeking으로 인해 낮음 (ACS 이후 보완)
캐시된 실행 계획 유효성	통계 변경 시 무효화됨 (Generic Plan만)	명시적인 무효화 작업 필요 (다시 하드 파싱되는 경우 재생성)
유지보수 작업 민감도	auto-analyze, vacuum full 등 실시간 영향 존재	REORG 및 통계 수집 시 Shared Plan 주의
대응 전략	plan_cache_mode(force_custom_plan), autovacuum 튜닝	SPM, SQL Baseline, Adaptive Cursor Sharing

 

※ 추가 설명

• PostgreSQL:
  
  • 플랜 예측 가능성: plan_cache_mode 설정을 통해 실행 계획의 캐싱 방식을 제어할 수 있습니다. force_custom_plan을 설정하면 Custom Plan을 사용하여 예측 가능한 실행 계획을 유지할 수 있습니다.
  • 캐시된 실행 계획 유효성: Generic Plan은 통계 변경 시 무효화되어 새로운 실행 계획이 생성됩니다. Custom Plan은 매번 새로 생성되므로 통계 변경의 영향을 직접적으로 받지 않지만, 통계 정보는 여전히 선택도 추정에 사용됩니다.
• Oracle:
  
  • 캐시된 실행 계획 유효성: 통계 변경 후 실행 계획이 자동으로 무효화되지 않으므로, 명시적인 무효화 작업이 필요합니다. 예를 들어, DBMS_SHARED_POOL.PURGE를 사용하여 실행 계획을 무효화할 수 있습니다.

---

PostgreSQL과 Oracle은 실행 계획의 생성 및 재사용 전략에서 기본 철학이 다릅니다.

• PostgreSQL: 세션 단위의 실행 계획 생성을 통해 정확성과 유연성을 강조하며, 동적인 환경 변화에 민감하게 반응합니다.
• Oracle: 글로벌 공유 계획 구조를 통해 효율성을 강조하며, 실행 계획의 재사용을 통해 성능을 최적화합니다.

PostgreSQL은 오픈 소스로서의 명확한 약점들이 존재합니다.

하지만, 지식의 깊이와 경험에 따라 대부분의 시스템에서 충분히 상용 DB를 대체할 수 있다고 생각합니다.

 

그리고 제일 중요한 건 재밌어요.

 

오늘은 여기까지~