적응형 최적화: Adaptive Plan·Statistics Feedback·Adaptive Cursor Sharing

핵심 요약

옵티마이저는 SQL이 실제로 수행되기 전에 통계정보와 Bind 값을 이용해 처리 행 수와 Cost를 추정합니다. 이 추정이 실제와 크게 다르면 부적절한 Access Path나 Join Method가 선택될 수 있습니다.

Oracle은 실행 중 또는 반복 실행에서 얻은 정보를 이용해 이러한 추정 한계를 보완합니다.

세 기능을 가장 간단히 구분하면 다음과 같습니다.

현재 실행에서 준비된 대안을 선택한다
  → Adaptive Query Plan

첫 실행의 추정 오류를 다음 최적화에 활용한다
  → Statistics Feedback

Bind 값의 선택도 범위별로 적합한 Child Cursor를 사용한다
  → Adaptive Cursor Sharing

적응형 기능은 통계·SQL·데이터 모델 문제를 없애는 기능이 아닙니다. 반복적인 재최적화나 Child Cursor 증가가 나타난다면 오래된 통계, 데이터 편중, 컬럼 상관관계, 복잡한 표현식, Bind 설계와 물리 구조를 함께 점검해야 합니다.

이 이론의 범위

이 이론은 SQLP의 SQL 옵티마이저 → SQL 옵티마이징 원리 범위에서 Adaptive Query Plan, Statistics Feedback, Adaptive Cursor Sharing의 핵심 차이를 다룹니다. Dynamic Statistics, SQL Plan Directive, Histogram·Extended Statistics 수집 방법, Child Cursor 공유 실패의 세부 원인은 후속 이론에서 다룹니다.

학습 목표

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

• 적응형 최적화가 필요한 이유를 설명한다.
• Default Plan, Adaptive Plan, Final Plan의 관계를 설명한다.
• Adaptive Query Plan이 현재 실행에서 선택할 수 있는 범위를 설명한다.
• Statistics Feedback이 첫 실행을 소급하여 개선하지 못하는 이유를 설명한다.
• Statistics Feedback과 Object Statistics의 차이를 설명한다.
• Bind-Sensitive Cursor와 Bind-Aware Cursor를 구분한다.
• Bind-Aware Cursor가 모든 Bind 값마다 새 Plan을 만드는 것은 아닌 이유를 설명한다.
• Adaptive Cursor Sharing과 CURSOR_SHARING Parameter를 구분한다.
• 실제 Cursor에서 적응형 기능의 적용 여부를 확인한다.
• Oracle 버전과 Parameter에 따라 기능 활성화 범위가 달라지는 점을 설명한다.
• 반복적인 적응형 보완이 발생할 때 근본 원인을 점검한다.

1. 적응형 최적화가 필요한 이유

실행계획은 실제 SQL이 완료되기 전에 만들어집니다.

최적화 시점
  → 통계와 Bind 정보를 이용해 예상 Cardinality와 Cost 계산
  → 실행계획 선택

실행 시점
  → 실제 데이터와 실제 Bind 값으로 Row Source 수행
  → 실제 Cardinality와 자원 사용량 확인

다음 상황에서는 예상과 실제가 크게 달라질 수 있습니다.

• 통계정보가 오래되었거나 누락됨
• 특정 값이 전체 데이터의 대부분을 차지함
• 두 컬럼이 강하게 연관되어 있으나 독립 조건으로 추정됨
• 함수·표현식의 결과 분포를 정확히 알기 어려움
• 데이터량과 분포가 급격히 변함
• Bind 값별 조건 결과 행 수가 크게 다름
• 하위 Operation의 작은 추정 오류가 상위 Join에서 확대됨

적응형 최적화는 실행 중 수집한 실제 정보를 이용해 현재 실행의 일부 결정을 조정하거나, 이후 실행의 추정과 Plan 선택을 개선합니다.

2. 세 기능 비교

3. Adaptive Query Plan: 현재 실행에서 준비된 대안 선택

Adaptive Query Plan은 최초 최적화 시점에 일부 지점에 대안 Subplan과 Optimizer Statistics Collector를 포함합니다.

Adaptive Plan 후보
  ├─ Subplan A: Nested Loops Join
  └─ Subplan B: Hash Join
           ↑
  Statistics Collector가 실제 Row 수 관찰

3.1 Default Plan, Adaptive Plan, Final Plan

첫 실행의 기본 흐름은 다음과 같습니다.

1. Default Plan으로 실행 시작
2. Statistics Collector가 중간 Row 수를 수집하고 일부 행을 Buffering
3. 수집값과 내부 Threshold를 비교해 Subplan 선택
4. 선택하지 않은 Row Source를 비활성화
5. 선택 결과를 포함한 Adaptive Plan을 Child Cursor에 저장
6. 후속 실행은 특별한 무효화 사유가 없다면 저장된 Plan을 재사용

따라서 Adaptive Query Plan은 실행 중 완전히 새로운 임의의 Plan을 무제한 생성하는 기능이 아닙니다. 최초 최적화에서 준비한 대안 범위 안에서 선택합니다.

3.2 Join Method 예시

옵티마이저가 선행 Row Source를 100행으로 예상했다고 가정합니다.

예상 100행
  → Nested Loops Join 후보가 유리

실행 중 실제 Row 수가 내부 Threshold를 크게 초과하면 준비된 Hash Join Subplan을 선택할 수 있습니다.

실제 500,000행
  → 준비된 Hash Join Subplan 선택
  → Nested Loops 관련 Row Source 비활성화

Adaptive Query Plan은 Join Method뿐 아니라 버전과 설정에 따라 병렬 분배 방식이나 Star Transformation의 Bitmap Index Pruning 같은 결정에도 사용될 수 있습니다.

3.3 확인 방법

실제 실행에 사용된 SQL ID와 Child Number를 지정하는 것이 안전합니다.

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id          => :sql_id,
        cursor_child_no => :child_no,
        format          => 'ADAPTIVE'
    )
);

확인 항목은 다음과 같습니다.

• Plan Note의 this is an adaptive plan
• - 표시가 붙은 inactive Row Source
• Statistics Collector
• Default Plan의 대안 구조
• 실제 선택된 Final Plan
• SQL ID와 Child Number

실제 Row 수까지 함께 보려면 실행 통계가 수집된 상태에서 ALLSTATS LAST ADAPTIVE 형식을 사용할 수 있습니다.

4. Statistics Feedback: 다음 최적화의 Cardinality 보정

Statistics Feedback은 반복 실행되는 SQL에서 예상 Cardinality와 실제 Cardinality의 차이가 클 때 이후 Plan을 개선하는 Automatic Reoptimization 기능입니다.

첫 실행
  → 실행계획 생성
  → E-Rows와 A-Rows 비교
  → 차이가 크면 보정 정보 저장
  → Statement를 재최적화 대상으로 표시

이후 재최적화
  → 보정 Cardinality 사용
  → Cost 재계산
  → 다른 실행계획 선택 가능

4.1 첫 실행을 소급하여 개선할 수 없는 이유

Feedback은 첫 실행이 끝나면서 생성됩니다. 이미 종료된 실행의 Join Order나 Access Path를 과거로 되돌려 변경할 수 없습니다.

현재 실행 도중 준비된 대안을 선택
  → Adaptive Query Plan

현재 실행 결과를 이후 최적화에 사용
  → Statistics Feedback

두 번째 실행이 무조건 새 Plan을 사용한다고 단정해서는 안 됩니다. Feedback을 사용하는 재최적화가 발생하고 해당 Cursor와 환경에서 보정값이 적용되어야 합니다.

4.2 Feedback이 발생할 수 있는 상황

• 통계가 없거나 오래됨
• 한 테이블에 여러 AND·OR 조건이 결합됨
• 복잡한 연산자와 표현식으로 선택도 추정이 어려움
• 여러 Join에서 추정 오류가 누적됨
• 컬럼 상관관계와 데이터 편중을 기본 통계가 충분히 반영하지 못함

4.3 Object Statistics와의 차이

Feedback이 반복적으로 필요하다면 다음 근본 대안을 검토합니다.

• 최신 Object Statistics
• 편중 컬럼 Histogram
• 상관관계 컬럼의 Column Group Statistics
• 함수·표현식의 Expression Statistics
• SQL 조건과 Join 구조 개선
• 적절한 인덱스·파티션 구조

4.4 Parameter 해석 주의

최신 Oracle 계열에서는 Adaptive Plan과 Adaptive Statistics의 제어가 분리되어 있습니다.

OPTIMIZER_ADAPTIVE_PLANS
  → Adaptive Query Plan 제어

OPTIMIZER_ADAPTIVE_STATISTICS
  → Adaptive Statistics의 일부 기능 제어

OPTIMIZER_ADAPTIVE_STATISTICS=FALSE라고 해서 모든 Statistics Feedback이 완전히 비활성화되는 것은 아닙니다. Join Cardinality Feedback과 Single-Table Cardinality Feedback의 적용 범위가 다를 수 있으므로, 실제 버전의 공식 문서와 Cursor Note를 확인해야 합니다.

5. Adaptive Cursor Sharing: Bind 선택도 범위별 Plan 선택

Bind 변수는 동일 SQL Text를 공유하여 Parse 비용을 줄이는 데 유리합니다. 하지만 데이터 편중이 크면 하나의 Plan이 모든 Bind 값에 적합하지 않을 수 있습니다.

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

COMPLETED  9,000,000행
CANCELLED      5,000행

CANCELLED
  → 소량 결과
  → Index Access가 유리할 수 있음

COMPLETED
  → 대량 결과
  → Full Table Scan이 유리할 수 있음

5.1 Bind-Sensitive Cursor

Bind-Sensitive Cursor는 다음 의미를 가집니다.

이 SQL의 최적 Plan이 Bind 값에 따라 달라질 가능성이 있음
  → 서로 다른 Bind 실행의 처리 특성을 관찰

대표적으로 옵티마이저가 Bind 값을 Peeking하여 Cardinality를 계산하고, 해당 Bind가 등치 또는 범위 조건에 사용될 때 Bind-Sensitive 후보가 될 수 있습니다.

Bind-Sensitive라고 해서 이미 여러 Plan을 사용하고 있다는 뜻은 아닙니다. 우선 실행 특성을 관찰하는 단계입니다.

5.2 Bind-Aware Cursor

여러 Bind 값의 실행 통계와 데이터 접근 특성이 크게 다르면 Cursor가 Bind-Aware 상태가 될 수 있습니다.

Bind-Aware
  → 현재 Bind 값의 선택도 범위를 확인
  → 적합한 기존 Child Cursor가 있으면 재사용
  → 적합한 Child Cursor가 없으면 Hard Parse 후 새 Plan 생성 가능

Oracle은 모든 Bind 값마다 별도 Child Cursor를 만드는 것이 아닙니다. 비슷한 선택도 범위에는 기존 Plan을 재사용할 수 있으며, 새 Plan이 기존 Plan과 같으면 Cursor Merging을 통해 선택도 범위를 합칠 수 있습니다.

5.3 Bind 선택도 범위 예시

희소값 범위
  → Index Plan Child Cursor

인기값 범위
  → Full Scan Plan Child Cursor

새 Bind 값
  → 기존 선택도 범위에 맞으면 해당 Child 재사용
  → 맞는 범위가 없을 때만 새 Hard Parse 가능

5.4 CURSOR_SHARING과의 차이

두 기능은 관련될 수 있지만 동일한 기능은 아닙니다.

6. 하나의 상황으로 세 기능 구분하기

다음 SQL을 가정합니다.

SELECT o.order_id,
       o.order_date,
       o.total_amount
FROM orders o
WHERE o.customer_grade = :grade;

6.1 Adaptive Query Plan

현재 실행에서 실제 선행 Row 수가 예상보다 많다는 것을 확인하고, 최초 계획에 포함된 Nested Loops와 Hash Join 대안 중 Hash Join을 선택합니다.

6.2 Statistics Feedback

첫 실행의 E-Rows가 1,000행이고 A-Rows가 500,000행이었다면 이 차이를 저장하여 이후 재최적화에서 더 현실적인 Cardinality를 사용합니다.

6.3 Adaptive Cursor Sharing

여러 실행을 관찰한 결과 VIP와 NORMAL 값의 선택도 범위가 크게 다르면 각 범위에 맞는 Child Cursor와 Plan을 사용합니다.

현재 실행의 대안 선택
  → Adaptive Query Plan

다음 최적화의 추정 보정
  → Statistics Feedback

Bind 선택도 범위별 Child Plan
  → Adaptive Cursor Sharing

7. 확인 방법

7.1 Adaptive Query Plan과 Statistics Feedback

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id          => :sql_id,
        cursor_child_no => :child_no,
        format          => 'ALLSTATS LAST ADAPTIVE'
    )
);

실행 통계가 수집된 경우 다음 항목을 확인합니다.

• E-Rows와 A-Rows
• Adaptive Plan Note
• inactive Row Source
• Statistics Collector
• statistics feedback used for this statement
• 재최적화 전후 Plan 변화

ALLSTATS LAST에서 실제 통계를 보려면 GATHER_PLAN_STATISTICS Hint 또는 STATISTICS_LEVEL=ALL 등으로 Plan Statistics가 수집되어 있어야 합니다.

7.2 Bind-Sensitive·Bind-Aware 상태

V$SQL에서 다음 항목을 확인할 수 있습니다.

• IS_BIND_SENSITIVE
• IS_BIND_AWARE
• 동일 SQL_ID의 CHILD_NUMBER
• Child별 PLAN_HASH_VALUE
• Child별 실행 횟수와 작업량

추가로 다음 View를 사용할 수 있습니다.

Child Cursor가 여러 개라는 사실만으로 ACS라고 단정하면 안 됩니다. 다음 원인도 함께 확인합니다.

• Bind 데이터 타입·길이 차이
• Optimizer 환경 차이
• NLS 설정 차이
• Parsing Schema와 권한 차이
• 객체·통계 무효화
• 병렬 관련 설정 차이

8. 적응형 기능과 근본 해결책 구분

적응형 기능은 안전망이지 근본 통계 품질을 대신하는 기능이 아닙니다.

가능한 개선 방법은 다음과 같습니다.

• 데이터 변화에 맞는 통계 수집 주기
• 편중 컬럼 Histogram
• 상관관계 컬럼의 Column Group Statistics
• 함수·표현식의 Expression Statistics
• SQL 조건과 Join 구조 단순화
• 대표 Bind와 편중 Bind의 업무 분리
• 적절한 인덱스·파티션 설계
• Partition·Global Statistics 일관성 관리

9. Oracle 버전과 설정에 따른 차이

Adaptive 기능은 Oracle 버전과 Patch 수준에 따라 기능 범위와 기본 활성화 여부가 달라질 수 있습니다.

9.1 Adaptive Query Plan

최신 Oracle 계열에서 Adaptive Query Plan은 일반적으로 다음 조건의 영향을 받습니다.

• OPTIMIZER_ADAPTIVE_PLANS
• OPTIMIZER_FEATURES_ENABLE
• OPTIMIZER_ADAPTIVE_REPORTING_ONLY
• 현재 Database Release와 Patch 수준

OPTIMIZER_ADAPTIVE_PLANS의 기본값은 최신 문서 기준으로 TRUE이며, 실제 동작 여부는 현재 설정과 Cursor Plan을 확인해야 합니다.

9.2 Adaptive Statistics

OPTIMIZER_ADAPTIVE_STATISTICS의 최신 기본값은 FALSE입니다. 이 Parameter는 Adaptive Statistics의 일부 기능을 제어하지만 모든 Statistics Feedback을 동일하게 제어하지는 않습니다.

Parameter 이름만 보고 기능 상태를 단정하지 않는다.
  → Database Release 확인
  → 실제 Parameter 확인
  → Cursor Note와 Final Plan 확인

SQLP 학습에서는 기능의 핵심 시점을 먼저 구분하고, 실무에서는 해당 버전의 Oracle 공식 문서를 기준으로 확인합니다.

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

11. 핵심 정리

Adaptive Query Plan
  → 현재 실행의 실제 Row 수를 이용한다.
  → 최초 최적화에서 준비한 대안 Subplan 중 선택한다.
  → 선택된 Final Plan은 Child Cursor에 저장될 수 있다.

Statistics Feedback
  → 첫 실행의 E-Rows와 A-Rows 차이를 수집한다.
  → 이후 재최적화에서 Cardinality를 보정한다.
  → Object Statistics를 직접 변경하지 않는다.

Adaptive Cursor Sharing
  → Bind 값별 선택도와 실행 특성을 관찰한다.
  → 선택도 범위에 맞는 Child Cursor를 재사용하거나 새 Plan을 생성한다.
  → 모든 Bind 값마다 Child를 만드는 것은 아니다.

적응형 최적화의 목적은 추정 오류를 숨기는 것이 아닙니다. 실행에서 얻은 실제 정보를 이용해 계획 선택의 한계를 보완하고, 반복적인 보정이 필요하면 통계와 SQL 구조의 근본 원인을 개선하는 것입니다.