오라클 파티션 테이블 통계정보 생성
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 파티션 테이블 통계 수집[편집]
1.1 파티션 테이블과 통계[편집]
- 파티션된 테이블은 전체 오브젝트, 파티션, 서브파티션(존재할 경우) 3개 통계정보를 갖음.
- 전체 오브젝트에 대한 통계는 GLOBAL 통계라 함.
- - GLOBAL 통계는 전체 테이블을 대상으로 하므로 대용량 데이터베이스에 대량 리소스와 시간 소요 .
- - GLOBAL 통계를 수집하지 않고 파티션 레벨 통계만 수집하여 운영하는 경우 있음(부정확한 실행계획으로 문제 발생 가능성 존재)
1.2 파티션 테이블의 통계 수집 방법[편집]
| 옵션 | 설명 |
|---|---|
| AUTO |
|
| ALL |
|
| GLOBAL AND PARTITION | GLOBAL과 PARTITION 레벨 통계 수집. |
| GLOBAL |
|
| PARTITION | PARTITION 레벨 통계만 수집하고, GLOBAL 통계는 PARTITION 통계를 추정(aggregate) 하여 생성 함. |
| SUBPARTITION | SUBPARTITION 레벨에서 통계 수집, 그 보다 상위의 PARTITION과 GLOBAL 통계는 SUBPARTITION 통계를 aggregate하여 생성 . |
- GLOBAL,PARTITION, SUBPARTITION 3가지 레벨로 통계를 수집할 수 있음.
- 아무런 옵션 없이 DBMS_STATS로 통계를 수집할 경우 granularity=>AUTO로 동작 됨
- => GLOBAL 통계를 포함한 모든 통계가 수집됨 .
- 추정(aggregate) 하여 생성 할때는 직접 전체 테이블을 스캔하는 대신 파티션 레벨에서 수집된 통계를 기준으로 상위 레벨의 Global 통계를 추정한다.
- => 예를 들어 1월 파티션과 2월 파티션이 각각 10만 건과 20만 건이라면, Global 통계의 NUM_ROWS는 30만 건으로 계산되어 입력.
- => 총 건수(NUM_ROWS)의 경우 이렇게 추정하여도 과거 파티션에 변동이 없는 한 정확한 값이 산정된다.
- NDV(Number of Distinct)의 경우, 예를 들어 status라는 컬럼값이 1월 파티션에는 'A' , 2월 파티션은 'A','B','C' 값을 가질 경우,
- Partition 레벨의 NDV는 1월 파티션이 1이고 2월 파티션은 3 이 된다.
- 그러나 GLOBAL 통계의 NDV값은 status 칼럼 값의 중복 여부에 따라 최소 3(A,B,C) 이 될 수도 있고 최대 4(A,A,B,C)가 될 수도 있다.
- => 따라서 aggregate할 경우 NDV 값은 부정확할 수 있음. 결국 GLOBAL 통계를 수집해야 함.
1.3 GLOBAL 통계 수집 한 경우[편집]
- 모든 파티셔닝된 오브젝트에 대해 AUTO나 ALL을 사용하여 통계를 수집하면 가장 이상적인 통계 수집.
- 그러나 대용량 파티션 테이블에 대해 GLOBAL 통계를 포함한 모든 레벨의 통계를 수집할 경우 대량 리소스 와 시간 소요.
- GLOBAL 통계는 2개 이상의 파티션을 사용 하거나
- 파티션 키 조건이 WHERE 조건절에 들어오지 않아서 파티션을 제거하는 PARTITION pruning이 일어나지 않을 경우 사용.
1.4 GLOBAL 통계를 수집하지 않고 PARTITION 레벨 통계만 수집했을 경우[편집]
- 추정은 status 컬럼 값이 파티션1 (A, B, C), 파티션2(A, C, E, F)라는 사실을 모르고 단순 계산하기 때문에 부정확 .
- 반면 GLOBAL 통계를 실제 수집 하면 해당 테이블을 전체 스캔하면서 NDV를 구하기 때문에 (A,C) 값이 중복된다는 사실을 파악하여 정확하게 5(A,B,C,E,F)의 NDV를 산출한다.
- PARTITION 통계는 WHERE 조건 절에 파티션 키 조건이 들어와서 단일 파티션이 선택되는 partition pruning이 발생할 경우 사용.
1.5 케이스별 통계 수집 옵션 사용 예제[편집]
- SALES 테이블의 SALE_YM컬럼을 파티션키로 월별 RANGE 파티셔닝 한 경우
1.5.1 GLOBAL 통계 사용 기준 예시[편집]
- WHERE 조건이 사용되지 않을 경우
-- 파티션 조건이 없는 경우
SELECT * FROM sales;
SELECT * FROM sales
WHERE sale_amt >= 2000
-- 단일 파티션이 아닌 4개 파티션을 액세스 => GLOBAL 통계 사용.
SELECT * FROM sales
WHERE sale_YM BETWEEN '201305' AND '201308'
-- 단일 파티션을 액세스하지만 BIND 변수를 사용 => GLOBAL 통계가 사용
SELECT * FROM sales
WHERE sale_YM = :sale_YM1.5.2 PARTITION 통계 사용 기준 예시[편집]
- 파티션 키 조건이 상수 값으로 사용,단일 파티션만 사용하며 PARTITION 통계 사용
SELECT * FROM sales
WHERE sale_YM = '201308'
1.6 GLOBAL 통계 수집 및 수집 여부 판단[편집]
- GLOBAL 및 PARTITION 통계를 수집한 후 수집된 통계 정보.
1.테이블 생성
CREATE TABLE SALES ( SALES_YM VARCHAR2 (6)
, SALES_NO NUMBER
, SALES_STATUS CHAR (1) NOT NULL
, CONSTRAINT SALES_PK PRIMARY KEY (SALES_YM, SALES_NO)
USING INDEX LOCAL TABLESPACE users
)
TABLESPACE users
-- 파티션
PARTITION BY RANGE(SALES_YM) (
PARTITION SALES_P201301 VALUES LESS THAN ('201302')
, PARTITION SALES_P201302 VALUES LESS THAN ('201303')
);2. 샘플 데이터 입력 : 월별 100건씩 입력
INSERT INTO SALES
SELECT to_char(add_months(to_date('20130101', 'YYYYMMDD'), ceil(level / 100)-1), 'YYYYMM') SALES_YM
, mod(level, 100) SALES_NO
, chr(64+round(dbms_random.value(1,ceil(level / 100) ))) SALES_STATUS
from dual
connect by level <= 2 * 100;
commit;3. GLOBAL + PARTITION 통계 모두 생성
exec dbms_stats.gather_table_stats(USER, 'SALES', GRANULARITY=>'GLOBAL AND PARTITION', CASCADE=>TRUE, NO_INVALIDATE=>FALSE);- 수집한 GLOBAL 통계 확인
- GLOBAL_STATS 칼럼이 YES인 경우 직접 테이블을 Full scan하여 GLOBAL 통계를 수집했음을 의미하며
- , NO는 직접 수집하지 않고 PARTITION 레벨 통계를 Aggregate하였음을 의미.
- 아래 결과는 GLOBAL_STATS 가 YES => 직접 GLOBAL 통계를 수집했다는 것.
SELECT table_name, num_rows, last_analyzed, global_stats
FROM user_tables
WHERE table_name = 'SALES';