복합 조건의 추정 보완: Dynamic Statistics·Column Group·Expression Statistics

1. Statistics가 없거나 현재 Data를 충분히 설명하지 못함
2. 같은 Table의 여러 Column이 강하게 상관됨
3. WHERE 절에서 함수·산술식·CASE 표현식을 반복 사용
4. 일회성 Stage·Temporary 성격의 Object
5. 복잡한 AND·OR Predicate 또는 Parallel Query

Dynamic Statistics
  → Parse 시점 Sample로 즉시 보완
  → Object Statistics 자체를 갱신하지 않음
  → Hard Parse 비용 발생 가능

Extended Statistics
  → 반복되는 구조적 관계를 지속 저장
  → 통계 수집 후 여러 SQL이 재사용
  → Access Path를 직접 만들지는 않음

SELECT customer_id,
       customer_name
FROM   customers
WHERE  country_code = 'KR'
AND    city_name    = 'SEOUL';

NUM_ROWS              = 10,000,000
country_code NDV      =        100
city_name NDV         =     20,000
실제 (KR,SEOUL) Row   =  2,000,000

Selectivity(country='KR') = 1/100
Selectivity(city='SEOUL') = 1/20,000

결합 Cardinality
≈ 10,000,000 × 1/100 × 1/20,000
≈ 5 Row

country_code와 city_name은 독립이 아님
SEOUL이라는 값이 KR이라는 정보를 거의 포함
개별 NDV만으로 실제 조합 빈도를 알 수 없음

Driving Row 심한 과소 추정
→ Nested Loops·Index Probe가 싸게 보임
→ 실제 Inner Probe·Table Access 대량 반복

Hard Parse
→ Object·Predicate·Plan Directive·Parallel 조건 확인
→ 필요 시 Block Sample
→ Sample에 Predicate 적용
→ Cardinality 보완
→ Cost 계산·Plan 선택

Dynamic Statistics
  → 해당 Optimization을 위한 보조 Estimate
  → USER_TAB_STATISTICS의 NUM_ROWS 등을 갱신하지 않음
  → 주로 Hard Parse 비용으로 발생

DBMS_STATS Object Statistics
  → Data Dictionary에 지속 저장
  → 여러 SQL Optimization에서 재사용

Note
-----
- dynamic statistics used for this statement (level=4)

SELECT /*+ DYNAMIC_SAMPLING(c 4) */
       c.customer_id,
       c.customer_name
FROM   customers c
WHERE  c.country_code=:country
AND    c.city_name=:city;

1. 어떤 조건에서 Dynamic Statistics를 사용할지
2. 얼마나 많은 Block을 Sample할지

Sample 확대
  → 추정 정확도 향상 가능
  → Recursive SQL·Parse CPU·I/O·Elapsed 증가
  → Concurrent Hard Parse 부하 증가 가능

Object Statistics를 Null로 운영
→ Optimization 시 Dynamic Statistics로 현재 규모 보완

일회성·Unknown
  → Dynamic Statistics

반복·구조적 관계
  → Persistent Extended Statistics

개별 Statistics
  country_code NDV
  city_name NDV

Column Group
  (country_code, city_name)의 실제 조합 NDV·분포

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

WHERE (country_code, city_name)
      IN (('KR','SEOUL'), ('US','NEW YORK'))

GROUP BY country_code, city_name

DECLARE
  l_name VARCHAR2(128);
BEGIN
  l_name := DBMS_STATS.CREATE_EXTENDED_STATS(
    ownname   => USER,
    tabname   => 'CUSTOMERS',
    extension => '(COUNTRY_CODE,CITY_NAME)'
  );

  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'CUSTOMERS'
  );
END;
/

CREATE_EXTENDED_STATS
  → Extension 정의 생성
  → System-generated Name 반환

GATHER_TABLE_STATS
  → Data를 읽어 NDV·Histogram 등 실제 Extension Statistics 채움

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname   => USER,
    tabname   => 'CUSTOMERS',
    method_opt =>
      'FOR ALL COLUMNS SIZE AUTO ' ||
      'FOR COLUMNS SIZE SKEWONLY (COUNTRY_CODE,CITY_NAME)'
  );
END;
/

BEGIN
  DBMS_STATS.SEED_COL_USAGE(
    sqlset_name => NULL,
    owner_name  => NULL,
    time_limit  => 300
  );
END;
/

SELECT DBMS_STATS.REPORT_COL_USAGE(
         ownname => USER,
         tabname => 'CUSTOMERS'
       )
FROM dual;

AUTO_STAT_EXTENSIONS = OFF
  → 기본값
  → Extension을 수동 생성하거나 METHOD_OPT에 명시

AUTO_STAT_EXTENSIONS = ON
  → Workload·SQL Plan Directive 기반 자동 Column Group 생성 가능

WHERE TRUNC(order_date)=DATE '2026-07-29'

WHERE LOWER(customer_name)=:name

WHERE quantity * unit_price > :amount

DECLARE
  l_name VARCHAR2(128);
BEGIN
  l_name := DBMS_STATS.CREATE_EXTENDED_STATS(
    ownname   => USER,
    tabname   => 'ORDERS',
    extension => '(TRUNC(ORDER_DATE))'
  );

  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'ORDERS'
  );
END;
/

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname   => USER,
    tabname   => 'ORDERS',
    method_opt =>
      'FOR ALL COLUMNS SIZE AUTO ' ||
      'FOR COLUMNS (TRUNC(ORDER_DATE)) SIZE SKEWONLY'
  );
END;
/

Expression Statistics
  → Optimizer가 결과 규모를 더 정확히 예상

Function-Based Index
  → Optimizer가 실제 Expression Key를 Index Scan 가능

단일 Column Skew
  ≠ Column Group 문제

Column 상관
  ≠ Histogram 하나로 완전 해결

표현식 추정
  ≠ Access Path 생성

SELECT extension_name,
       extension,
       creator,
       droppable
FROM   user_stat_extensions
WHERE  table_name='CUSTOMERS';

SELECT e.extension,
       c.column_name,
       c.num_distinct,
       c.density,
       c.histogram,
       c.num_buckets,
       c.last_analyzed
FROM   user_stat_extensions e
JOIN   user_tab_col_statistics c
  ON   c.table_name=e.table_name
 AND   c.column_name=e.extension_name
WHERE  e.table_name='CUSTOMERS';

BEGIN
  DBMS_STATS.DROP_EXTENDED_STATS(
    ownname   => USER,
    tabname   => 'CUSTOMERS',
    extension => '(COUNTRY_CODE,CITY_NAME)'
  );
END;
/

SELECT /*+ GATHER_PLAN_STATISTICS */
       customer_id,
       customer_name
FROM   customers
WHERE  country_code=:country
AND    city_name=:city;

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

Extended Statistics 생성·수집
→ Cursor Invalidations 또는 새 Hard Parse
→ 새 E-Rows·Plan 사용

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

Starts>1
  → Expected Total ≈ E-Rows×Starts
  → Actual per Start ≈ A-Rows/Starts

충분한 Estimate 개선
→ 올바른 Plan 선택 경계 통과
→ 실제 Resource·Elapsed 개선

E-Rows는 여전히 20% 오차
하지만
NL Join → 적절한 Hash Join
Buffers 80% 감소
Elapsed 70% 감소

→ 실질적 성공