[튜닝] CBO 통계의 수집

삽질!/DATABASE 2013. 9. 17. 16:29

■ CBO 내부 구조

○ Estimator는 Cardinality, Selectivity, Cost를 계측
○ Plan의 조사는 정교한 내부 알고리즘에 의하여 일정 시간 내에 완료
○ 최종적으로 가장 Cost가 작은 Plan이 선택

◆ Cardinality
- 어떤 Row set에 속하는 Row들의 갯수
◆ Selectivity
- 어떤 Row set에 특정한 조건을 적용하는 경우 그 조건을 만족하는 Row들의 비율
- 쿼리의 술어(Predicate)나 컬럼값들의 분포도와 밀접한 관련이 있음.
◆ Cost
- 하나의 SQL문장을 수행하기 위해 필요한 리소스의 소요량(I/O횟수, CPU사용량, 메모리 사용량, 네트워크 전송량)
- Cardinality 및 Selectivity를 기반으로 계산

1 1 1 1 1 1 1 1 2 1

Cardinality : 값이 다른 것이 몇 개 있는가? 여기서는 카디널리티가 2임.
Selectivity : 특정 값이 뽑힐 확률. 2가 뽑힐 확률은 2/10 = 0.5임. 즉 여기서는 Selectivity가 0.5임.

■ CBO와 통계정보
○ Cardinality, Selectivity, 그리고 궁극적으로 Cost 계산을 위해서는 데이터의 정확한 통계 정보 필요
○ 통계정보는 Static Dictionary에 저장
- 자동으로 바뀌지 않아, 사용자에 의한 수집이 필요. 바꾸려면 DDL을 날려줘야 함.
- 두가지 방법 : Analyze명령 또는 DBMS_STATS 패키지 이용

◆ Analyze 명령

SQL> analyze table scott.emp compute statistics;
SQL> analyze table scott.emp estimate statistics;

통계정보를 새로 만들 때는 compute.
이전에 만들어져 있는 통계정보를 검증할 때는 estimate.

◆ DBMS_STATS 패키지

SQL> exec dbms_stats.gather_table_stats ('scott', 'emp');

◆ 히스토그램

SQL> analyze table scott.emp compute statistics for columns ename;

히스토그램은 특별한 통계정보로써, 데이터가 한쪽으로 쏠리면 기존의 통계정보는 먹지 않는데, 이때 사용할 수 있는 것이 히스토그램임.
단점은 히스토그램 만드는데 오랜 시간이 걸리고, 저장공간 또한 많이 차지하기 때문에 일반 통계정보를 만들어서 잘 작동하면 그거 쓰면 되고, 그게 만족스럽지 못할 때 그때 히스토그램을 쓰면 된다.

□ 히스토그램 가이드라인
- 데이터 분포의 불균형이 심한 컬럼에만 사용
- 추가적 저장공간이 필요하므로 상당한 성능 개선이 있는 경우에만 사용
- 바인드 변수를 사용하는 경우 Histogram은 사용되지 않음.

■ 정리
- 어떤 테이블에 1억건의 데이터가 들어있다는 통계정보가 있는데, 9천 건을 삭제해도 통계정보는 그대로이다. 이유는 DML이기 때문이다. 그러나 truncate같은 DDL을 날린다면 통계정보가 바뀐다. 그래서 통계정보만 바꿔주는 dbms_stats 패키지나 analyze명령을 날려주면 통계정보가 변경된다. 그러나 이거 한번 날려줄 때마다 서버에 부하가 심하다. 그리하여 업무시간에는 절대 통계정보를 업데이트 하면 안된다.
- 10g는 위 명령이 자동으로 1시간에 한번씩 업데이트 된다. 9i는 관리자가 수동으로 날려줘야 한다. 그러나 우리가 배웠던 cron을 이용하여 자동으로 업무 이외 시간에 돌려주게끔 하면 된다.
- 테이블이나 인덱스가 급격히 바뀌었을 때 통계정보를 변경해서 분석해주면 된다.