"오라클 파티션 테이블 통계정보 생성"의 두 판 사이의 차이
DB CAFE
(→파티션 테이블의 통계 수집 방법) |
(→파티션 테이블의 통계 수집 방법) |
||
(같은 사용자의 중간 판 5개는 보이지 않습니다) | |||
3번째 줄: | 3번째 줄: | ||
=== 파티션 테이블과 통계 === | === 파티션 테이블과 통계 === | ||
# 파티션된 테이블은 전체 오브젝트, 파티션, 서브파티션(존재할 경우) 3개 통계정보를 갖음. | # 파티션된 테이블은 전체 오브젝트, 파티션, 서브파티션(존재할 경우) 3개 통계정보를 갖음. | ||
− | |||
# 전체 오브젝트에 대한 통계는 GLOBAL 통계라 함. | # 전체 오브젝트에 대한 통계는 GLOBAL 통계라 함. | ||
#: - GLOBAL 통계는 전체 테이블을 대상으로 하므로 대용량 데이터베이스에 대량 리소스와 시간 소요 . | #: - GLOBAL 통계는 전체 테이블을 대상으로 하므로 대용량 데이터베이스에 대량 리소스와 시간 소요 . | ||
11번째 줄: | 10번째 줄: | ||
− | {| | + | {| align="center" class="wikitable" style="margin:auto;border-style:solid;border-width:5px;border-color:block;" |
|+ * 위 세 가지 통계를 수집하기 위해서는 DBMS_STATS 패키지 granularity 옵션 사용 | |+ * 위 세 가지 통계를 수집하기 위해서는 DBMS_STATS 패키지 granularity 옵션 사용 | ||
|- | |- | ||
! 옵션 !!설명 | ! 옵션 !!설명 | ||
|- | |- | ||
− | | AUTO ||# DBMS_STATS가 어느 레벨의 통계를 수집할지 자동으로 결정. | + | | AUTO || |
+ | # DBMS_STATS가 어느 레벨의 통계를 수집할지 자동으로 결정. | ||
# 디폴트 동작이며, DBMS_STATS의 set_param 프로시저로 변경할 수 있음 | # 디폴트 동작이며, DBMS_STATS의 set_param 프로시저로 변경할 수 있음 | ||
# AUTO는 통계가 없거나 기존 수집된 통계가 오래되어 Stale 상태일 경우 GLOBAL 통계를 포함한 모든 통계를 새로 수집 함. | # AUTO는 통계가 없거나 기존 수집된 통계가 오래되어 Stale 상태일 경우 GLOBAL 통계를 포함한 모든 통계를 새로 수집 함. | ||
|- | |- | ||
− | | ALL ||# GLOBAL, PARTITION, SUBPARTITION에 대해서 모두 통계 수집 . | + | | ALL || |
+ | # GLOBAL, PARTITION, SUBPARTITION에 대해서 모두 통계 수집 . | ||
# AUTO와 차이점: Stale 여부에 관계없이 전체 레벨의 통계를 수집한다는 점이 다름. | # AUTO와 차이점: Stale 여부에 관계없이 전체 레벨의 통계를 수집한다는 점이 다름. | ||
|- | |- | ||
| GLOBAL AND PARTITION || GLOBAL과 PARTITION 레벨 통계 수집. | | GLOBAL AND PARTITION || GLOBAL과 PARTITION 레벨 통계 수집. | ||
|- | |- | ||
− | | GLOBAL ||# GLOBAL 통계 수집. | + | | GLOBAL || |
+ | # GLOBAL 통계 수집. | ||
# 테이블이 큰 경우 전체 오브젝트를 스캔해야 하므로 오랜 시간 소요(샘플 통계 수집을 통해 단축 가능). | # 테이블이 큰 경우 전체 오브젝트를 스캔해야 하므로 오랜 시간 소요(샘플 통계 수집을 통해 단축 가능). | ||
|- | |- | ||
48번째 줄: | 50번째 줄: | ||
# 모든 파티셔닝된 오브젝트에 대해 AUTO나 ALL을 사용하여 통계를 수집하면 가장 이상적인 통계 수집. | # 모든 파티셔닝된 오브젝트에 대해 AUTO나 ALL을 사용하여 통계를 수집하면 가장 이상적인 통계 수집. | ||
# 그러나 대용량 파티션 테이블에 대해 GLOBAL 통계를 포함한 모든 레벨의 통계를 수집할 경우 대량 리소스 와 시간 소요. | # 그러나 대용량 파티션 테이블에 대해 GLOBAL 통계를 포함한 모든 레벨의 통계를 수집할 경우 대량 리소스 와 시간 소요. | ||
− | + | #:* GLOBAL 통계는 2개 이상의 파티션을 사용 하거나 | |
− | * GLOBAL 통계는 2개 이상의 파티션을 사용 하거나 | + | #:* 파티션 키 조건이 WHERE 조건절에 들어오지 않아서 파티션을 제거하는 PARTITION pruning이 일어나지 않을 경우 사용. |
− | * 파티션 키 조건이 WHERE 조건절에 들어오지 않아서 파티션을 제거하는 PARTITION pruning이 일어나지 않을 경우 사용. | ||
---- | ---- | ||
57번째 줄: | 58번째 줄: | ||
# 추정은 status 컬럼 값이 파티션1 (A, B, C), 파티션2(A, C, E, F)라는 사실을 모르고 단순 계산하기 때문에 부정확 . | # 추정은 status 컬럼 값이 파티션1 (A, B, C), 파티션2(A, C, E, F)라는 사실을 모르고 단순 계산하기 때문에 부정확 . | ||
# 반면 GLOBAL 통계를 실제 수집 하면 해당 테이블을 전체 스캔하면서 NDV를 구하기 때문에 (A,C) 값이 중복된다는 사실을 파악하여 정확하게 5(A,B,C,E,F)의 NDV를 산출한다. | # 반면 GLOBAL 통계를 실제 수집 하면 해당 테이블을 전체 스캔하면서 NDV를 구하기 때문에 (A,C) 값이 중복된다는 사실을 파악하여 정확하게 5(A,B,C,E,F)의 NDV를 산출한다. | ||
− | + | #:* PARTITION 통계는 WHERE 조건 절에 파티션 키 조건이 들어와서 단일 파티션이 선택되는 partition pruning이 발생할 경우 사용. | |
− | * PARTITION 통계는 WHERE 조건 절에 파티션 키 조건이 들어와서 단일 파티션이 선택되는 partition pruning이 발생할 경우 사용. | ||
− | --- | + | ---- |
=== 케이스별 통계 수집 옵션 사용 예제 === | === 케이스별 통계 수집 옵션 사용 예제 === |
2024년 7월 31일 (수) 19:57 기준 최신판
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_YM
1.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';