| {code} 1) 시간 : 부하가 없는 시간대에 가능한 빠르게 수집을 완료해야 함 2) 샘플 크기 : 가능한 적은 양의 데이터를 읽어햐 함 3) 정확성 : 전수 검사할 때의 통계치에 근접해야 함 4) 안정성 : 데이터에 큰 변화가 없을 경우 매번 통계치가 바뀌지 않아야 함 |
=> 충분한 신뢰수준을 갖춘 안정적인 통계정보를 옵티마이저에게 제공하려면 치밀한 전략이 필요하다.
|
h2. 4. 주기적으로 통계 수집하면서 안정적이어야 최선
- 안정성을 중시하여 통계정보를 수집하지 않으면 안된다.
h2. 5. 통계 수집 정책 수립은 필수
- 통계를 수집할 필요가 없는 오브젝트에 대해서는 Lock옵션으로 통계정보를 고정할 수 있다.
- 운영 DB에서 수집한 통계정보를 개발 DB에도 반영한 상태에서 개발을 진행해야 한다.
- 통계정보 변화로 인한 문제를 대비해 가장 안정적이었던 최근 통계정보를 백업해 둔다.
- 전략을 세우는 세부 단계에서는 오브젝트별 통계수집 주기와 샘플링 비율등을 표로 정리해둔다.
- 안정적인 고성능 데이터베이스 구축을 위해 시스템 여건과 오브젝트 특성에 맞는 통계수집 정책을 마련해야 한다.
h1. 2. DBMS_STATS
- 과거 Analyze 명령어를 대신하여 dbms_stats패키지를 사용한다.
- dbms_stats가 더 정교하게 통계를 계산하며, 파티션 테이블/인덱스일때는 반드시 dbms_stats를 사용한다.
- dbms_stats.gather_table_stats 프로시저의 인자표 ( 참조 : page 446 ~ 447 )
h1. 3. 컬럼 히스토그램 수집
- 히스토그램을 수집하고 관리하는 비용이 크기 때문에 필요한 컬럼에만 히스토그램을 수집한다.
- 조건절에 자주 사용하면서 편중된 데이터 분표를 갖는 컬럼이 주 대상이다.
- 주로 인덱스 컬럼에 히스토그램을 수집하는것이 좋으나, 인덱스가 없는 조건절 컬럼이더라도 테이블을 액세스하고 나서의 최종 선택도를 계산할때 필요한 컬럼의 히스토그램은 수집한다. ( 다른 집합과의 조인 순서 및 조인 방식 결정 )
- 히스토그램이 불필요한 경우
|
1) 컬럼 데이터 분포가 균일
2) Unique하고 항상 등치조건으로만 검색되는 컬럼
3) 항상 바인드 변수로 검색되는 컬럼
|
- 관련 인자는 dbms_stats.gather_table_stats의 method_opt이다.
- 8i, 9i 기본값은 for all columns size 1 : 모든 컬럼에 대해 히스토그램을 수집하지 말라는 의미이다.
- 10g 기본값은 for all columns size auto : 오라클이 모든 컬럼에 대해 skew 여부를 조사해서 버킷 개수를 결정하는 의미이다. auto는 해당 컬럼이 조건절에 사용되는 비중까지 고려한다. ( sys.col_usage$뷰를 참조)
|
SQL> desc col_usage$;
Name Null? Type
|
- 10g dbms_stats 기본 동작 방식이 변경되어 히스토그램으로 인해 실행계획이 나쁜쪽으로 바뀌거나 통계정보 수집 시간이 늘어나는 문제점이 발생할 수 있다.
- 대용량 테이블일때 관리자가 직접 히스토그램 수집 컬럼을 아래와 같이 지정해 주는것이 좋다.
|
method_opt => 'for columns col1 size 20 col2 size 254 col3 size 100'
|
- dbms_stats.set_param 프로시저를 통해 기본값을 설정할 수 있다.
h1. 4. 데이터 샘플링
- 샘플링 비율을 높일수록 통계정보의 정확도는 높아지고, 통계정보 수집시간은 더 소요된다.
- 샘플링 비율 조정하기 위해 dbms_stats.estimate_percent 인자를 사용한다. ( 일정 비율 이상이면 대개 충분한 신뢰 수준에 도달, 5%에서 시작해 값을 늘려가며 적정 크기를 결정한다.)
- 블록 단위 샘플링 : block_sample인자를 통해 결정한다. 블록 단위 샘플링이 더 빠르고 효율적이지만 데이터 분포가 고르지 않을 때 정확도가 떨어진다. (기본값 : 로우 단위 샘플링 )
- 안정적인 통계정보의 필요성 : 전수검사에 비해 SQL성능을 불안정하게 만든다. 컬럼에 Null값이 많거나 데이터 분포가 고르지 않을때 샘플링 방식은 부정확한 통계정보를 생성한다.
- [참고] 선택도를 구하는 공식의 세가지 구성요소 : Null값을 제외한 로우 수, Distinct Value 개수, 총 레코드 개수
- 해시 기반 알고리즘으로 NDV 계산 - 11g
: 데이터 분포가 고르지 않은 상황에서 샘플링 방식을 사용하여 정확한 통계정보를 수집할 수 없는 문제를 해결하기 위해 11g는 해시 기반의 알고리즘을 고안했다.
: 소트를 수행하지 않기 때문에 대용량 파티션 또는 테이블 전체 스캔을 하더라도 기존 샘플링 방식보다 빠르다.
: 전체를 대상으로 NDV를 구하므로 정확도가 100%에 가깝다.
h1. 5. 파티션 테이블 통계 수집
- 파티션 테이블일 때 테이블 레벨 통계와 파티션 레벨 통계를 따로 관리한다.
- 파티션 레벨 통계 : Static Partition Pruning이 작동될 때 사용된다. 결합 파티션일 때는 서브파티션 레벨로 통계를 관리 할 수 있다.
- 테이블 레벨 통계 : Dynamic Partition Pruning이 작동될 때 사용된다. 쿼리에 바인드 변수가 사용됐거나, 파티션 테이브링 NL조인에서 Inner쪽 테이블일 경우, 파티션 키에 대한 조건절이 없을 때도 테이블 레벨 통계가 사용된다.
- dbms_stats은 global통계를 별도로 수행하는 반면 anlyze는 파티션 통계를 가지고 global 통계를 유추하므로 부정확하다.
- granularity 옵션 사용하여 통계 수집한다.
|
|
- 대용량 파티션 테이블에 대한 통계 수집 효율성을 높일 방안과 전략이 필요하다.
- 10g 이하 버전 : 최근 파티션만 통계를 수집하고 나서 테이블 전체 통계를 한 번 더 수행한다.
|
begin
dbms_stats.gather_table_stats ('ods', 'order'
, partname => 'order_part_200912'
, granularity => 'partition'
, estimate_percent => 20
);
end;
begin
dbms_stats.gather_table_stats ('ods', 'order'
, granularity => 'global'
, estimate_percent => 20
);
end;
|
h2. 1. NDV를 제외한 Incremental Grobal 통계 - 10.2.0.4
- 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를 제외한 나머지 테이블 레벨 통계는 방금 수집한 새 파티션 통게와 다른 파티션의 기존 통계를 이용해 구한다.
|
- Unique 인덱스를 가진 컬럼이나 파티션 키 컬럼의 경우는 테이블 레벨 NDV도 갱신이 이루어진다.
: Unqiue 인덱스를 가진 컬럼 : 전체 레코드 개수가 NDV와 일치
: 파티션 키 컬럼은 파티션 레벨 NDV를 더한 값과 테이블 레벨 NDV가 일치
- 위 두 경우를 제외한 다른 컬럼의 테이블 레벨 NDV는 좀 더 넓은 주기로 통계를 수집하거나 테이블 전반에 많은 양의 추가/갱신이 일어날 때마다 한 번씩 수집해 주어야 한다.
- 새로운 값이 계속 입력되는 컬럼은 low_value/high_value를 매일 갱신해 주어야 한다. 기존 파티션 통계와 추가된 파티션 통계로 부터 테이블 레벨 통계를 구할 수 있다.
h2. 2. NDV를 포함한 완벽한 Incremental Grobal 통계 - 11g
- 11g에서 파티션 레벨 NDV를 이용해 Grobal NDV를 정확하게 구할 수 있다.
- 파티션 레벨 컬럼별로 synopsis라고 하는 별도의 메타 데이터를 관리하여 NDV를 포함한 완벽한 Incremental Grobal 통계수집 기능을 제공한다.
- sysnopsis : Distinct Value에 대한 샘플로 파티션마다 각 컬럼이 갖는 값의 집합을 보관했다가 이를 머지하여 Grobal NDV를 구한다. ( 기존에는 집합 개수만 보관했기 때문에 불가능하였다.)
- 기본적으로 비활성화되어 있으며, 테이블 또는 스키마 별로 활성화한다.
|
begin
dbms_stats.set_table_prefs ('ods', 'order', 'incremental', 'true');
end;
=> 아래와 같이 파티션 테이블에 대한 통계수집
=> 'global and partition' 옵션을 지정했지만 테이블 전체를 두번 읽지 않으며 통계 정보가 누락되었거나 stale 상태에 있는 파티션만 통계를 수집한다.
=> NDV를 제외한 파티션 레벨 통계와 sysnopsis를 이용해 테이블 레벨 Global 통계를 갱신한다.
begin
dbms_stats.gather_table_stats ('ods', 'order'
, granularity => 'global and partition'
, estimate_percent => 20
);
end;
|
h1. 6. 인덱스 통계 수집
- 테이블 통게를 수집하면서 cascade 옵션을 true로 설정하여 해당 테이블에 속한 모든 인덱스 통계도 같이 수집한다.
- 인덱스 마다 gather_index_stats 프로시저를 따로 수행하는 것과 일량은 동일하다.
- 인덱스는 이미 정렬된 상태여서 소프 연산이 불필요하기 때문에 통계 수집에 걸리는 시간이 짧다. ( 샘플링 방식 불필요 )
- 그러나 테이블 통계와 같이 진행될 경우 테이블에 적용된 샘플링 비율이 인덱스 통계 수집에도 적용되는 문제가 있다.
- 이런 경우, 테이블 통계만 샘플링 방식을 사용하고, 인덱스는 전수 검사를 하도록 각기 통계를 수집하는 것이 좋다.
|
begin
dbms_stats.gather_table_stats ('user', 'big_table', cascade=>false, estimate_percent=>10);
end;
dbms_stats.gather_index_stats ('user', 'big_table_pk', estimate_percent=>100);
dbms_stats.gather_index_stats ('user', 'big_table_x1', estimate_percent=>100);
|
- 10g부터는 인덱스를 처음 생성하거나 재생성할 때 인덱스 통계가 자동 수집된다. (_optimizer_compute_index_stats = true )
h1. 7. 캐싱된 커서 invalidation
- no_invalidate 옵션에 따라 통계를 수집한 테이블과 관련된 SQL커서의 무효화시점이 달라진다.
1) false : 통계정보 변경시 관련된 SQL커서들이 즉시 무효화되고, 첫 번째 수행하는 세션에 의해 새로 갱신된 통계정보를 이용한 실행계획이 로드된다.
2) true : SQL커서가 자동으로 Shared Pool에서 밀려났다가 다시 로드될 때 새로 갱신된 통계정보를 사용한다. 통계정보 변경시 관련된 SQL 커서들을 무효화하지 않는다.
3) dbms_stats.auto_invalidate : 통계정보 변경시 관련된 SQL커서들이 한꺼번에 무효화하지 않고 정해진 시간 동안 조금씩 무효화한다. ( 무효화된 커서들이 동시에 수행되면서 하드파싱에 의한 라이브러리 캐시 경합을 방지하기 위하여 10g에서 도입한 기능)
_optimizer_invalidation_period파라미터에 무효화 소요 시간을 설정한다. ( 기본값 : 18000초 )
4) 기본값 : 9i => false, 10g => dbms_stats.auto_invalidate
h1. 8. 자동 통계 수집
- 10g부터 매일밤 10기 부터 다음날 아침 6시까지 모든 사용자 오브젝트에 대한 통계를 자동 수집하도록 Job이 등록되어있다.
- gather_stats_job에 의해 자동 수행되며 통계정보가 없거나 통계 테이블의 state_stats컬럼을 참조하여 통계정보 수집 후 DML이 많이 발생한 모든 오브젝트를 대상으로 한다.
- gather_stats_job : 데이터베이스 생성시 자동 등록되며, Maintenance 윈도우 그룹에 의해 등록된 윈도우가 열릴 때마다 스케쥴러에 의해 수행된다.
h2. 1. 통계정보 갱신 대상 식별
- 테이블 모니터링 기능
- 9i는 nomonitorting이 기본값으로 필요한 테이블만 monitoring옵션을 지정한다 ( alter table emp monitoring; )
- 10g는 모든 테이블 모니터링 한다,
- DML발생량 모니터링 : statistic_level이 typical, all일 때 monitoring옵션이 지정된 테이블에서 발생하는 DML 발생량을 모니터링
- 모니터링 대상 테이블이 10%이상 변경이 발생했을때 해당 테이블을 stale 상태로 변경 ( *_tab_statistic 뷰의 stale_stats = 'YES' )
- stale 상태인 테이블들에 대해 통계정보 새로 수집 : gather_database_stats 또는 gather_schema_stats프로시저를 호출하면서 option인자에 'gather_stale' 또는 'gather_auto' 지정한다.
- 11g에서는 stale상태로 바뀌는 임계치를 오브젝트별로 조정 가능하다.
- 테이블 변경 사항은 Shared Pool에 모았다가 SMON이 주기적으로 데이터 딕셔너리에 반영하는 시점이후 *_tab_modification과 *_tab_statistic 뷰의 stale_stats컬럼이 변경된다.
- dbms_stats.flush_database_monitoring_info 프로시저를 호출하면 현재 변경사항이 딕셔너리에 바로 반영된다.
- gather_stats_job 등록상태 조회 : 457page
- Maintenance 윈도우 그룹 등록상태 조회 : 458page
h2. 2. 자동 통계 수집 기능 활용 가이드
- 중대형급 이상 데이터베이스의 경우 10g에서 제공하는 자동 통계 수집 가능은 사용하지 않는것이 좋다.
- Maintenance 윈도우 이내에 통계 수집이 완료되지 않은 경우에 시스템이 불안정한 상태에 빠질 수 있다.
- 오브젝트별 전략을 세우고 짧은 시간 내에 정확하고 안정적인 통계정보를 수집할 수 있도록 별도의 스크립트를 준비한다.
- 11g부터 Statistics Preference 기능을 이용하여 오브젝트별 통계정보 수집 선택사양을 설정한다.
h1. 9. Statistics Preference
- gather_stats_jobs를 활성화한 상태에서 테이블 또는 스키마별로 통계 수집 방법을 따로 설정한다.
- 시스템 여건과 테이블 특성에 맞는 통계수립 정책이 자동 통계 수집 기능에 반영되었다.
- dbms_stats.set_table_prefs, dbms_stats.set_schema_prefs 프로시저를 사용하여 설정한다. ( 460 page )
- perference 설정 내용 조회 : select * from dba_tab_stst_prefs;
h2. 문서에 대하여
* 최초작성자 : [이신재]
* 최초작성일 : 2010년 11월 11일
* 이 문서는 [오라클클럽|http://www.gurubee.net] [대용량 데이터베이스 스터디] 모임에서 작성하였습니다.
* {color:blue}{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*{color}