06. 히스토그램

(1) 히스토그램 유형

• 분포가 균일하지 않은 컬럼은 히스토그램으로 더 정확한 카디널리티 산출 가능
  • 높이균형(Height-Balanced) 히스토그램
  • 도수분포(Frequency) 히스토그램

• 히스토그램 생성은 컬럼 통계 수집 시 버킷을 2개 이상으로 지정
  • dba_histograms
  • dba_tab_histograms

• 히스토그램 설명 예제 데이터 (member)
  • 2,000명 회원 입력, 40세 연령 회원 1,000명 (50%)

(2) 도수분포 히스토그램

• Value-Based 히스토그램, 값별 빈도수(Frequency Number) 저장

• 컬럼 값마다 하나의 버킷 할당 (값의 수 = 버킷 수)
  • 버킷 수가 컬럼의 NDV 보다 크거나 같을 때 (버킷 최대 수 : 254)
  • member.age 의 NDV 가 89 이므로 도수분포 히스토그램 가능

exec dms_stats.gather_table_stats(user, 'member', method_opt => 'for all columns size 100');

-- user_tab_col_statistics.num_distinct : 89
-- user_tab_col_statistics.num_buckets : 89
-- user_tab_col_statistics.histogram : FREQUENCY

• NDV 와 버킷 수 항상 일치
• 100개 버킷 요청 했으나 NDV 만큼 버킷 생성 (항상 254로 요청 하면 됨)

• dba_histograms

※ 실제 데이터 값/분포 와 일치

• 값별 빈도수를 미리 계산 해 두므로 카디널리티를 쉽고 정확하게 구할 수 있으나, 버킷 수 한계로 NDV가 많은 컬럼의 경우 높이균형 히스토그램 사용

(3) 높이균형(Height-Balanced) 히스토그램

• Equi-Depth 히스토그램
• NDV 보다 적은 버킷 요청시 만들어 짐 - NDV 가 버킷 최대 수(254) 초과 시
  • 하나의 버킷이 여러 개 값을 담당 - NDV 가 100, 버킷 수 10 : 하나의 버킷이 평균 10개 값을 대표
• 각 버킷은 (1/(버킷 수) * 100)%의 데이터 분포 가짐
• 각 버킷 빈도수 : (총 레코드 수) / (버킷 개수)

• member.age 높이 균형 히스토그램 적용 예 : 총 2,000건
  • 버킷이 20개, 버킷당 100(2,000/20)개 레코드 높이 (=빈도수) 가짐, 분포는 각 5%
  • 빈도 수가 많은 값은 두개 이상의 버킷 할당 : member.age = 40 레코드는 10개 버킷 차지

• 높이 균형 히스토그램 생성

exec dbms_stats.gather_table_stats(user, 'member', method_opt => 'for all column size 20');

-- user_tab_col_statistics.num_distinct : 89
-- user_tab_col_statistics.num_buckets : 20
-- user_tab_col_statistics.histogram : HEIGHT BALANCED

• 히스토그램은 압축 저장 되므로 실제 버킷은 11개뿐 (user_histograms)

※ 압축 표현 해도 비중/인원수 구하는데 문제 없음, 39세/40세가 각각 500명으로 계산 됨

• dba_histograms

Popular Value 에 대한 선택도/카디널리티 계산

• 조건절 값이 두 개 이상 버킷을 가짐

선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)

카디널리티
= (총 로우 수) * 선택도
= (총 로우 수) * (조건절 값의 버킷 개수) / (총 버킷 개수)
= 2,000 * 10 / 20
= 1,000

※ Popular Value 40 은 총 20개 버킷 중 10개를 사용 - 선택도 50% (10 / 20)

Non-Popular Value 에 대한 선택도/카디널리티 계산

• 미리 구해 놓은 density 값 이용

카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 * density

-- user_tab_col_statistics.num_distinct : 89
-- user_tab_col_statistics.density : 0.2567285
-- 카디널리티 : 2,000 * 0.2567285 = 513

Density

• 해당 컬럼을 '=' 조건으로 검색 시 선택도
• 오라클이 density 구하는 공식 (참고만)
  • 히스토그램이 없거나, 100% 균일한 분포 시 : 1 / num_distinct
  • 높이균형 히스토그램 : ∑(모든 non-popular value 빈도수)^2 / (null을 제외한 총 로우 수) * ∑(모든 non-popular value 빈도수)
  • 도수분포 히스토그램 : 1 / (2 * (null을 제외한 총 로우 수))
• 기억하기 : '=' 조건 조회 시 옵티마이저가 1 / num_distinct 를 이용 하거나 미리 구해둔 density 값을 이용
• density 이용시 카디널리티

카디널리티 = 총 로우 수 * 선택도 = num_rows * density

(4) 바인드 변수 사용 시 카디널리티 계산

• 바인드 변수 사용 시 최적화 후 실행 시점에 값만 다르게 바인딩 하면서 반복 재사용 하므로, 최적화 시점에서 컬럼의 데이터 분포 활용 불가. (통계 정보는 활용 하나 컬럼 히스토그램 정보를 사용하지 못함)
• 옵티마이저는 평균 분포를 가정한 실행 계획 생성
  • 컬럼 분포가 균일 하면 다행이나, 그렇지 않을 경우 비효율 실행계획 문제 가능성 있음

'=' 조건

• 바인드 변수로 '=' 검색시 선택도
  • 히스토그램 없음 : 1 / num_distinct
  • 도수분포 히스토그램 : 1 / num_distinct
  • 높이균형 히스토그램 : density

범위검색 조건

• 고정된 규칙으로 선택도 추정

• 바인드 변수 사용시 정해진 계산식에 기초해 비용을 계산하므로 최적 실행계획 수립 가능성 낮음, 상수 조건 사용시 거의 정확한 카디널리티 계산
  • DW, OLAP, 배치 프로그램에서 수행 되는 쿼리는 변수보다 상수가 좋음, OLTP 에서도 값의 종류가 적고 분포가 균일하지 않을 때는 상수 조건이 유리
  • 파티션 테이블 쿼리 시 파티션 레벨 통계 정보 이용 못하고 테이블 레벨 통계만 이용

(5) 결합 선택도

• 두개 이상 컬럼에 대한 결합 선택도 구할 때 정확성 떨어짐 (특히 컬럼 간 상관관계가 있을 때)

동적 샘플링(Dynamic Sampling)

• 소량의 데이터 샘플링으로 조건절에 사용된 두 개 이상 컬럼의 결합 분포 구함 (동적 샘플링 레벨 4 이상 설정 시 / dynamic_sampling)

create table 사원
as
select empno 사원번호, ename 사원명, sal 급여, sal * 0.1 상여 from emp;

exec dbms_stats.gather_table_stats(user, '사원', method_opt => 'for columns 급여 size 254 상여 size 254');

select * from 사원 where 급여 >= 2000 and 상여 >= 200;

• 급여/상여 컬럼은 상관 관계가 매우 높다.
  • 옵티마이저는 카디널리티를 2로 예상 (실제 6건)

• • 동적 샘플링 레벨 4 설정시, 카디널리티 6 예상

• DYNAMIC_SAMPLING 힌트의 정체 - http://ukja.tistory.com/112
  • 통계 정보가 완벽히 있어도 필요할 때가 있다 (LIKE '%T%')
  • 부하가 크기 때문에 OLTP 보다는 OLAP 에 어울린다.

• 통계 정보가 없고 동적 샘플링도 못 쓸때
  • 통계 정보가 없을 때 CBO는 무슨 일을 하는가? - http://ukja.tistory.com/288

다중 컬럼 통계(Multi-Column Statistics)

• Extended Statistics 기능으로 다중 컬럼에 대한 히스토그램 생성 가능

-- 설정
exec dbms_stats.create_extended_stats(user, '사원', '(급여, 상여)');
exec dbms_stats.drop_extended_stats(user, '사원', '(급여, 상여)');
exec dbms_stats.gather_table_stats(user, '사원', method_opt => 'for all columns size skewonly for columns (급여, 상여) size 254');

-- 확인
select extension_name, extension from dba_stat_extensions where owner = user and table_name = '사원';
select dbms_stats.show_extended_stats_name(user, '사원', '(급여, 상여)') group_name from dual;
select * from dba_histograms where owner = user and table_name = '사원' and column_name = 'SYS_XXXXXXXXXX' order by endpoint_number;

• Oracle 11g - Extended Statistics와 Histogram의 화려한 부활 - http://ukja.tistory.com/95
  • Extended Statistics 테스트 케이스

기타

• 불필요한 Histogram만 삭제하기 - http://ukja.tistory.com/184
  • dbms_stats.delete_column_stats.col_stat_type => 'HISTOGRAM'

07. 비용

• 옵티마이저가 사용하는 두가지 비용 모델

※ _optimizer_cost_mode = CHOOSE : 시스템 통계가 있으면 CPU, 없으면 I/O 비용 모델 선택 (기본값)

※ 쿼리 레벨 비용 모델 선택 : cpu_costing / no_cpu_costing

(1) I/O 비용 모델

인덱스를 경유한 테이블 액세스 비용

• Single Block I/O 방식 사용
  • 1 Block 읽기 = 1 I/O Call

• 예상 비용 : 인덱스 스캔 단계 6, 테이블 액세스 단계 64 번의 Single Block I/O Call 발생
  • 인덱스 단계 Rows(=카디널리티)가 2,385 이나 클러스터링 팩터를 고려
• 인덱스를 이용한 테이블 액세스 비용 공식

*

• 인덱스 Access Predicate 와 Filter Predicate 가 다른 조건절
  • 좌변 컬럼을 가공
  • 왼쪽 '%' 혹은 양쪽 '%' LIKE
  • 같은 컬럼에 대한 조건절이 두개 이상
• 위와 같은 케이스를 제외하고 유효 인덱스 선택도와 유효 테이블 선택도는 항상 같다.

• 인덱스 스캔 비용, 총 테이블 액세스 비용 산출

Full Scan에 의한 테이블 액세스 비용

• HWM 아래쪽 블록을 순차적으로 읽는 과정의 I/O Call 횟수
  • Multi Block I/O 사용 : I/O Call 횟수 = 총 블록 수 / db_file_multiblock_read_count

• 내부적으로 조정된 값으로 비용 계산 하므로 예상치와 오차 있으나 cost 는 감소 함

※ user_tables.blocks = 709

I/O 비용 모델의 비현실적인 가정

• 디스크 I/O Call 횟수로 비용을 평가 한다는 것은...
  • Single Block I/O 와 Multi Block I/O 는 비용이 같음 (1 블록 과 128 블록 읽는 비용이 같음)
  • 캐싱 효과를 전혀 고려하지 않음 (모든 블록을 디스크에서 읽음)

optimizer_index_cost_adj

• 인덱스 탐색 비용 조정 (1 ~ 10,000), 상대적인 비용의 표현
• 기본값 : 100 (Single Block I/O 비용 = Multi Block I/O 비용) , 25 설정 시 Single Block I/O 비용이 25% 가 됨
  • 낮게 설정 할 수록 인덱스 액세스 선호 하게 됨

optimizer_index_caching

• NL 조인에서 Inner 쪽 인덱스 블록이 캐싱돼 있을 가능성 설정
• 기본값 : 0 (NL 조인 시 Inner 인덱스 블록을 매번 디스크에서 읽는다고 가정)
  • 값을 높게 설정할 수록 인덱스 NL 조인 선호 하게 됨

(2) CPU 비용 모델

• I/O 보다 CPU가 성능에 더 큰 영향을 끼치는 케이스
  • 해시 조인 시, 해시 체인에 달린 레코드가 많을 때 (해시 체인 스캔 부하)
  • 캐싱된 블록 반복 읽기 (블록 내 매번 많은 레코드 스캔시)
  • 버퍼 Pin 상태의 같은 블록 반복 읽기
  • 여러개의 사용자 정의 함수를 대량 레코드와 함께 반복 호출
  • 메모리 소트 반복

• CPU 사용량 다소 증가 케이스
  • 조건저러 개수가 아주 많음
  • 조건절 혹은 SELECT-LIST에 많은 연산 집약적 작업
    {note:title=CPU 비용 모델}
    Cost = ( #SRds * sreadtim + #MRds * mreadtim + #CPUCycles / cpuspeed) / sreadtim
    ※ 9i 부터 도입
    {note}

I/O 비용

• I/O 일량을 시간으로 표현
• 인덱스 스캔(Single Block) 과 테이블 스캔(Multi Block) 의 상대적인 시간 차이 표현

CPU 비용

• '예상 CPU 사이클 수'(버퍼 캐시에서 데이터 읽는 CPU 비용 포함) / '초당 처리할 수 있는 CPU 사이클 수'
• I/O 시간 + CPU 연산 시간 으로 쿼리 수행 비용 평가
• 비용을 Single Block I/O 소요 시간 단위로 표현 (/sreadtim)
  • Cost = 10 : Single Block I/O 10번 수행 시간 소요
    ※ 향후 캐싱 효과를 비용 계산시에 포함 할것으로 예상 됨 (dba_tab_statistics.(avg_cached_blocks, avg_cache_hit_ratio))

08 통계정보 II

(1) 전략적인 통계수집 정책의 중요성

CBO 능력을 최대한 끌어 올리는 핵심 요소

• 통계정보가 CBO에 미치는 영향력은 절대적.
• 옵티마이저가 그 능력을 최대한 발휘할 수 있도록 환경을 조성해 주어야 한다. (통계정보)
• 옵티마이저가 바보 같을 때는 많은 경우 원인이 통계정보 이상에 있다.

DB 관리자의 핵심 역할은 통계정보 관리

• 마지막 통계 정보 수집일이 오래 되었다면, DBA에게 본 절의 내용을 소개.
• CBO 환경에서는 통계정보 수집 정책을 세우고 그에 따라 통계정보를 관리.
• 악성 쿼리로 돌변한 경우 대개 통계정보에서 비롯된 문제

통계정보 수집 시 고려사항

• 파티션에 골고루 혹은 특정 파티션만 갱신이 이뤄지는 경우
• 서비스 다운타임이 매일 확보 혹은 없는(24*7) 경우
• 시스템 목적이 OLTP 혹은 DW 인 경우
• 데이터 특성상 작게 샘플링 혹은 전수 검사 하는 경우

주기적으로 통계 수집하면서 안정적이야 최적

• 정확한 통계정보는 좋은 실행 계획을 위해 중요 (특히 비정형 쿼리가 많은 OLAP)
• OLTP 에서는 통계정보의 안정성이 더 중요할 수 있음. (최적 성능 < 안정적 운영)
  • 하지만 CBO 에서는 통계정보가 반드시 필요 (전략적인 통계수집 정책 필요)

통계 수집 정책 수립은 필수

• 통계 수집 불필요 오브젝트는 통계 고정 가능 (DBMS_STATS.LOCK_TABLE_STATS)
• 핵심 프로그램은 힌트를 적용해 실행계획 고정
• 운영 DB 수집 통계를 개발 DB 반영 (시스템 통계 및 관련 파라미터 포함)
• 통계정보 관련 성능 이슈 발생 시 빠른 정상화를 위해 안정적인 최근 통계 정보 백업
• 오브젝트별 전략 예시

• 위와 같은 전략 설계는 데이터베이스 설계 시 수행, 오픈 전 적정성 여부 확인 필요
• 자동 수집 기능을 이용하더라도 위와 같은 전략은 필요

(2) DBMS_STATS

• ANALYZE 에서 더 정교한 DBMS_STATS 로 갈아타야 함
  • 파티션 테이블/인덱스의 경우 필수

• ANALYZE 는 아래 정보 수집에 여전히 필요
  • FREELIST 블록 정보 (AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS) : FLM 만 해당
  • 체인 발생 로우 수 (CHAIN_CNT) : 옵티마이저 관련 통계가 아님
    • DBMS_STATS 는 DBA_TABLES.CHAIN_CNT 가 NULL 이면 0 으로 업데이트
  • 체인, 마이그레이션 발생 로우 식별 (LIST CHAINED ROWS INTO CHAINED_ROWS)
  • 블록, 로우 정합성 검증 (VALIDATE STRUCTURE)

• DBMS_STATS.GATHER_TABLE_STATS 인자

(3) 컬럼 히스토그램 수집

• 조건절에 자주 사용되면서 편중된(Skewed) 데이터 분포를 갖는 컬럼이 히스토그램 수집 대상
  • 인덱스가 없는 조건절 컬럼도 조인 순서 및 방식에 영향을 주므로 수집 대상에 포함 됨

• 히스토그램 불필요 컬럼
  • 데이터 분포가 균일
  • UNIQUE 하고 항상 등치 조건 검색
  • 항상 바인드 변수로 검색

• dbms_stats.gather_table_stats.method_opt
  • 8i, 9i 기본값 : 'for all columns size 1' (모든 컬럼 히스토그램 수집 하지마)
  • 10g 기본값 : 'for all columns size auto' (모든 컬럼 Skew 조사 후 버킷 개수 결정)
    • auto 는 skewonly 와 달리 컬럼이 조건절에 사용되는 비중까지 고려 (sys.col_usage$)

desc sys.col_usage$

• 히스토그램 주의 사항
  • 없던 히스토그램이 생겨 SQL 실행 계획이 부정적으로 바뀔 수 있음
  • 통계정보 수집 시간이 증가 함 (큰테이블의 디스크 소트 부하)
  • 대용량 테이블은 관리자가 히스토그램 수집 컬럼을 지정 하는 것이 바람직
  • 10g 부터 기본 값을 table-driven 방식으로 관리 (문제시 dbms_stats.set_param 으로 기본값 변경 가능)
    • 문제 예시: sys.col_usage$ 가 충분히 수집 되지 않는 경우 히스토그램 수집이 패스 될 수 있음

(4) 데이터 샘플링

샘플링 비율

• estimate_percent 값과 정확도는 선형 비례 하지 않고, 일정 비율 이상이면 충분 함
  • 적정 비율 조사 필요 : 5% 부터 시작, 값을 늘려 가며 수집 (대개 5% 면 충분)

블록 단위 샘플링

※ estimate_percent 적용 시만 활용됨

안정적인 통계정보의 필요성

• 샘플링 방식 사용시 매번 통계치가 다르게 구해질 수 있음 (실행 계획이 불안정해 질 수 있음)
  • 특히 NULL 이 많거나 데이터 분포가 고르지 않을 때 샘플링 비율에 크게 영향 받음
  • 선택도 계산시 Null 값을 제외한 로우 수, Distinct Value 개수, 총 레코드 개수 활용 됨

해시 기반 알고리즘으로 NDV 계산 - 11g

• 히스토그램 사용할 수 없을 때 NDV(the Number of Distinct Values)를 선택도 계산에 활용
  • 데이터 분포가 고르지 않은 경우 안정성 문제 있음
  • 11g 부터 소트 없이 해시 기반 알고리즘으로 전체 대상 NDV 산출 (과거 샘플링 보다 빠름)

(5) 파티션 테이블 통계 수집

• 파티션 테이블은 테이블 레벨 통계(Global) 와 파티션 레벨 통계를 각각 관리

• ANALYZE 는 파티션 통계로 테이블 통계 유추
• DBMS_STATS 는 테이블 통계를 위한 쿼리 별도 수행
• granularity

• 파티션1 NDV 가 10, 파티션2 NDV 가 20 일 때 테이블 NDV는?
  • 20 : 파티션2 의 집합이 파티션1 의 집합을 완전 포함
  • 30 : 파티션1, 파티션2 집합이 서로 배타적

• Range 파티션은 대개 새 파티션에만 데이터가 입력 되는데 통계 수집을 위해 테이블 전체 두번 스캔?
  • 새 파티션만 통계정보 갱신하면 어느 순간부터 매우 부정확한 상태가 됨 (NDV, LOW_VALUE, HIGH_VALUE, 히스토그램)
  • 10g 이하 전략 : 새 파티션 통계 수집 + 테이블 전체 통계 수집 (혹은 dbms_stats.copy_table_stats)

NDV를 제외한 Incremental Global 통계 - 10.2.0.4

• granularity : 'approx_global and partition' 추가
  • 테이블 통계, 히스토그램을 파티션 레벨 통계, 히스토그램 으로부터 집계 함
    • 컬럼 NDV 와 인덱스의 Distinct Key 개수 제외

begin dbms_stats.gather_table_stats('ods', 'order', partname => 'order_200911', granularity => 'approx_global and partition'); end;

• 2009년 11월 파티션만 통계 수집 후, NDV를 제외한 나머지 테이블 레벨 통계는 파티션 통계를 이용해 구한다.
  • 파티션 통계로 부터 정확히 얻을 수 있는 경우 테이블 NDV 를 갱신 한다. (UNIQUE INDEX 가진 컬럼 : 레코드수, 파티션 키 컬럼 :파티션 레벨 NDV 의 합)
  • 넓은 주기 혹은 대량 데이터 변경 시 테이블 레벨 통계 수집 필요
  • NDV 보다 주로 LOW_VALUE/HIGH_VALUE 로 문제가 발생
    • 범위에서 초과된 값으로 조회시 카디널리티는 무조건 1로 계산

NDV를 포함한 Incremental Global 통계 - 11g

• 파티션 NDV를 이용해 테이블 NDV를 정확히 구할 수 있게 됨
  • NDV를 포함한 Incremental Global 통계 수집 기능을 위해 파티션 컬럼별 'synopsis' 라는 메타 데이터 관리 시작

• 활성화 시 'global and partition' 지정 해도 테이블 전체 두번 읽지 않고, 통계가 없거나 Stale 상태 파티션만 통계 수집
  • 파티션 통계와 synopsis를 이용해 테이블 통계 갱신
  • dbms_stats.set_table_prefs('ods', 'order', 'incremental', 'true');

(6) 인덱스 통계 수집

• 테이블은 샘플링, 인덱스는 별도로 전수 검사 권장
  • 인덱스는 작고 정렬이 없어 전수 검사 가능
  • 테이블 통계 와 같이 수집 하면 인덱스 샘플링 비율도 테이블 따라감
• 인덱스 (재)생성시 인덱스 통계가 자동 수집 됨 : _optimizer_compute_index_stats = true (기본값)
  • FBI 생성시 생성되는 Hidden Column 은 별도 수집 필요 - http://ukja.tistory.com/196

(7) 캐싱된 커서 Invalidation

• _optimizer_invalidation_period = 18,000 초

(8) 자동 통계 수집

• 10g : 22시 ~ 06시 모든 사용자 오브젝트 통계 자동 수집 (gather_stats_job)
  • 통계 정보가 없거나, DML이 많이 발생한 오브젝트 대상
• gather_stats_job
  • maintenance_window_group 에 등록된 윈도우가 열릴 때 스케쥴러에 의해 수행, 닫힐 때 중단 (stop_on_window_close 값)

통계정보 갱신 대상 식별

• 10g 부터 모든 사용자 테이블은 기본으로 모니터링 되고 있음 (dba_tab_modifications)
• 대상 테이블에 10% 이상 변경이 발생하면 dba_tab_statistics.stale_stats = 'YES' 설정 됨 ('gather stale', 'gather auto' option 적용시 수집 대상이 됨)
  • 공유풀에 모인 모니터링 결과를 SMON이 주기적(약 3시간)으로 딕셔너리에 반영 (dbms_stats.flush_database_monitoring_info : 증시 반영)

자동 통계 수집 기능 활용 가이드

• 일 년에 한두 번쯤 생각날 때만 통계 정보를 수동 수집 < 자동 수집 기능
• 자동 수집 기능은 중대형급 데이터베이스 환경에서는 안정성 관점에서 비추천
• 오브젝트별 전략을 세우고, 별도 스크립트로 관리, 11g 부터는 파라미터 driven 방식으로 관리

(9) Statistics Preference

• 10g : 자동 통계 수집 사용 시, 기본 설정을 적용하고 싶지 않은 오브젝트에 Lock 설정 (dbms_stats.lock_table_stats)
• 11g : Statistics Preference - 자동 통계 수집 시 테이블/스키마별 통계 수집 방식을 따로 설정
  • dbms_stats.set_table_prefs
  • dbms_stats.set_schema_prefs
  • dba_tab_stat_prefs;