Bind Variable과 실행계획: Bind Peeking·Adaptive Cursor Sharing

핵심 요약

Bind Variable은 값이 달라도 SQL Text를 일정하게 유지하여 Parent Cursor 공유와 Hard Parse 감소에 도움을 줍니다.

SELECT order_id,
       order_date
FROM orders
WHERE customer_id = :customer_id;

그러나 다음 두 문제는 구분해야 합니다.

Literal SQL이 값마다 다른 SQL Text로 생성됨
  → Parent Cursor와 SQL_ID가 증가
  → Hard Parse·Shared Pool 사용 증가

Bind SQL이지만 값별 데이터 분포가 크게 다름
  → 하나의 실행계획이 모든 값에 적합하지 않을 수 있음
  → Bind Peeking과 Adaptive Cursor Sharing으로 보완 가능

핵심 개념은 다음과 같습니다.

Bind Variable
  → SQL Text 공유 가능성 향상

Prepared Statement·Cursor 재사용
  → Parse Call 자체 감소

Adaptive Cursor Sharing
  → Bind 선택도 범위별 Plan 품질 보완

이 이론의 범위

이 이론은 SQLP의 SQL 옵티마이저 → SQL 공유 및 재사용 범위에서 Bind Variable, Bind Peeking, Adaptive Cursor Sharing과 CURSOR_SHARING의 관계를 다룹니다. Histogram 생성 방법, Parent·Child Cursor 공유 실패 Column 전체 목록과 실행계획별 인덱스 내부 동작은 후속 이론에서 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음 내용을 설명할 수 있어야 합니다.

• Literal SQL과 Bind SQL의 Parent Cursor 생성 차이를 설명한다.
• Application Bind와 Prepared Statement 재사용의 역할을 구분한다.
• Bind Variable의 성능·보안 이점과 실행계획 측면의 한계를 설명한다.
• Data Skew가 값별 Access Path 선택에 미치는 영향을 설명한다.
• Bind Peeking이 발생할 수 있는 시점과 Plan 재사용 관계를 설명한다.
• Histogram이 Bind Cardinality 추정에 주는 도움과 한계를 설명한다.
• Bind-Sensitive Cursor가 되는 기본 조건을 설명한다.
• Bind-Aware Cursor의 선택도 범위 재사용과 새 Child 생성 흐름을 설명한다.
• Cursor Merging이 필요한 이유를 설명한다.
• ACS와 CURSOR_SHARING Parameter가 독립적인 기능인 이유를 설명한다.
• V$SQL, V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS, V$SQL_CS_HISTOGRAM의 역할을 구분한다.
• Literal 폭증과 Bind Plan Skew를 서로 다른 문제로 진단한다.

1. Literal SQL과 Bind SQL

1.1 Literal SQL

다음 SQL은 조회 값만 다릅니다.

SELECT *
FROM orders
WHERE customer_id = 101;

SELECT *
FROM orders
WHERE customer_id = 205;

SELECT *
FROM orders
WHERE customer_id = 930;

기본 CURSOR_SHARING=EXACT 환경에서는 SQL Text가 서로 다르므로 별도 Parent Cursor와 SQL_ID가 생성될 수 있습니다.

Literal 101 → Parent A
Literal 205 → Parent B
Literal 930 → Parent C

값과 사용자 수가 많아질수록 다음 문제가 커질 수 있습니다.

• Parent Cursor 증가
• Hard Parse와 Parse CPU 증가
• Shared Pool 메모리 사용 증가
• Library Cache 동시성 비용 증가
• SQL 모니터링과 관리 복잡성 증가

1.2 Bind SQL

Bind Variable은 값이 들어갈 위치를 Placeholder로 두고 실행 시 값을 전달합니다.

SELECT *
FROM orders
WHERE customer_id = :customer_id;

101 ─┐
205 ─┼─→ 동일 SQL Text → 같은 Parent Cursor 후보
930 ─┘

Application에서 Bind API와 Prepared Statement를 올바르게 사용하면 하나의 SQL Text와 Cursor를 여러 값에 재사용할 수 있습니다.

2. Application Bind의 이점과 한계

Bind Variable과 Statement 재사용은 역할이 다릅니다.

Bind Variable
  → 값 변화로 SQL Text가 달라지는 문제를 줄임

Prepared Statement·Cursor 재사용
  → Parse Once·Execute Many 구조로 Parse Call 자체를 줄임

Bind만 사용하고 실행할 때마다 Statement를 새로 준비하면 기존 Plan을 재사용하더라도 Soft Parse가 반복될 수 있습니다.

3. Data Skew가 실행계획 선택을 어렵게 만드는 이유

ORDERS.STATUS의 분포가 다음과 같다고 가정합니다.

SQL은 하나입니다.

SELECT order_id,
       customer_id,
       order_date
FROM orders
WHERE status = :status;

값별로 유리한 Access Path가 달라질 수 있습니다.

ERROR
  → 적은 Index Entry와 ROWID 처리
  → Index Access가 유리할 가능성

COMPLETE
  → 대량 Index Entry와 Table Access by ROWID
  → Full Table Scan이 유리할 가능성

같은 Bind SQL에 하나의 Plan만 계속 사용하면 특정 값에서는 빠르지만 다른 값에서는 매우 느릴 수 있습니다.

4. Bind Peeking

Bind Peeking은 Optimizer가 관련 Hard Parse에서 사용자 Bind 값을 참고해 Predicate의 Selectivity와 Cardinality를 추정하는 기능입니다.

Hard Parse
  → Bind 값 Peeking 가능
  → 값의 Selectivity·Cardinality 추정
  → 실행계획 생성
  → Child Cursor에 저장

중요한 기준은 다음과 같습니다.

• Bind 값을 매 Execute마다 다시 Peeking하는 것은 아닙니다.
• Soft Parse로 기존 Child Cursor를 재사용하면 기존 Plan을 사용합니다.
• 새로운 Child Cursor를 만드는 Hard Parse가 발생하면 그 시점의 Bind 값이 다시 Plan 생성에 사용될 수 있습니다.
• Bind Peeking이 모든 Bind SQL에 항상 적용되는 것은 아닙니다.

4.1 첫 Plan이 희소값에 맞춰진 경우

첫 Hard Parse Bind = ERROR
  → 소량 행으로 추정
  → Index Plan 선택 가능

이후 Bind = COMPLETE
  → 기존 Index Plan 재사용 가능
  → 대량 ROWID Table Access로 느려질 수 있음

4.2 첫 Plan이 인기값에 맞춰진 경우

첫 Hard Parse Bind = COMPLETE
  → 대량 행으로 추정
  → Full Scan Plan 선택 가능

이후 Bind = ERROR
  → 소량 조회에도 Full Scan을 사용할 수 있음

Bind Peeking은 최초 Plan을 값에 맞게 만들 수 있지만 그 Plan이 모든 후속 Bind에 최적이라는 보장은 없습니다.

5. Histogram과 Bind Peeking

Histogram은 Column 값의 빈도 차이를 표현하는 통계정보입니다.

Histogram 없음 또는 분포 정보 부족
  → NDV와 균등 분포 가정 중심
  → 인기값과 희소값의 차이를 충분히 반영하지 못할 수 있음

Histogram 존재 + Hard Parse에서 Bind Peeking
  → 현재 Bind 값의 빈도 차이를 더 구체적으로 추정 가능

다만 다음 사항을 구분해야 합니다.

• Histogram은 값별 Selectivity 추정에 도움을 줍니다.
• Histogram이 있다고 한 Child Plan이 모든 값에 적합해지는 것은 아닙니다.
• Histogram은 Bind-Sensitive Cursor의 필수 조건으로 단정할 수 없습니다.
• ACS는 실행 결과를 관찰해 값별 Plan 필요성을 판단합니다.

Histogram의 유형과 생성·관리 방법은 통계정보 이론에서 다룹니다.

6. Bind-Sensitive Cursor

Bind-Sensitive Cursor는 Bind 값에 따라 최적 Plan이 달라질 가능성이 있다고 Oracle이 판단하여 실행 특성을 관찰하는 Cursor입니다.

대표적인 판단 조건은 다음과 같습니다.

• Optimizer가 Bind 값을 Peeking하여 Cardinality를 계산함
• Bind가 등치 또는 범위 Predicate에 사용됨
• Bind 개수와 형태가 내부 제한을 만족함

Bind-Sensitive
  → 아직 여러 Plan이 확정된 상태가 아님
  → 서로 다른 Bind 실행의 Row 수와 자원 사용을 관찰

V$SQL.IS_BIND_SENSITIVE = 'Y'로 확인할 수 있습니다.

Bind-Sensitive라는 사실만으로 이미 여러 실행계획을 사용한다고 판단하면 안 됩니다.

7. Bind-Aware Cursor와 Adaptive Cursor Sharing

여러 Bind 값의 실행 결과가 크게 다르고 다른 Plan이 유리하다고 판단되면 Cursor가 Bind-Aware 상태가 될 수 있습니다.

Bind-Sensitive 상태
  → 여러 Bind 값의 실행 통계 관찰
  → Cardinality와 Data Access Pattern 차이가 큼
  → Bind-Aware 전환 가능

Bind-Aware Cursor는 새 Bind 값의 선택도를 기존 Child Cursor의 유효 범위와 비교합니다.

새 Bind 값으로 Parse 요청
  → Bind Selectivity 계산
  → 적합한 기존 Child 범위 확인
      ├─ 범위 일치: 기존 Child Plan 재사용
      └─ 범위 불일치: Hard Parse 후 새 Child Plan 생성 가능

전환 과정에서 최초 Cursor가 IS_SHAREABLE='N'으로 표시되어 이후 Aging Out 대상이 될 수 있습니다. 이는 Bind-Aware 상태를 만들기 위한 일시적인 전환 비용일 수 있습니다.

7.1 모든 Bind 값마다 새 Child가 생성되지는 않는다

Oracle은 값 자체가 아니라 선택도 범위와 Plan 적합성을 중심으로 기존 Child를 찾습니다.

희소값 선택도 범위
  → Index Plan Child 재사용

인기값 선택도 범위
  → Full Scan Plan Child 재사용

새 Bind 값의 선택도가 기존 범위에 포함되면 Hard Parse 없이 기존 Plan을 재사용할 수 있습니다. 따라서 Bind 값 수에 비례해 Child가 계속 증가하는 구조는 아닙니다.

8. Cursor Merging

Bind-Aware Cursor가 새 Bind 값을 위해 Hard Parse했지만 새 Plan이 기존 Child Plan과 같을 수 있습니다.

새 Bind 범위
  → 새 Child와 Plan 생성
  → 기존 Child의 Plan과 동일
  → 선택도 범위를 합침
  → 중복 Child를 Not Shareable 처리

이를 Cursor Merging이라고 합니다.

Cursor Merging의 목적은 다음과 같습니다.

• 같은 Plan을 사용하는 선택도 범위를 하나로 통합
• Library Cache의 불필요한 Child Cursor 감소
• 새로운 Bind 값에서 기존 Child를 재사용할 수 있는 범위 확대

IS_SHAREABLE='N'인 과거 Child가 보이더라도 ACS 전환과 Cursor Merging 과정인지 함께 확인해야 합니다.

9. ACS와 CURSOR_SHARING은 서로 다른 기능이다

9.1 CURSOR_SHARING

CURSOR_SHARING=FORCE는 Literal SQL 폭증을 줄이는 전술적 방법이지만 다음 한계가 있습니다.

• Parse Call 자체는 남습니다.
• 유사 SQL을 찾기 위한 Soft Parse 작업이 추가될 수 있습니다.
• 유용한 Literal 정보까지 치환되어 Plan 품질이 나빠질 수 있습니다.
• 모든 문장이 반드시 하나의 Cursor로 합쳐지는 것은 아닙니다.
• SQL Injection 문제를 해결하지 않습니다.
• Application Bind를 대신하는 영구 설계로 권장되지 않습니다.

근본 해결은 Application에서 사용자 정의 Bind를 사용하고 SQL Text와 Statement 수명주기를 관리하는 것입니다.

9.2 ACS와의 관계

Adaptive Cursor Sharing은 CURSOR_SHARING Parameter와 독립적입니다.

CURSOR_SHARING
  → Literal SQL을 얼마나 공유 가능한 Text로 처리할지 결정

Adaptive Cursor Sharing
  → Bind가 있는 SQL에서 현재 Bind 선택도에 적합한 Child Plan을 선택

ACS는 다음 SQL에 모두 적용될 수 있습니다.

• Application이 작성한 사용자 Bind SQL
• CURSOR_SHARING=FORCE가 만든 System-Generated Bind SQL

Bind가 없는 Literal-only SQL에는 ACS가 적용되지 않습니다.

10. ACS 진단 View

10.1 V$SQL: Child 상태와 Plan 사용량

SELECT sql_id,
       child_number,
       plan_hash_value,
       executions,
       is_bind_sensitive,
       is_bind_aware,
       is_shareable,
       buffer_gets,
       cpu_time,
       elapsed_time
FROM v$sql
WHERE sql_id = :sql_id
ORDER BY child_number;

확인 순서는 다음과 같습니다.

1. Child Cursor는 몇 개인가?
2. Child별 PLAN_HASH_VALUE가 다른가?
3. IS_BIND_SENSITIVE, IS_BIND_AWARE가 Y인가?
4. IS_SHAREABLE='N'인 과거 Child가 존재하는가?
5. 각 Child의 EXECUTIONS와 실행당 자원 사용은 어떠한가?

실행당 비용을 비교합니다.

SELECT child_number,
       plan_hash_value,
       executions,
       ROUND(buffer_gets / NULLIF(executions, 0), 1) AS gets_per_exec,
       ROUND(cpu_time / NULLIF(executions, 0) / 1000, 1) AS cpu_ms_per_exec,
       ROUND(elapsed_time / NULLIF(executions, 0) / 1000, 1) AS elapsed_ms_per_exec,
       is_bind_sensitive,
       is_bind_aware,
       is_shareable
FROM v$sql
WHERE sql_id = :sql_id
ORDER BY child_number;

10.2 V$SQL_CS_SELECTIVITY: Child의 선택도 범위

SELECT sql_id,
       child_number,
       predicate,
       range_id,
       low,
       high
FROM v$sql_cs_selectivity
WHERE sql_id = :sql_id
ORDER BY child_number, range_id;

이 View는 Extended Cursor Sharing에서 각 Child가 공유될 수 있는 Bind Predicate의 선택도 Low·High 범위를 보여 줍니다.

10.3 V$SQL_CS_STATISTICS: Bind-Aware 판단용 실행 통계

V$SQL_CS_STATISTICS는 Oracle이 Bind-Aware 여부를 판단할 때 참고하는 표본 실행의 처리 행 수, Buffer Gets와 CPU 정보 등을 요약합니다.

확인할 수 있는 대표 내용은 다음과 같습니다.

• Bind Set이 Cursor 생성에 사용되었는지
• 처리한 Row 수
• Buffer Gets
• CPU 사용량

10.4 V$SQL_CS_HISTOGRAM: 실행 이력 분포

V$SQL_CS_HISTOGRAM은 ACS가 Extended Cursor Sharing을 활성화할지 판단할 때 사용하는 실행 횟수의 Bucket 분포를 보여 줍니다.

Child Cursor가 여러 개라는 사실만으로 ACS라고 단정하면 안 됩니다. Bind Metadata·Optimizer 환경·권한·객체 차이도 Child 증가 원인이 될 수 있으므로 V$SQL_SHARED_CURSOR와 함께 확인합니다.

11. 문제 유형별 대응 방향

11.1 Literal SQL 폭증

현상
  → 유사 SQL이 여러 SQL_ID로 생성
  → 각 Parent의 VERSION_COUNT는 작을 수 있음

개선
  → Application Bind API 적용
  → SQL Text 생성 규칙 통일
  → Prepared Statement 재사용
  → 필요 시 FORCE를 임시·Session 범위에서 검증

11.2 Bind SQL의 값별 성능 편차

현상
  → 한 SQL_ID 아래 여러 Child와 Plan
  → Bind 값별 평균 Buffer·Elapsed 차이

확인
  → Data Skew·Histogram
  → Bind Peeking Plan
  → IS_BIND_SENSITIVE·IS_BIND_AWARE
  → Selectivity Range
  → Child별 실제 사용량

11.3 불필요한 Child Cursor 증가

현상
  → 같은 Plan Hash의 Child가 많이 생성
  → BIND_MISMATCH·환경 차이 발생

개선
  → Bind Type·Length·Character Set 통일
  → Connection Pool Session 설정 통일
  → V$SQL_SHARED_CURSOR로 ACS 이외의 원인 분리

12. 자주 혼동하는 판단과 정확한 기준

13. 핵심 정리

Literal SQL 폭증
  → 여러 SQL Text와 Parent Cursor 문제

Application Bind
  → SQL Text 공유와 보안 개선

Bind Peeking
  → 관련 Hard Parse에서 현재 Bind 값으로 최초 Plan 추정

Bind-Sensitive
  → 값별 성능 차이를 관찰

Bind-Aware
  → 선택도 범위별 Child Plan 사용

Cursor Merging
  → 같은 Plan의 선택도 범위를 통합

CURSOR_SHARING=FORCE
  → Literal을 System Bind로 치환하는 임시 완화책
  → ACS와 독립적인 기능

가장 중요한 진단 기준은 다음과 같습니다.

유사 SQL이 여러 SQL_ID로 나뉘는가?
  → Literal·SQL Text 공유 문제

한 SQL_ID 아래 값별 Plan과 비용이 다른가?
  → Bind Peeking·Data Skew·ACS 문제

같은 Plan Child가 불필요하게 많은가?
  → Bind Metadata·환경 공유 실패 문제