꿈꾸는 개발자, DBA 커뮤니티 구루비

전략적인 통계수집 정책의 중요성
DBMS_STATS
컬럼 히스토그램 수집
데이터 샘플링
파티션 테이블 통계 수집
인덱스 통계 수집
캐싱된 커서 Invalidation
자동 통계 수집
자동 통계 수집 기능 활용 가이드
Statistics Preference

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

실제 통계 정보를 수집 하고 , DB 관리자들이 효과적인 통계 정보 수집전략을 세우는 것을 목표

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

부정확한 통계정보는 잘못된 실행 게획을 실행 하게 된다.
EX) 엉둥한 인덱스를 탄다, NL 조인이 HASH JOIN으로 실행..

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

중요 거래 테이블일 경우 통계 정보 수집일자가 대부분 오래된 상태라면 성능 문제가 발생할 우려가 있다.
DBA는 단순 백업&복구, 오브젝트 생성관리, 트러블 슈팅 및 그이 상으로 중요한 역활은 통계정보 수집 정책을 세우고 그에 따라 통계 정보를 안정적으로 운영 관리하는데 있다.
EX) 문제없던 쿼리가 갑자기 악성 SQL로 돌변했할수 있음

3) 통계정보 수집시 고려사항

시간 : 부하가 없는 시간대에 가능한 빠르게 수집을 완료해야 함
샘플 크기 : 가능한 적은 양의 데이터를 읽어햐 함
정확성 : 전수 검사할 때의 통계치에 근접해야 함
안정성 : 데이터에 큰 변화가 없을 경우 매번 통계치가 바뀌지 않아야 함

만약 샘플 크기를 최소화하더라도 정확성, 안정성을 확보 할 수 있다면 시간을 크게 줄일 수 있지만 그렇지 않다면 시간이 오래 걸리더라도 전수 검사를 해야함

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

통계정보의 중요성은 무엇보다 좋은 실행계획을 통해 쿼리 성능을 높이는데 있음
CBO를 사용하는 한 통계정보를 수집하지 않을 수 없다.
통계정보를 주기적으로 수집하면서도 안정적으로 운영되는 시스템이야말로 최선

DBMS_STATS

Analyze 명령어 대신 DBMS_STATS 패키지를 사용하며 특히 파티션 테이블/ 인덱스 일때는 반드시 사용
446~447 참조

컬럼 히스토그램 수집

나은 실행 계획을 수립 하는데 도움이 되지만 수집*관리 비용이 만만치 않으므로 필요한 컬럼에 대해서만 수집해야 하며 조건 절에 자주 사용 되면서 편중된 데이터 분포를 갖는 컬럼이 주 대상이다.
인덱스 컬럼에 히스토그램을 수집해야 하며, 인덱스가 없는 조건절 컬럼이라도 테이블을 액세스하고 나서의 최종 선택도를 계산할때 필요한 컬럼의 히스토그램은 수집한다

히스토그램이 불필요

컬럼 데이터 분포가 균일
Unique하고 항상 등치조건으로만 검색되는 컬럼
항상 바인드 변수로 검색되는 컬럼

dbms_stats.gather_table_stats에서 컬럼 히스토그램의 수집과 관련된 인자는 method_opt이다.
8i, 9i 기본값은 'for all columns size 1' : 모든 컬럼에 대해 히스토그램을 수집하지 말것.
10g 기본값 'for all columns size auto': 오라클이 모든 컬럼에 대해 skew 여부를 조사해서 버킷 개수를 결정하라는 뜻이다.
auto가 skewonly와 다른 점은 해당 컬럼의 조건절에 사용되는 비중까지 고려해서 결정한다.

10g에서 dbms-stats의 기본 동작 방식이 바뀐 사실을 모른데 업그레이드를 할 경우 없던 히스토그램이 생길수 있으며
주요 sql의 실행게획이 오히려 나쁜쪽으로 변경 된다던지, 수집 시간이 늘어 날수 있다.
대용량 테이블일 경우는 관리자가 직접 히스토그램 수집 컬럼을 변경하는 것이 바람직 하다.
method_opt => 'for columns col1 size 20 col2 size 254 col3 size 100'

데이터 샘플링

샘플링 비율을 높일수록 통계 정보의 정확도는 높아지지만 수집하는데 더 많은 시간이 소요 된다.

샘플링 비율

dbms 샘플링 비율을 조정 하기 위해 estimate_percent 인자를 사용한다.
모든 테이블을 조상 대상으로 삼기 어려우므로 가장 큰 몇몇 테이블만 설정 해도 큰효과를 얻을수 있다.
5%에서 시작값을 늘려가며 몇번의 통계 수집을 하다보면 적정 크기를 결정 할 수 있다.

블록 단위 샘플링

block_sample인자를 통해 블록 단위로 할지 로우 단위로 할지 결정
기본값이 로우 단위이나 블록 단위 샘플링이 더 빠르고 효과적임 그러나 데이터 분포가 고르지 않을 경우 정확도가 떨어짐

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

샘플링 방식을 사용하면 매번 통계치가 다르게 구해질 수 있고 이는 실행 계획에 영향을 미쳐 SQL의 성능을 불안전 하게 한다.
선택도 구하는 공식의 세가지 구성요소는 NULL제외한 로우수, Distinct Value 개수, 총 레코드 개수이다.
그러므로 특히 NULL 값이 많거나 데이터 분포가 고르지 않을 경우가 불안정 하다고 할 수 있다.

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

분포가 고르지 않는 상항에서 샘플링 방식을 하용 하면 매번 다르게 구해질 수 있어 안정적인 실행 계획을 기대하기 어렵다.
그래서 11g에서는 해시기반의 새로운 알로그림을 개발 하였고, 대용량 파티션 또는 테이블전체를 스캔하더라도 기존에 샘플링 방식을 사용 할 때보다는 오히려 빠른 속도를 낼 수 있게 되었다.
소트를 수행 하지 않고, 전체 대상으로 NDV를 구하므로 정확도는 당연 100%에가깝다.

파티션 테이블 통계 수집

파티션 레벨 통계: Static Partition Pruning이 작동될 때 사용된다.
결합 파티션일 때는 서브파티션 레벨로 통계를 관리 할 수 있다

테이블 레벨 통계 : Dynamic Partition Pruning이 작동될 때 사용된다.
쿼리에 바인드 변수가 사용됐거나, 파티션 테이브링 NL조인에서 Inner쪽 테이블이면 엑세스해야 할 대상 파티션 목록을 쿼리 최적화 시점에 정할 수 없기 때문이다.
또한 파티션 키에 대한 조건절이 없을 때도 테이블 레벨 통계가 사용된다.

dbms_stats은 global통계를 별도로 수행하는 반면 anlyze는 파티션 통계를 가지고 global 통계를 유추하므로 부정확하다
dbms_stats 패키지를 이용하여 파티션 테이블의 통계를 수집 할때는 granularity 옵션을 신중히 선택

global : 테이블 레벨 통계 수집
partition : 파티션 레벨 통계 수집 ( 테이블 레벨 통계 수집이 되어있지 않을경우, 파티션 레벨 통계로부터 추정된 값으로 테이블 레벨 통게를 설정한다.)
subpartition : 서브 파티션 레벨 통계 수집
global and partition : 테이블과 파티션 레벨 통계 수집 ( 테이블 레벨 NDV 정보를 정확하게 수집하기 위해 global 통계를 위한 쿼리를 한번 더 수행한다.)
all : 테이블, 파티션, 서브 파티션 레벨 통계 수집
auto : 파티션 유형에 따라 오라클이 결정

대용량 파티션 테이블에 대한 통계 수집 효율성을 높일 방안과 전략이 필요하다.
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

NDV를 제외한 Incremental Grobal 통계 - 10.2.0.4

10.2.0.4 에서 granularity인자에 선택 할 수 있는 값으로서 'approx_global and partition' 추가 되었다.
gobal and partition과 다른 점은 테이블 통계를 위한 쿼리를 따로 수행하지 않고 파티션 레벨 통계로부터 집계한다는데 있다.
컬럼 NDV와 인덱스의 Distinct Key개수는 제외된다.

아래는 2009년 11월 파티션만 통계를 수집하고(이때 해시 기반 NDV 계산 기능이 효과를 발휘함), ndv를 제외한 나머지 테이블 레벨 통계는 방급 수집한 새 파티션 통계와 다른 파티션의 기존 통계를 이용해 구한다.


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

NDV를 그대로 두지만 경우에 따라서 갱신이 이루어 진다.
unique 인덱스를 가진 컬럼은 전체 레코드 개수가 ndv와 일치
파티션 키 컬럼은 파티션 레벨 NDV를 더한 값과 테이블 레벨 NDV가 일치

위의 2~3의 경우를 제외 하고는 NDV를 그렇게 자주 갱신해 주지 않어라도 문제는 없다.
(좀 더 넓은 주기로 테이블 레벨 통계를 수집 하거나 테이블 전반에 많은 양의 추가/갱신이 일어날 때마다 한 번씩 수집해 주어야 한다.)

새로운 값이 계속 입력 되는 컬럼은 NDV보다 주로 low_value/high_value때문에 문제가 발생한다.
이 두 통계치는 기존 파티션 통계와 추가된 파티션 통계로 부터 정확한 테이블 레벨 통계를 구할 수 있다.

인덱스 통계 수집

테이블 통계를 수집 하면서 cascade 옵션을 true로 설정하면 테이블에 속한 모든 인덱스 통계도 같이 수집된다.
통계를 같이 수집 한다고 해서 빠린게 아니라 인덱스 마다 gather_index-stats 프로시저를 따로 수행 하는 것과 일량이 같다.
테이블 통계 수집에서는 소트연산이 발생
하지만 인덱스는 이미 정렬된 상태에서 소트 연산이 불필요하기 때문에 빠르다
테이블을 통계만 샘플링 방식을 사용하고, 인덱스는 전수 검사하도록 각기 통계를 수집해 주는 것이 좋다.


  begin
   --테이블 통계는 estimate mode
   dbms_stats.gather_table_stats ('user', 'big_table', cascade=>false, estimate_percent=>10);
    
   --인덱스 통계는 compute mode
   dbms_stats.gather_index_stats ('user', 'big_table_pk', estimate_percent=>100);
   dbms_stats.gather_index_stats ('user', 'big_table_x1', estimate_percent=>100); 
  end;
/

캐싱된 커서 Invalidation

no_invalidate 옵션을 어떻게 지정하느냐에 따라 통계를 수집한 테이블과 관련된 SQL 커서의 무효화 시점이 달라진다.
false : 통계정보 변경 시 관련된 SQL 커서들이 즉시 무효화, 따라서 곧이어 첫 번째 수행하는 세션에 의해, 새로 갱신된 통계정보를 이용한 실행계획이 로드된다
true: 통계정보 변경 시관련된 SQL커서들을 무효화 하지 않는다. SQL 커서가 자동으로 Shared Pool에서 밀려 났다가 다시 로드될 때 비로서 새로 갱신된 통계 정볼르 사용한다.
dbms_stats.auto_invalidate:통계정보 변경 시 관련된 SQL 커서들을 한꺼번에 무효화하지 않고 정해진 시간 동안 조금씩 무효화한다. 무효화된 수많은 커서가 동시에 수행되면서 하드파싱에 의한 라이브러리 캐시 경합이 발생하는 현상을 방지하려고 10g에서 도입된 기능이다.

중요한 것은 9i에서 false이던 기본 값이 10g에서 DBMS_STATS.AUTO_INVALIDATE로 바뀌었다.

자동 통계 수집

10g 부터 기본적으로 매일 밤 10부터 다음날 아침 6시까지 모든 사용자 오브젝트에 대해 통계를 자동 수집하도록 job이 등록돼있다.
gather_stats_job에 의해 자동 수행되며 통계정보가 없거나 통계 테이블의 state_stats컬럼을 참조하여 통계정보 수집 후 DML이 많이 발생한 모든 오브젝트를 대상으로 한다.
gather_stats_job : 데이터베이스 생성시 자동 등록되며, Maintenance 윈도우 그룹에 의해 등록된 윈도우가 열릴 때마다 스케쥴러에 의해 수행된다

통계정보 갱신 대상 식별

테이블 모니터링 기능
- 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

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

중대형급 이상 데이터베이스의 경우 10g에서 제공하는 자동 통계 수집 가능은 사용하지 않는것이 좋다.
Maintenance 윈도우 이내에 통계 수집이 완료되지 않은 경우에 시스템이 불안정한 상태에 빠질 수 있다.
오브젝트별 전략을 세우고 짧은 시간 내에 정확하고 안정적인 통계정보를 수집할 수 있도록 별도의 스크립트를 준비한다.
11g부터 Statistics Preference 기능을 이용하여 오브젝트별 통계정보 수집 선택사양을 설정한다.

Statistics Preference

gather_stats_jobs를 활성화한 상태에서 테이블 또는 스키마별로 통계 수집 방법을 따로 설정한다.
시스템 여건과 테이블 특성에 맞는 통계수립 정책이 자동 통계 수집 기능에 반영되었다.
dbms_stats.set_table_prefs, dbms_stats.set_schema_prefs 프로시저를 사용하여 설정한다. ( 460 page )
perference 설정 내용 조회 : select * from dba_tab_stst_prefs;