Index·System Statistics: Access Path 비용을 만드는 입력값

B-tree Root·Branch 탐색
→ 조건 범위의 Leaf Block·Entry Scan
→ 후보 ROWID 생성
→ Heap Table Block 방문
→ Predicate·Join·Sort 처리

Index Statistics
  BLEVEL
  LEAF_BLOCKS
  NUM_ROWS
  DISTINCT_KEYS
  AVG_LEAF_BLOCKS_PER_KEY
  AVG_DATA_BLOCKS_PER_KEY
  CLUSTERING_FACTOR

System Statistics
  CPUSPEEDNW·IOSEEKTIM·IOTFRSPEED
  CPUSPEED·SREADTIM·MREADTIM·MBRC
  MAXTHR·SLAVETHR

Predicate Cardinality
+ Index 크기·Key 중복도·Table 배치
+ CPU·Single Block·Multiblock I/O 특성
→ 후보 Access Path·Join의 Cost
→ 최저 Cost Plan 선택

Cost
  = 같은 SQL과 Optimizer 환경에서
    후보 Plan을 비교하기 위한 내부 예상 단위

1. Root·Branch 수직 탐색
2. 시작 Leaf Block 도달
3. 조건 Range의 Leaf Entry Scan
4. 후보 ROWID 반환
5. ROWID가 가리키는 Table Block 접근
6. Table Filter·Projection·Join 처리

몇 개의 Leaf Block을 읽는가?
몇 개의 Entry·ROWID를 상위로 반환하는가?
ROWID가 몇 개의 Table Block을 방문하게 하는가?
Table Access를 Covering으로 제거할 수 있는가?
Full Scan의 Multiblock 경로가 더 저렴한가?

SELECT index_name,
       index_type,
       blevel,
       leaf_blocks,
       num_rows,
       distinct_keys,
       avg_leaf_blocks_per_key,
       avg_data_blocks_per_key,
       clustering_factor,
       sample_size,
       last_analyzed
FROM   user_indexes
WHERE  table_name = 'ORDERS'
ORDER  BY index_name;

SELECT index_name,
       partition_name,
       blevel,
       leaf_blocks,
       num_rows,
       distinct_keys,
       avg_leaf_blocks_per_key,
       avg_data_blocks_per_key,
       clustering_factor,
       last_analyzed
FROM   user_ind_partitions
WHERE  index_name = 'ORDERS_DATE_LIX'
ORDER  BY partition_position;

Global Index Statistics
Partition Index Statistics
Subpartition Index Statistics

→ Query가 실제 접근하는 범위와 같은 Statistics 수준을 확인

BLEVEL
  = Root Block에서 Leaf Block까지의 B-tree Level

BLEVEL = 0
  → Root Block과 Leaf Block이 같은 Block

BLEVEL = 1
  → Root 아래 Leaf Level이 존재

BLEVEL 증가
  → 한 번의 Probe에서 통과하는 Branch Level 증가 가능

단건 Probe 1회
  → 수직 탐색 Level 영향

대량 Range Scan
  → Leaf Scan·Table Access가 더 큰 비용일 수 있음

BLEVEL 하나
  ≠ Index Rebuild 근거

Table NUM_ROWS
  → Table Statistics Row 수

Index NUM_ROWS
  → Index Statistics Entry 수

CREATE INDEX orders_x1
ON orders(optional_code, optional_type);

optional_code IS NULL
AND optional_type IS NULL

→ 일반 B-tree에 Entry가 없을 수 있음

CREATE INDEX orders_x1
ON orders(status, region);

status NDV                    = 4
region NDV                    = 20
실제 (status,region) 조합 NDV = 45

ORDERS_X1.DISTINCT_KEYS       ≈ 45

선두 Column NDV
  = status만의 Distinct Value 수

DISTINCT_KEYS
  = (status,region) 전체 Index Key 조합의 Distinct 수

값이 1에 가까움
  → 평균적으로 Key Entry가 한 Leaf Block 안에 존재

값이 큼
  → 중복 Key Entry가 여러 Leaf Block에 걸칠 수 있음

작음
  → 같은 Key Row가 적은 Table Block에 모임

큼
  → 같은 Key Row가 여러 Table Block에 분산

INDEX STATUS_IX

status='READY' Row
  100,000건
  18,000 Table Blocks 분포

AVG_DATA_BLOCKS_PER_KEY
  → Key별 평균 Table Block 분산을 이해하는 보조값

인기 Key 1개
  → 수십만 Row·많은 Block

나머지 Key
  → 소량 Row

평균값
  → 특정 인기 Key의 실제 작업량을 숨길 수 있음

CF가 Table BLOCKS에 가까움
  → 같은 Leaf Block의 Index Entry가
    같은·인접 Table Block을 가리키는 경향
  → 넓은 Range Scan에 유리한 방향

CF가 Table NUM_ROWS에 가까움
  → Index 순서의 ROWID가
    여러 Table Block에 무작위로 분산된 방향
  → 많은 후보를 처리할수록 Table Access 증가 가능

ORDERS_DATE_IX
  → Date 순서와 Table 적재 순서가 유사
  → 낮은 CF 방향

ORDERS_CUSTOMER_IX
  → Customer 순서로 Row가 분산
  → 높은 CF 방향 가능

낮은 CF
  → Index Range Scan이 더 넓은 범위까지 경쟁 가능

높은 CF
  → Full Table Scan이 더 일찍 저렴해질 수 있음

최근 Partition
  → 순차 적재·작은 BLEVEL·좋은 CF

과거 Partition
  → Update·재적재·큰 Segment·다른 CF

Plan PSTART·PSTOP
+ 대상 Index Partition Statistics
+ Global Statistics
+ 실제 A-Rows·Buffers

Object Statistics
  → 얼마나 많은 Row·Block을 처리할지

System Statistics
  → 그 작업의 CPU·I/O 상대 비용을 어떻게 환산할지

Index 경로
  → 여러 Single Block I/O·CPU Probe

Full Scan 경로
  → Sequential·Multiblock I/O

System Statistics
  → 두 작업의 상대 Cost를 현실에 가깝게 비교

BEGIN
  DBMS_STATS.GATHER_SYSTEM_STATS(
    gathering_mode => 'NOWORKLOAD'
  );
END;
/

실제 업무 Counter 사용 X
Data File에 Random Read 제출
→ CPU·I/O System 특성 측정

BEGIN
  DBMS_STATS.GATHER_SYSTEM_STATS('START');
END;
/

-- 대표 Workload 수행

BEGIN
  DBMS_STATS.GATHER_SYSTEM_STATS('STOP');
END;
/

Noworkload와 Workload 모두 존재
  → Optimizer는 Workload Statistics 사용

대표 평상시 Workload
  → 유용한 비용 모델

Backup·장애·비정상 Batch 구간
  → 전체 SQL의 Cost 왜곡 가능

MREADTIM·MBRC를 수집·검증하지 못함
+ SREADTIM·CPUSPEED는 존재
→ SREADTIM·CPUSPEED 사용
→ Full Scan Cost에는 DB_FILE_MULTIBLOCK_READ_COUNT 활용 가능

Workload Statistics를 수집했다
  ≠ 모든 System Statistics 값이 반드시 채워졌다

SELECT sname,
       pname,
       pval1,
       pval2
FROM   sys.aux_stats$
WHERE  sname IN ('SYSSTATS_INFO','SYSSTATS_MAIN')
ORDER  BY sname, pname;

Index Cost
  ≈ BLEVEL 수직 탐색
   + 예상 Leaf Block Scan
   + 예상 Table Block 방문
   + CPU Predicate·Join·Sort

Full Scan Cost
  ≈ 대상 Table Blocks
   + Multiblock I/O 특성
   + CPU Predicate·Join·Sort
   + Parallel Overhead

기존 Shared Cursor
  → 기존 Plan을 계속 재사용할 수 있음

새 SQL·새 Hard Parse
  → 갱신된 System Statistics로 Cost 계산

BEGIN
  DBMS_STATS.CREATE_STAT_TABLE(
    ownname => USER,
    stattab => 'SYSTEM_STATS_BAK'
  );

  DBMS_STATS.EXPORT_SYSTEM_STATS(
    stattab => 'SYSTEM_STATS_BAK',
    statid  => 'BEFORE_CHANGE'
  );
END;
/

BEGIN
  DBMS_STATS.DELETE_SYSTEM_STATS;
END;
/

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       amount
FROM   orders
WHERE  status=:status
AND    order_date>=:from_dt
AND    order_date< :to_dt;

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

Index Starts·E-Rows·A-Rows·Buffers·Reads
Table A-Rows·Buffers·Reads
Access·Index Filter·Table Filter
후보 ROWID와 최종 Row 차이

Table Blocks·Buffers·Reads
Parallel·Direct Path 여부
Predicate CPU
Sort·TEMP