옵티마이저 통계의 기초: Table·Column Statistics 읽기

Table·Column·Index·System Statistics
+ Extended·Dynamic Statistics
→ Predicate Selectivity
→ Row Source Cardinality
→ Access Path·Join Order·Join Method·Sort 비용
→ 최저 Cost Plan 선택

Optimizer Statistics
  → 실행 전 예상
  → E-Rows·Cost의 근거

Runtime Statistics
  → 특정 Cursor 실행의 실제 작업량
  → Starts·A-Rows·Buffers·Reads·A-Time

통계 값·수집 범위·시점
→ Optimizer 예상 E-Rows
→ 선택된 Access Path·Join
→ 실제 A-Rows·Buffers
→ 최초 추정 오차 원인

Selectivity
  → Predicate가 전체 Row 중 선택할 비율

Cardinality
  → 각 Plan Operation이 반환할 예상 Row 수

Cost
  → 예상 I/O·CPU·Memory 사용량의 내부 비교값

Driving Row 과대 추정
  → Hash Join·Full Scan 쪽 비용이 상대적으로 유리해질 수 있음

Driving Row 과소 추정
  → Nested Loops Inner Probe가 실제로 과도하게 반복될 수 있음

NUM_ROWS=1,000,000
  ≠ 지금 이 순간 COUNT(*)=1,000,000이라는 보장

A-Rows=15,000
  = 해당 Cursor 실행에서 Row Source가 상위로 반환한 실제 Row

SELECT table_name,
       partition_name,
       object_type,
       num_rows,
       blocks,
       avg_row_len,
       sample_size,
       last_analyzed,
       stale_stats,
       global_stats,
       user_stats,
       notes
FROM   user_tab_statistics
WHERE  table_name = 'ORDERS'
ORDER  BY object_type, partition_name;

SELECT segment_name,
       segment_type,
       blocks,
       bytes
FROM   user_segments
WHERE  segment_name = 'ORDERS';

Table Statistics BLOCKS
  → Cost 계산용 Object 통계

Segment BLOCKS
  → 현재 할당된 물리 공간

High Water Mark Scan 범위
  → 실제 Full Scan·Segment 상태와 연결해 별도 확인

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => DBMS_STATS.AUTO_CASCADE
  );
END;
/

Sample 정확도
  + 필요한 Statistics 종류
  + Data 분포·상관관계 표현
  → 전체 통계 품질

SELECT column_name,
       num_distinct,
       num_nulls,
       low_value,
       high_value,
       density,
       avg_col_len,
       histogram,
       num_buckets,
       sample_size,
       last_analyzed,
       global_stats,
       notes
FROM   user_tab_col_statistics
WHERE  table_name = 'ORDERS'
ORDER  BY column_name;

NONE
FREQUENCY
TOP-FREQUENCY
HYBRID
HEIGHT BALANCED

Histogram 없음
  → 균등 분포 가정의 영향이 큼

Histogram 있음
  → 인기값·비인기값·Bucket 정보로 값별 추정 보완

Selectivity ≈ 1 / NDV
Cardinality ≈ NUM_ROWS / NDV

NUM_ROWS = 1,000,000
NDV      = 4

개념적 Cardinality ≈ 250,000

Non-NULL Row = NUM_ROWS - NUM_NULLS
Average Non-NULL Frequency
  = (NUM_ROWS - NUM_NULLS) / NDV

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

Non-NULL 평균 빈도 = 900,000 / 4 = 225,000

NUM_ROWS / NDV
  → Oracle 문서의 가장 단순한 Cardinality 설명

(ROWS - NULLS) / NDV
  → Data의 Non-NULL 값당 평균 빈도 이해

실제 E-Rows
  → Density·Histogram·Predicate·Optimizer 계산 결과

Histogram 없음
  DENSITY = 1 / NUM_DISTINCT

Histogram 있음
  DENSITY는 Histogram Endpoint 관계를 반영
  모든 값의 Selectivity를 단일 DENSITY로 설명할 수 없음

DENSITY 하나
→ 모든 Popular·Nonpopular 값 Cardinality를 동일하게 계산

WHERE status = 'READY'

WHERE status IS NULL

Statistics HIGH_VALUE = 2026-07-01
실제 Data Max         = 2026-08-01
Predicate             = 2026-07-31 이상

→ 최신 구간 빈도·Cardinality를 부정확하게 추정할 수 있음

STALE_STATS = YES
  → 변경량 기준으로 Statistics가 Stale

STALE_STATS = NO
  → Stale 임계치를 넘지 않음

STALE_STATS = NULL
  → Statistics가 수집되지 않은 상태 가능

SELECT DBMS_STATS.GET_PREFS(
         pname   => 'STALE_PERCENT',
         ownname => USER,
         tabname => 'ORDERS'
       ) AS stale_percent
FROM dual;

Global Table Statistics
Partition Statistics
Subpartition Statistics
Global·Partition Column Statistics

WHERE region_code = :region
AND   store_code  = :store

Column Group Statistics
  → 같은 Table의 여러 Column 관계를 표현

WHERE UPPER(customer_name) = :name

Expression Statistics
  → 함수·계산식 결과의 Selectivity 추정 보완

장점
  → 부족한 Statistics를 Parse 시점에 일부 보완

비용
  → Recursive SQL·Hard Parse Overhead
  → 영구 Statistics를 완전히 대체하지 않음

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       customer_id,
       amount
FROM   orders
WHERE  status = 'ERROR';

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_no,
    'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
  )
);

| Id | Operation          | Starts | E-Rows | A-Rows | Buffers |
|---:|--------------------|-------:|-------:|-------:|--------:|
|  1 | TABLE ACCESS FULL  |      1 | 250000 |  15000 |   12000 |

E-Rows
  → Optimizer 예상

A-Rows
  → 실제 상위 반환 Row

Buffers
  → 실제 Logical Block 작업량

최초 오차 지점
→ 해당 Table·Predicate·Join의 통계를 점검

Starts = 1
  → E-Rows와 A-Rows 직접 비교 가능

Starts = 10,000
  → Actual per Start ≈ A-Rows / Starts
  → 또는 Expected Total ≈ E-Rows × Starts

1. SQL_ID·Child Cursor·실제 Bind·Optimizer 환경을 확정한다.
2. Plan Tree 아래에서 최초 E-Rows·A-Rows 오차 지점을 찾는다.
3. Starts를 반영해 예상·실제 비교 단위를 맞춘다.
4. Table NUM_ROWS·BLOCKS·Sample·LAST_ANALYZED를 확인한다.
5. Predicate Column의 NDV·NULL·Density·Histogram·Low/High를 확인한다.
6. Stale Percent·DML 변화·Partition 범위를 확인한다.
7. 편중·상관·Expression·Missing Statistics로 원인을 분류한다.
8. 필요한 Statistics만 Gather·Extended·Dynamic 방식으로 보완한다.
9. 동일 Bind·Fetch에서 Plan·A-Rows·Buffers·Reads·Elapsed를 재검증한다.
10. Plan 변화·Cursor Invalidations·다른 SQL 회귀를 Monitoring한다.