10gR2버전에서는 자동으로 수행하지만 그 이전 버전에서는 수동으로 통계정보를 수집해야 한다. 대부분 비업무시간에 동작하도록 Cron을 이용하여 통계정보를 수집한다.
수집 방법
- ANALYZE 명령
- DBMS_STATS 패키지
- 데이터 분포와 Histogram
통계 대상
인덱스 통계
- 인덱스 레벨 (정확)
- Leaf 블록 수
- Distinct Key 수
- Key 당 평균 Leaf 블록수
- Key 당 평균 데이터 블록수
- 인덱스 항목 수
- Clustering Factor
- 마지막의 ANALYZE 날짜 및 SAMPLE 크기
DBA_INDEXES
ALL_INDEXES
USER_INDEXES
테이블 통계
- 행 수
- 사용된 블록 수(정확)
- 빈 블록수 (정확)
- 블록 내의 평균 사용 가능공간
- Data Chaining 또는 Data Migration 된 행 수
- 행의 평균 길이
- 마지막의 ANALYZE 날짜 및 SAMPLE 크기
DBA_TABLES
ALL_TABLES
USER_TABLES
ANALYZE
Syntax
아래 Syntax의 표기법은 BNF와 EBNF이 약간 변형된 형태이므로 표기법을 알면 이해하기 쉽다. 형식화 되지 않은 문법을 표기할 때 거의 모든 곳에서 사용하는 표기법이다. 아래에서 간단하게 설명한다.
각진 사각형 : 필수 요소
둥근 사각형 : 다른것으로 치환 되거나 기호
갈라지는 선(택일) : 그 중 하나
기존 선 위에 갈라 지는 선 : 옵션
전체적인 Analyze 의 Syntax 이다. 치환되는 다른 것들은 위 링크에 자세히 나와있다.
- Analyze 명령어는 Table, Index, Cluster 을 대상으로 사용한다.
- COMPUTE, DELETE, ESTIMATE
COMPUTE : 새롭게 통계 정보를 생성 (*)
DELETE : 통계정보 삭제
ESTIMATE : 기존 통계정보를 이용하여 분석(*)
아래는 텍스트 형태의 BNF 이다. 아무래도 도표형식이 보기 편하다.
갈라지는 선 { }
택일: |
옵션 : []
_clause : 다른 문장으로 치환됨을 의미 (본래의 표기법은 ::= 으로 되어 있다.)
ANALYZE
{ TABLE [ schema. ]table
[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]
| INDEX [ schema. ]index
[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]
| CLUSTER [ schema. ]cluster
}
{ compute_statistics_clause
| estimate_statistics_clause
| validation_clauses
| LIST CHAINED ROWS [ into_clause ]
| DELETE [ SYSTEM ] STATISTICS
} ;사용 예
emp 테이블의 통계정보를 새로 생성
ANALYZE TABLE emp COMPUTE STATISTICS;
emp 테이블의 기존 통계정보를 이용하여 생성
ANALYZE TABLE emp ESTIMATE STATISTICS;
ANALYZE TABLE emp
-- 퍼센트
삭제
ANALYZE TABLE orders DELETE STATISTICS;
단점
위의 Syntax 에서 확인 할 수 있듯이 Schema 단위의 통계정보 수집이 불가능하고 Table, index, cluster 단위로 가능하다.
DBMS_STATS
DBMS_STATS는 Oracle8i 에서 도입되었다.
특징 및 장점
- ANALYZE에서 미지원하는 Schema 단위의 통계 수집이 가능
- 데이터베이스 전체에 대한 통계 수집 가능
- 통계수집시 병렬작업 지원
- 많은 subprogram을 포함하고 있음
DBMS_STATS의 Subprogram
DBMS_STATS는 패키지로 많은 Subprogram이 있다.
자세한 리스트는 이 링크를 참조한다. Summary of DBMS_STATS Subprograms
그중 통계정보의 수집과 관련된 Procedure는 아래와 같이 Index, Table, Schema, Database, System 단위의 수집 기능을 제공한다.
STATTAB 생성
통계정보를 수집하기 위해서는 통계정보가 저장될 테이블을 생성해한다. 그 테이블을 stattab 이라고 간단하게 말하는데 stattab 생성 프로시져가 존재한다.
CREATE_STAT_TABLE Procedure
DBMS_STATS.CREATE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL);
사용 예
테이블스페이스의 값은 생략해도 된다.
EXCUTE DBMS_STATS.CREATE_STAT_TABLE (
ownname => 'scott',
stattab => 'stattab',
tblspace => 'users');
또는
Excute DBMS_STATS.CREATE_STAT_TABLE ('scott','stattab');
GATHER_TABLE_STATS
필수값으로 owname, tabname 은 무조건 넣어야 한다.
Syntax
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL, block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1', degree NUMBER DEFAULT NULL, granularity VARCHAR2 DEFAULT 'DEFAULT', cascade BOOLEAN DEFAULT FALSE, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT FALSE);
그 외에도 정말 많은 기능이 있다. 자세한 내용은 링크를 참조하자.
데이터 분포와 Histogram
참고 : http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_46a.htm#2058892
데이터 분포의 비대칭성을 나타낼 수 있는 통계 정보가 필요하다.
일반적으로 아래같은 형태로 데이터가 존재할때 B 학교의 학생만 찾는다면 일반적인 통계정보는 사용할 수 없다. 이때 사용하는 것이 Histogram 이다.
학교 이름 학생수
A 90000
B 2
C 242
D 897
E 34
// 이런식으로 데이터가 분포되어 있는 것을 보고 skew 라고 한다.
위의 예제에서 B 학생을 찾을 때 일반적인 통계정보를 사용하는 CBO의 경우 Selectivity 계산을 하면 20%가 나온다. 그래서 CBO 는 5% 이상의 데이터를 찾는다는 결론이 나오므로 인덱스를 사용하지 않는다.
(B 학교 학생을 찾기 위해서는 반드시 인덱스를 타야 성능이 보장된다. 하지만 A 학교 학생을 모두 출력하고자 한다면 Index의 사용보다는 Full Scan이 더 나은 성능을 보장할 것이다.)
찾는 학교 B = 1
Cardinality = 5
Selectivity = 학교 B /
Cardinality
20% = 1 / 5
하지만 Bind 변수의 경우 실행계획이 생성된 이후에 Bind 변수를 처리하므로 Histogram을 사용하지 않는다.
특징 및 단점
- 데이터 분포의 불균형이 심한 컬럼에만 사용한다.
- 추가적 저장공간이 필요하다.
- Bind 변수 사용시 Histogram은 사용되지 않는다.
ANALYZE 에서의 Histogram
ANALYZE의 compute_statistics_clause 에서 정의된다.통계정보를 새롭게 생성해야 하는것을 의미하고 일반적인 통계보다 많은 자원과 시간이 걸린다.
사용 예
locations 테이블의 Histogram 을 생성하는 예제이다.
ANALYZE TABLE locations
COMPUTE STATISTICS FOR COLUMNS country_id
- TODO
DBMS_STAT 를 이용한 histogram 생성법
DBMS_STAT 의 다양한 예문