Selectivity·Cardinality·Cost: 통계 추정과 Plan 선택 진단

핵심 요약

Cost-Based Optimizer는 조건이 얼마나 많은 Row를 선택할지 먼저 추정하고, 그 결과를 바탕으로 Access Path와 Join Plan의 작업량을 비교합니다.

Selectivity
= 입력 Row 중 조건을 통과할 것으로 예상한 비율

Cardinality
= 각 Row Source가 반환할 것으로 예상한 Row 수

Cost
= 예상 Cardinality를 바탕으로 계산한 I/O·CPU·메모리 작업량의 내부 비교값

세 값은 독립된 암기 항목이 아니라 다음과 같이 연결됩니다.

Predicate와 Statistics
→ Selectivity
→ Cardinality
→ Access·Join·Sort 작업량
→ Cost
→ 실행계획 선택

Cardinality가 틀리면 뒤의 Cost 계산도 연쇄적으로 틀릴 수 있으므로, SQLP 튜닝에서는 E-Rows와 A-Rows가 처음 크게 갈라지는 지점을 찾는 것이 중요합니다.

Selectivity
→ Predicate 통과 비율

Cardinality
→ 단계별 예상 Row 수

Cost
→ 예상 I/O·CPU·Memory 작업량

Plan 선택
→ 같은 SQL·Optimizer 환경의 후보 비교

이 이론의 범위

SQLP SQL 고급 활용 및 튜닝 → 통계정보 범위에서 Selectivity, Cardinality, Histogram·Extended Statistics, Join 추정, Cost와 실제 실행 통계 진단을 다룹니다.

학습 목표

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

• Selectivity의 범위와 “선택도가 높다”라는 표현의 혼동을 피한다.
• 단순 등치 조건의 Cardinality를 NDV로 계산한다.
• NULL, Histogram, 범위 조건, Column 상관관계가 단순 공식을 바꾸는 이유를 설명한다.
• 각 실행계획 Operation마다 Cardinality가 존재한다는 점을 이해한다.
• Cardinality 과소·과대 추정이 Join Order와 Join Method에 미치는 영향을 설명한다.
• Starts, E-Rows, A-Rows를 같은 단위로 비교한다.
• Cost를 실제 시간이나 서로 다른 SQL의 절대 성능값으로 해석하지 않는다.
• Frequency·Top-Frequency·Hybrid Histogram의 목적을 구분한다.
• Column Group·Expression Statistics와 Dynamic Statistics의 역할을 설명한다.
• Bind Peeking·Adaptive Cursor Sharing·Statistics Feedback이 추정에 미치는 영향을 이해한다.

1. Selectivity

Selectivity는 입력 Row 집합에서 Predicate를 통과할 것으로 예상하는 비율입니다.

0.0에 가까움 → 적은 Row를 선택함
1.0에 가까움 → 많은 Row를 선택함

예를 들어 1,000,000행 중 10,000행을 선택할 것으로 예상한다면 다음과 같습니다.

Selectivity = 10,000 ÷ 1,000,000 = 0.01 = 1%

실무에서 “선택도가 높다”를 적은 Row를 잘 골라낸다는 뜻으로 쓰기도 하고, Selectivity 수치가 크다는 뜻으로 쓰기도 합니다. 혼동을 피하려면 다음처럼 표현하는 것이 안전합니다.

• 조건이 매우 선택적이다: 결과 비율이 작다.
• Selectivity 값이 크다: 결과 비율이 크다.

Selectivity는 Optimizer 내부 계산값이므로 일반 실행계획 Column에 직접 표시되지 않습니다. Plan에는 Selectivity가 입력 Row 수에 적용된 결과인 예상 Rows·E-Rows가 나타납니다.

2. Cardinality

Cardinality는 실행계획의 각 Operation이 반환할 것으로 예상한 Row 수입니다.

Cardinality = 입력 Row 수 × Selectivity

Cardinality는 Table 전체 Row 수만 뜻하지 않습니다. 다음 모든 단계에 예상 Row 수가 존재합니다.

• Table Scan 결과
• Index Scan이 만든 후보 ROWID
• Join 결과
• GROUP BY 결과 Group 수
• DISTINCT 결과
• Subquery·View 결과
• Sort·Window Operation의 입력과 출력

실행계획의 Rows 또는 E-Rows 열이 예상 Cardinality를 보여 줍니다.

Table의 NUM_ROWS는 Base Object Statistics이고, Cardinality는 Predicate·Join·GROUP BY·DISTINCT 등을 반영한 Operation별 추정값입니다.

3. Histogram이 없는 단순 등치 조건

다음 통계를 가정합니다.

EMPLOYEES.NUM_ROWS     = 1,000,000
DEPARTMENT_ID.NDV      = 100
NUM_NULLS              = 0
Histogram              = NONE
Predicate              = department_id = 30

값이 균등하게 분포한다고 단순 가정하면 다음과 같습니다.

Selectivity ≈ 1 / 100 = 0.01
Cardinality ≈ 1,000,000 × 0.01 = 10,000

실제 department_id=30 Row가 300,000건이면 Optimizer의 예상은 30배 작습니다. NDV는 값의 종류 수만 알려 줄 뿐, 각 값이 몇 번 등장하는지는 알려 주지 않기 때문입니다.

NULL이 존재하는 경우

NUM_ROWS  = 1,000,000
NUM_NULLS =   200,000
NDV       =       100

개념적으로 Non-NULL Row는 800,000건이므로 값 하나의 평균은 약 8,000건입니다.

Non-NULL Row = 1,000,000 - 200,000
예상 Row     ≈ 800,000 ÷ 100 = 8,000

이 계산은 원리를 이해하기 위한 Non-NULL 평균입니다. 실제 Optimizer는 Density, Histogram, Bind Peeking, 데이터 타입 변환과 Predicate 종류 등 추가 요소를 사용합니다.

1 / NDV
→ Histogram이 없는 단순 등치 조건의 출발점

(NUM_ROWS - NUM_NULLS) / NDV
→ Non-NULL 값의 개념적 평균 Row 수

Oracle의 실제 내부 계산을 모든 상황에서 이 두 공식으로 고정하지 않습니다.

3.1 Histogram과 데이터 편중

Histogram은 Column 값의 빈도 분포를 Bucket으로 저장해 균등 분포 가정을 보완합니다.

Frequency·Hybrid Histogram에서 인기값은 실제 Bucket 빈도에 가까운 추정이 가능하고, 비인기값은 Density를 이용할 수 있습니다.

인기값 Cardinality
≈ NUM_ROWS × 값이 차지한 Endpoint 비율

비인기값 Cardinality
≈ NUM_ROWS × Density

SIZE AUTO는 Query Workload의 Column 사용 이력을 참고해 필요한 Histogram을 자동 선택할 수 있습니다. Histogram은 모든 Column에 많을수록 좋은 것이 아니며 수집 비용·Plan 변동·Bind 처리 영향을 함께 고려합니다.

4. Predicate 종류에 따라 추정 방법이 달라진다

1/NDV는 Histogram이 없는 단순 등치 조건을 이해하기 위한 출발점입니다. 이 공식은 Histogram이 없는 단순 등치 조건에 한정해 사용하며, Predicate 종류에 맞는 추정 방식을 적용합니다.

통계가 없거나 오래됐거나 Predicate가 복잡해 충분하지 않으면 Optimizer는 Dynamic Statistics 또는 Predicate 유형별 내부 기본값을 사용할 수 있습니다.

Dynamic Statistics는 Parse 시 일부 Block을 Sample하여 Persistent Statistics를 보완합니다. 정식 Object Statistics를 영구적으로 대체하는 기능은 아닙니다.

5. 여러 Predicate와 독립성 가정

다음 두 조건이 있다고 가정합니다.

WHERE country_code = 'KR'
AND   city_name    = 'SEOUL'

개별 통계가 다음과 같다고 가정합니다.

country_code='KR' Selectivity = 1/100
city_name='SEOUL' Selectivity  = 1/1,000

두 조건이 서로 독립이라고 단순 가정하면 다음처럼 곱할 수 있습니다.

결합 Selectivity ≈ 1/100 × 1/1,000 = 1/100,000

하지만 도시와 국가는 강한 상관관계를 가집니다. SEOUL인 Row는 대부분 KR이므로 두 조건을 독립적으로 곱하면 결과를 지나치게 작게 추정할 수 있습니다.

이런 경우 Column Group Statistics는 (country_code, city_name) 조합을 하나의 통계 단위로 취급해 결합 Cardinality 추정을 개선합니다.

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(
           ownname   => 'APP',
           tabname   => 'ADDRESS',
           extension => '(COUNTRY_CODE,CITY_NAME)'
       )
FROM dual;

함수·표현식 조건은 Expression Statistics 후보입니다.

WHERE UPPER(customer_name) = 'KIM'

AND 조건의 독립 곱뿐 아니라 OR 조건의 단순 합산도 겹침 영역을 중복 계산해 오차가 날 수 있습니다.

다음과 같은 조건도 단순 독립 곱셈이 부정확할 수 있습니다.

• order_date와 fiscal_year
• postal_code와 city
• product_category와 product_subcategory
• status와 completed_date IS NOT NULL
• 같은 Column에 적용된 중복·포함 관계 조건

5.1 Bind 값과 Cursor별 추정

Hard Parse 시 Bind Peeking이 가능하면 Optimizer는 최초 Bind 값을 참고해 Selectivity를 추정할 수 있습니다.

첫 Bind = 희소 값
→ Index Plan 후보

후속 Bind = 인기 값
→ 같은 Plan이 비효율적일 수 있음

Adaptive Cursor Sharing은 Bind 값 범위에 따라 여러 Child Cursor·Plan을 사용할 수 있습니다. 다음을 함께 확인합니다.

• PEEKED_BINDS
• IS_BIND_SENSITIVE
• IS_BIND_AWARE
• Child Cursor별 Plan Hash·E-Rows·A-Rows

Histogram이 있어도 Bind를 사용하는 모든 실행이 자동으로 최적 Plan을 얻는다고 단정하지 않습니다.

6. Join Cardinality

Join의 비용은 각 입력 집합의 크기와 Join 결과 Row 수에 크게 좌우됩니다.

Outer Row 수
× Inner 접근 비용
→ NL Join 반복량

Build Row 수
→ Hash Area 크기와 Spill 가능성

양쪽 입력 Row 수
→ Sort Merge Join의 Sort·Merge 작업량

예를 들어 Optimizer가 Driving Set을 10건으로 예상해 NL Join을 선택했는데 실제로 500,000건이라면 Inner Index Probe가 500,000번 가까이 반복될 수 있습니다.

반대로 실제 100건인 집합을 1,000,000건으로 예상하면 작은 Index Probe 대신 Full Scan·Hash Join·대규모 Sort를 선택할 수 있습니다.

Join Cardinality는 다음 정보에 영향을 받습니다.

• 양쪽 입력 Cardinality
• Join Key의 NDV와 중복도
• PK·UK·FK·NOT NULL 같은 Schema Constraint
• Constraint의 Enabled·Validated·Rely 상태
• Join Column의 NULL과 데이터 편중
• 여러 Join Predicate 사이의 상관관계
• Filter가 Join 전후 어느 단계에 적용되는지
• Partition·Global Statistics와 Dynamic Statistics

개념적으로 PK·FK Join에서 모든 FK가 유효하고 Filter가 없다면 Child Row 수와 비슷한 Join Cardinality를 기대할 수 있습니다. 반면 Many-to-Many Join은 양쪽 중복의 곱으로 Fan-out이 크게 증가할 수 있습니다.

Table A의 Key 한 값당 평균 100 Row
Table B의 같은 Key당 평균 50 Row
→ 해당 Key Join 결과 약 5,000 Row 가능

NDV만으로 값별 중복 편중을 충분히 설명하지 못하면 Join Cardinality가 크게 틀릴 수 있습니다.

7. Cost

Cost는 예상 Cardinality를 바탕으로 후보 실행계획이 소비할 I/O, CPU, Memory 작업을 내부 단위로 환산한 값입니다.

Cost에는 Table·Index Block 수, Clustering Factor, Single·Multiblock I/O, System Statistics, Row Width와 Workarea 예상 등이 영향을 줄 수 있습니다.

Cardinality가 작게 추정됨
→ Index Probe·NL Join·작은 Sort가 저렴하게 보일 수 있음

Cardinality가 크게 추정됨
→ Full Scan·Hash Join·병렬 처리·큰 Sort가 유리하게 보일 수 있음

Cost는 다음 범위에서 해석합니다.

• 같은 SQL의 후보 Access Path 비교
• 같은 Query Block의 Join Order·Join Method 비교
• 같은 Optimizer 환경에서의 Plan 선택 이유 분석

다음 비교에는 사용할 수 없습니다.

• SQL A의 Cost 100과 SQL B의 Cost 200을 보고 A가 두 배 빠르다고 판단
• Cost를 실제 초 단위 시간으로 변환
• 다른 Optimizer Mode·System Statistics 환경의 Cost를 절대값으로 비교
• Cost를 직접 수정하거나 “Cost만 낮추는 튜닝”을 목표로 설정
• Cost만 보고 Cache·Lock·동시성·네트워크 병목을 설명

Cost는 실행계획을 고르는 입력이고 실제 성능은 Buffers, Reads, CPU, Elapsed Time, Wait Event로 검증합니다.

8. Cardinality 오차가 전파되는 과정

다음 SQL을 생각해 봅니다.

SELECT /*+ gather_plan_statistics */
       c.customer_name,
       o.order_id,
       o.amount
FROM   customers c
JOIN   orders o
  ON   o.customer_id = c.customer_id
WHERE  o.status = 'ERROR';

실제 데이터가 다음과 같다고 가정합니다.

ORDERS Row       = 10,000,000
STATUS NDV       = 4
ERROR 실제 Row   = 5,000
Histogram        = NONE

균등 분포 가정에서는 ERROR를 약 2,500,000건으로 추정할 수 있습니다.

잘못된 STATUS 추정
→ ORDERS Filter Cardinality 과대 추정
→ CUSTOMERS와 Join 결과도 과대 추정
→ Full Scan·Hash Join 후보 Cost가 상대적으로 유리
→ 큰 Workarea·병렬 후보까지 검토

반대로 실제 인기 값을 희소 값으로 과소 추정하면 NL Join과 반복 Table Access가 선택될 수 있습니다.

핵심은 최종 Operation의 Row 수만 보는 것이 아니라 가장 아래에서 최초로 발생한 추정 오차가 상위 Operation에 어떻게 전파되는지 추적하는 것입니다.

Cardinality뿐 아니라 예상 Row Width·Bytes 오차도 Hash Area·Sort Workarea·Network·Parallel Distribution Cost에 영향을 줄 수 있습니다.

9. Starts, E-Rows, A-Rows 비교

반복 Operation에서는 비교 단위를 맞춰야 합니다.

E-Rows  = 해당 Operation의 예상 Row, 반복 Row Source에서는 보통 Start당 해석
Starts  = 해당 Row Source가 실제 시작된 횟수
A-Rows  = 표시된 실행에서 실제 반환한 누적 Row

개념적으로 다음 두 방법 중 하나를 사용합니다.

예상 총 Row ≈ E-Rows × Starts
실제 총 Row = A-Rows

또는

예상 1회당 Row = E-Rows
실제 1회당 Row ≈ A-Rows ÷ Starts

예시:

| Operation             | Starts | E-Rows | A-Rows |
|-----------------------|--------|--------|--------|
| INDEX RANGE SCAN      |  1,000 |      1 | 50,000 |

예상 총 Row = 1 × 1,000 = 1,000
실제 총 Row = 50,000
실제 1회 평균 = 50,000 ÷ 1,000 = 50

Inner Index Probe가 한 번당 1건이라고 예상했지만 실제로는 평균 50건을 반환한 것입니다. 이 오차가 NL Join 반복 비용을 크게 늘릴 수 있습니다.

Starts=0인 비선택 Adaptive Branch나 실행되지 않은 Operation은 E-Rows가 있어도 실제 작업하지 않았을 수 있습니다. 반대로 일부 Row만 Fetch했다면 Starts·A-Rows가 전체 결과 기준이 아닐 수 있습니다.

10. 실제 추정 오차 진단

SELECT /*+ gather_plan_statistics */
       order_id, customer_id, amount
FROM   orders
WHERE  status = :status
AND    region = :region;

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

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

1. 실제 Bind 값과 Child Cursor를 확정한다.
2. 아래쪽 Operation부터 Starts·E-Rows·A-Rows를 비교한다.
3. 최초 오차 지점의 Predicate를 확인한다.
4. NDV·NULL·Histogram·통계 수집 시점을 확인한다.
5. 여러 Column이면 상관관계와 Column Group을 확인한다.
6. 함수 조건이면 Expression Statistics를 확인한다.
7. 통계가 없거나 오래됐거나 복잡한 조건이면 Dynamic Statistics 사용 여부를 확인한다.
8. Note에서 Dynamic Statistics·Statistics Feedback·Adaptive Plan 사용을 확인한다.
9. Partition별·Global Statistics와 Incremental Statistics 상태를 확인한다.
10. 수정 후 Join Order·Access Path·Buffers·Reads·Memory·Elapsed Time을 재검증한다.

10.1 Statistics Feedback과 후속 실행

첫 실행에서 E-Rows와 A-Rows가 크게 다르면 Optimizer는 Statistics Feedback을 저장하여 다음 Parse·실행에서 보정된 Cardinality를 사용할 수 있습니다.

첫 실행
→ 기존 통계로 Plan 생성
→ 실제 Row와 큰 차이 관찰

후속 실행
→ Feedback을 참고한 재최적화 가능

Statistics Feedback은 첫 실행의 이미 발생한 비용을 되돌리지 않으며, 모든 오차가 반드시 Feedback으로 해결되는 것도 아닙니다. Persistent Statistics·Histogram·Extended Statistics 등 근본 원인을 함께 개선합니다.

11. 혼동하기 쉬운 판단