Oracle: Bind Peeking, Adaptive Cursor Sharing, Adaptive Plan

Oracle

Oracle: Bind Peeking, Adaptive Cursor Sharing, Adaptive Plan

dewstream 2025. 5. 13. 08:00

728x90

※ Oracle: Bind Peeking, Adaptive Cursor Sharing, Adaptive Plan.

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

이번 포스팅은 오라클의 바인드 피킹 관련 내용입니다.

1. 바인드 피킹 (Bind Peeking)

개념

바인드 피킹은 Oracle에서 파라미터 바인딩(bind variable) 을 사용한 SQL 문장을 실행할 때, 첫 번째 실행 시에만 바인드 변수의 실제 값(literal value)을 '엿보고(peek)', 그 값을 기반으로 실행계획을 수립하는 기법입니다.

SELECT * FROM employees WHERE department_id = :dept_id;

첫 실행에서 :dept_id에 10이 들어왔다면, 옵티마이저는 department_id = 10이라는 조건이 있다고 가정하고 통계를 활용하여 실행계획을 수립합니다. 이후의 실행은 값이 20, 30 등으로 달라져도 처음 만든 실행계획을 재사용합니다.

첫 동작 흐름

1. SQL이 파싱되면 (Parse Phase)
2. 바인드 변수의 값을 첫 실행 시 Peek
3. 옵티마이저는 Peek된 값을 기준으로 통계 정보를 적용하여 계획을 수립
4. 이후 같은 SQL ID의 커서는 재사용

2. 실행계획 커서(Cursor)와 커서 공유

커서란?

Oracle은 SQL을 내부적으로 커서(Cursor)라는 객체로 관리합니다. 이는 SQL 문, 바인드 정보, 실행계획, 실행 통계 등을 포함하는 구조체입니다.

Library Cache에 저장
SQL 문장의 Normalized Text(공백 제거, 대소문자 통일 등)를 기준으로 hash
동일한 SQL 문장은 커서를 공유함

커서 공유 종류 (CURSOR_SHARING 파라미터)

값	의미
EXACT (기본값)	SQL 텍스트가 완전히 동일할 때만 커서 공유
FORCE	바인드 변수 없이도 커서를 강제 공유 (문자 리터럴 → 바인드로 치환)
SIMILAR	리터럴을 바인드 변수로 치환하지만, 값에 따라 계획이 달라지면 커서 분리 (11g deprecated)

3. 바인드 피킹 관련 문제점

문제	설명
계획 고정	첫 실행 시 비정상적인(희귀) 값으로 최적화되면 이후 실행이 느려짐
커서 공유 과잉	FORCE 설정 시 모든 SQL이 같은 계획을 공유해 sub-optimal
Adaptive Cursor Sharing (ACS)	Oracle 11g부터 도입. 실행 시점에 바인드 값에 따라 다른 커서를 선택 가능

4. 바인드 피킹 제어 방법

바인드 피킹 파라미터

1. _OPTIM_PEEK_USER_BINDS (비공식 hidden parameter)

설명: 옵티마이저가 SQL 실행 시 바인드 변수의 값을 peek(엿보기) 해서 실행계획을 만들지 여부를 제어
값: TRUE (기본값), FALSE
용도:
- 바인드 변수 기반 계획 고정화 방지
- 바인드 피킹 자체를 막고 통계 기반의 일반 계획 유도
Bind Peeking 연관성: 이 파라미터를 FALSE로 설정하면 바인드 피킹이 비활성화되어, 옵티마이저는 바인드 값 없이 통계만 보고 계획을 수립함
예시:

ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE;

2. OPTIMIZER_FEATURES_ENABLE

설명:
- 특정 Oracle 버전의 옵티마이저 동작을 에뮬레이션(모방) 하도록 하는 파라미터.
- 해당 버전의 힌트 처리 방식, Join 순서 결정, 바인드 처리 방식 등 전반적인 옵티마이저 알고리즘을 따라감
값: '11.2.0.4', '12.1.0.2', '19.1.0' 등 버전 문자열
용도: 실행계획이 과거 버전에서 더 안정적이었을 경우, 해당 버전 옵티마이저 동작을 유지하고자 할 때 사용
Bind Peeking 연관성: 바인드 피킹, 히스토그램 반영 방식, ACS 동작 유무 등도 버전별로 다르므로, 이 파라미터는 간접적으로 바인드 피킹 및 실행계획 수립에 영향
예시:

ALTER SESSION SET optimizer_features_enable = '12.1.0.2';

3. CURSOR_SHARING

설명: SQL 문장이 동일하지 않더라도 리터럴 값을 바인드 변수로 치환하여 커서를 공유할지 여부를 제어
값:
- EXACT (기본값): SQL 텍스트가 완전히 같을 때만 공유
- FORCE: 리터럴을 바인드 변수로 치환하여 강제로 공유
- SIMILAR: 선택도 차이에 따라 커서를 분리 (11g 이후 deprecated)
용도: 커서 수 증가 방지, Library Cache 부하 완화
Bind Peeking 연관성:
- 리터럴을 바인드로 치환해 커서를 공유할 경우, 바인드 피킹이 무조건 발생하며
  → 바인드 값의 skew(쏠림)에 따른 계획 문제로 이어질 수 있음
예시:

ALTER SESSION SET cursor_sharing = FORCE;

4. optimizer_capture_sql_plan_baselines / optimizer_use_sql_plan_baselines

설명:
- SQL Plan Management(SPM) 기능 관련 파라미터
  → SQL 실행 시 계획을 캡처하고, 이후 실행 시 같은 계획을 유지
값: TRUE, FALSE
용도: 실행계획의 예기치 않은 변화 방지 (특히 바인드 피킹, 통계 갱신 등으로 인한 변화 대응)
Bind Peeking 연관성: ACS나 바인드 피킹에 의해 실행계획이 바뀌는 경우에도 Baseline이 있다면 기존 계획이 고정되어 성능 안정성 확보 가능
예시:

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;
ALTER SYSTEM SET optimizer_use_sql_plan_baselines = TRUE;

바인드 피킹에 영향을 주는 통계 관련 파라미터

1. optimizer_dynamic_sampling

설명: 통계가 없거나 부정확한 경우, 옵티마이저가 실행계획 수립 시 동적으로 샘플링을 수행하여 선택도를 추정함.
범위: 0 ~ 11 (Oracle 19c 기준)
- 기본값: 2 (기본 통계가 있을 경우 샘플링 거의 안함)
- 값이 클수록 정밀하게 샘플링 (단, parse 시점의 부하 증가)
Bind Peeking 연관성:
- 바인드 변수 사용 시 통계가 부실하면 이 값을 높여 선택도 추정을 정교하게 할 수 있음.

ALTER SESSION SET optimizer_dynamic_sampling = 11;

2. statistics_level

설명: 옵티마이저가 수집하는 통계의 수준을 지정
값:
- BASIC: 최소한의 통계 수집 (예: gather_plan_statistics 사용 안됨)
- TYPICAL (기본값): 일반적인 통계 수집 및 성능 피드백 활성화
- ALL: 모든 통계를 수집 (V$SQL_PLAN_STATISTICS_ALL 등에서 세부 정보 확인 가능)
Bind Peeking 연관성:
- ALL로 설정 시 Adaptive Cursor Sharing의 트리거에 더 많은 정보 제공 가능

3. optimizer_use_pending_statistics

설명: DBMS_STATS.SET_TABLE_STATS나 GATHER_STATS로 수집했지만 아직 적용되지 않은 pending 통계를 사용할지 여부
용도:
- 통계 적용 전, 테스트 용도로 다른 세션에서 미리 평가 가능
Bind Peeking 연관성:
- 새로운 통계가 바인드 값 선택도 추정에 영향을 줄 수 있으므로, 사전 테스트 필요

4. 히스토그램 관련 통계

method_opt (DBMS_STATS 파라미터)
예시:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR',
    tabname => 'EMPLOYEES',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;

옵션 설명:
- SIZE 1: 히스토그램 생성 안함
- SIZE 254: 최대 254 버킷
- SIZE AUTO: Oracle이 히스토그램 생성 여부를 결정
Bind Peeking 연관성:
- 히스토그램이 존재해야 옵티마이저가 바인드 값의 skew(쏠림 현상)를 감지해 더 정확한 계획을 수립함
- 특히 frequency/height balanced 히스토그램이 skew한 값에 민감하게 작동

바인드 피킹 튜닝 전략 요약표

항목	설명
_OPTIM_PEEK_USER_BINDS	바인드 피킹을 비활성화하는 비공식 파라미터 (디버깅/특수 상황용)
optimizer_dynamic_sampling	통계가 없거나 부정확한 경우, 실행 시 샘플링 수준을 조정 (0~11)
statistics_level	옵티마이저가 수집하는 통계의 수준을 설정 (BASIC, TYPICAL, ALL)
optimizer_use_pending_statistics	아직 적용되지 않은 통계를 기준으로 실행계획을 수립할지 여부
method_opt (DBMS_STATS)	히스토그램 생성 방식 설정. 선택도 추정 정확도에 큰 영향
CURSOR_SHARING	SQL 리터럴을 바인드 변수로 치환하여 커서 수를 제어 (EXACT, FORCE, SIMILAR)
OPTIMIZER_FEATURES_ENABLE	특정 버전의 옵티마이저 동작을 전체적으로 모방하도록 설정

힌트

힌트 기반 바인드 피킹 제어

힌트	설명
/+ NO_BIND_AWARE /	Adaptive Cursor Sharing(ACS) 방지. 항상 같은 커서 사용
/+ NO_BIND_PEAKING /	바인드 피킹을 무시하고 통계만 기반으로 실행계획 선택
/+ FULL(t) /, /+ INDEX(t idx) /	강제 스캔 방식 지정으로 선택도 예측 오류 방지 가능
/+ OPT_PARAM('optimizer_dynamic_sampling', 11) /	힌트를 통해 쿼리 단위로 동적 샘플링 수준 조정
/+ OPT_PARAM('optimizer_features_enable','19.1.0') /	쿼리 단위로 특정 버전 옵티마이저 동작 모방

예시:

-- 바인드 피킹 방지 (기본 통계 기반으로 계획 수립)
SELECT /*+ NO_BIND_PEAKING */ * FROM employees WHERE department_id = :dept_id;

5. Adaptive Cursor Sharing (ACS)

Oracle 11g부터 도입된 ACS (Adaptive Cursor Sharing) 는 바인드 변수의 값에 따라 실행계획의 성능 차이가 클 경우, 동일 SQL이라도 바인드 값에 따라 다른 커서(child cursor) 를 생성하여 사용할 수 있도록 한 기능입니다.

ACS 작동 원리

단계	설명
1. bind-sensitive	바인드 변수의 값에 따라 Selectivity가 바뀔 수 있음을 옵티마이저가 감지
2. 실행 통계 수집	v$sql_cs_selectivity, v$sql_cs_histogram에 값별 통계 축적
3. bind-aware 전환	실행 결과에 따라 성능 차이가 클 경우 → 바인드 값 기반으로 커서 분기 시작
4. 커서 분기 활성화	동일한 SQL이더라도 바인드 값별로 최대 20개까지 child cursor 생성 가능 (_cursor_obsolete_threshold)

SQL이 바인드 변수 사용 중이고, bind-sensitive한 조건을 포함할 경우 실행 통계(v$sql_cs_selectivity)를 수집하고 일정 기준 이상 성능 편차가 감지되면 → bind-aware 상태로 전환 이후 바인드 값 범위별로 child cursor 분기

ACS 확인 방법

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable
FROM   v$sql
WHERE  sql_text LIKE 'SELECT * FROM employees WHERE department_id = :dept_id%';

ACS 관련 상태 정의

상태	설명
bind-sensitive	바인드 값에 따라 선택도가 달라질 수 있음을 Oracle이 감지함 (기본 상태)
bind-aware	실제 실행 통계를 통해 성능 편차가 크다고 판단됨 → 바인드 값별 커서 생성 시작
bind-equal	바인드 값이 실행계획에 영향 없음 (ACS 미적용)

예시:

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable
FROM   v$sql
WHERE  sql_text LIKE 'SELECT * FROM employees WHERE department_id = :dept_id%';

내부 트리거 조건

요소	설명
v$sql_cs_selectivity	바인드 값 별 selectivity 기록
v$sql_cs_histogram	바인드 값의 범위 정보 추적
옵티마이저 조건	선택도 차이 * plan cost > 임계치 발생 시 커서 분기
child 커서 수	기본적으로 최대 20개까지 분기 가능 (_cursor_obsolete_threshold)

제어 방법

방법	설명
힌트 사용	/+ BIND_AWARE /, /+ NO_BIND_AWARE /
히스토그램 설정	method_opt => 'for columns size 254'
파라미터 조정	optimizer_features_enable로 Oracle 버전별 옵티마이저 동작 변경

성능 튜닝 시나리오

시나리오 A: 바인드 값 따라 성능 차이 큰 경우

히스토그램 생성 (method_opt => SIZE 254)
ACS 활성화 → 실행계획 자동 분기 유도
모니터링: v$sql_cs_selectivity 및 v$sql의 child 커서 수 확인

시나리오 B: 성능 차이 없음에도 커서 과도 생성

힌트 사용:

/*+ NO_BIND_AWARE */ -- ACS 방지

파라미터 설정 검토:

ALTER SESSION SET optimizer_features_enable = '11.2.0.4';

시나리오 C: 강제 커서 분기 힌트

SELECT /*+ BIND_AWARE */ * FROM employees WHERE department_id = :dept_id;
-- 실제 효과는 실행 통계에 따라 달라짐

Bind Peeking vs ACS

항목	Bind Peeking	ACS
최초 실행 기준 계획 사용	✅ (첫 실행 기준 계획 고정)	❌ (성능 차이 감지 시 분기)
값 기반 분기	❌	✅ (바인드 값 기준으로 분기)
히스토그램 영향	제한적	히스토그램 + 실행 통계 모두 반영
커서 수 제한	1	최대 20
제어 가능 여부	부분 가능 (힌트/파라미터)	부분 가능 (힌트/자동 조정)

6. Adaptive Plan

Adaptive Plan은 Oracle 12c부터 도입된 기능으로, 쿼리를 실행하는 도중에 옵티마이저가 예상한 통계와 실제 결과의 차이를 감지하여 실행계획의 일부를 동적으로 조정할 수 있게 합니다.

작동 방식

단계	설명
1. 실행 전	옵티마이저는 예측 통계를 기반으로 Plan을 구성하며, Adaptive 옵션이 포함된 경로를 포함시킴
2. 실행 중	실행계획 노드의 실제 Cardinality(반환 행 수)를 관찰
3. 조건 충족 시	옵티마이저는 실행 중에 다른 Plan 경로로 전환함 (Join 방식 변경 등)

초기에는 보수적인 계획으로 시작하고, 실행 중에 더 나은 경로가 확인되면 즉시 실행계획을 전환하는 점이 핵심입니다.

적용 대상 예시

적용 영역	예시	설명
조인 방식	Nested Loop ↔ Hash Join	예상 행 수보다 많으면 Hash Join으로 전환
Group By 방식	Sort Aggregate ↔ Hash Aggregate	그룹 수, 메모리 상황에 따라 전환
인덱스 방식	Index Full Scan ↔ Skip Scan ↔ FTS	조건 절과 통계에 따라 접근 방식 변경
서브쿼리 처리	Inline View ↔ Materialize	실시간 통계 기반 전략 선택
필터 방식	Bloom Filter 사용 여부	Join 결과에 따라 Bloom Filter 동적 적용

Adaptive Plan 확인 방법

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +ADAPTIVE'));

Adaptive Plan 설정 파라미터

-- 12.2 이상
ALTER SYSTEM SET optimizer_adaptive_plans = TRUE;
ALTER SYSTEM SET optimizer_adaptive_statistics = FALSE;  -- FALSE 권장

optimizer_adaptive_statistics는 성능 예측이 불안정하고 부작용 가능성으로 인해 FALSE로 설정하는 것이 Oracle 권장안입니다.

Bind Peeking vs ACS vs Adaptive Plan

항목	Bind Peeking	Adaptive Cursor Sharing	Adaptive Plan
도입 버전	Oracle 9i	Oracle 11g	Oracle 12c
적용 시점	첫 실행 시점	Hard Parse 시점 (바인드 감지 후)	실행 도중 (Runtime)
작동 대상	바인드 변수	바인드 변수	SQL Plan 전체
커서 분기	❌ 단일 커서만 사용	✅ child cursor 최대 20개 생성	❌ 단일 커서, Plan 내부에서 분기
히스토그램 활용	일부	적극 활용	간접 활용
대표 뷰	v$sql	v$sql_cs_selectivity, v$sql_cs_histogram	v$sql_plan, DBMS_XPLAN.DISPLAY_CURSOR()

7. 확인 SQL

바인드 피킹 및 ACS 상태 확인 (v$sql)

SELECT 
  sql_id,
  child_number,
  is_bind_sensitive,
  is_bind_aware,
  is_shareable,
  parsing_schema_name,
  executions,
  plan_hash_value
FROM v$sql
WHERE sql_text LIKE 'SELECT % FROM employees WHERE department_id = :dept_id%';

is_bind_sensitive = 'Y' → 바인드 변수 값에 따라 성능 차이가 있을 가능성 존재
is_bind_aware = 'Y' → Oracle이 바인드 값에 따라 child cursor를 분기하기 시작함
is_shareable = 'N' → 동일 SQL이라도 공유되지 않고 여러 커서가 생겼음을 의미

바인드 선택도(Selectivity) 추적 (v$sql_cs_selectivity)

SELECT 
  sql_id,
  child_number,
  bind_set_hash_value,
  predicate,
  peeked,
  selectivity
FROM v$sql_cs_selectivity
WHERE sql_id = '&SQL_ID';

바인드 변수 별 실행 시점의 선택도 추정치 확인 가능
값이 크게 다르면 ACS 트리거 가능성 ↑

바인드 값 분포 확인 (v$sql_cs_histogram)

SELECT 
  sql_id,
  child_number,
  bind_set_hash_value,
  low,
  high,
  row_count
FROM v$sql_cs_histogram
WHERE sql_id = '&SQL_ID';

Oracle이 바인드 값들을 어떤 구간으로 나누어 child cursor를 만들었는지 보여줌

실행계획 차이 확인 (v$sql_plan)

SELECT 
  sql_id,
  child_number,
  id,
  operation,
  options,
  object_name,
  cardinality,
  cost
FROM v$sql_plan
WHERE sql_id = '&SQL_ID'
ORDER BY child_number, id;

각 child_number 별로 실제 다른 실행계획이 생성되었는지 확인 가능

커서 분기 원인 분석 (v$sql_shared_cursor)

SELECT 
  sql_id,
  child_number,
  reason
FROM v$sql_shared_cursor
WHERE sql_id = '&SQL_ID';

Oracle이 왜 같은 SQL이라도 커서를 재사용하지 않았는지 이유를 설명
ACS 관련 항목으로 bind mismatch, optimizer mismatch, stats_row_mismatch 등이 자주 나타남

8. 상황 별 대응 전략

상황	대응 전
특정 값에서만 느려짐	실행계획 고정(SQL Profile / Baseline), Adaptive Cursor Sharing 활용
공유 계획이 항상 안 좋음	바인드 대신 리터럴 사용 또는 힌트로 커서 분리
커서 수 과도 증가	cursor_sharing = force 고려 (단 성능 영향 주의)
바인드 피킹 방지 필요	_optim_peek_user_binds=false 설정 (권장 X), 힌트 사용

오늘은 여기까지~

728x90

저작자표시 비영리 변경금지 (새창열림)

'Oracle' 카테고리의 다른 글

Oracle: 메모리 구조 (4)	2025.08.16

현재글Oracle: Bind Peeking, Adaptive Cursor Sharing, Adaptive Plan

DewStream

📧: dewstream.kr@gmail.com

250x250

일상, 튜닝, essay, Query, linux, PostgreSQL, 일기, HA, PG, tuning, 에세이, SQL, Parameter, 최적화, 고가용성, 리눅스, life, Optimization, 포스트그레스, 파라미터,

Today :
Yesterday :

DewStream