DBMS_STATS를 이용한 ORACLE ERP 전용 통계정보 수집 패키지 FND_STAT에 대한 정리이다.
통계정보 수집 관련 기본적이면서도 중요한 내용이 상세히 기재되어 있으나 한글 문서가 없는 관계로 간소화하여 정리하였다.
1. Docs 1586374.1
EBS의 통계정보 수집을 위한 FND_STATS 패키지에대한 내용이 수록되어 있는 문서이다.
2. FND_STAT Body Procedure들의 주요 Parameter
문서에서는 패키지 내의 프로시저들에서 주요하게 쓰이는 파라미터 일부에 대해 소개하고 있다.
아래는 문서에서 소개한 파라미터 및, 해당 파라미터 설명에 필요한 파라미터를 부가적으로 기재하였다.
2.1. schemaname
스키마 단위 수집 및 ALL 옵션을 통한 전체 스키마 수집
ex)
exec FND_STATS.ENABLE_SCHEMA_MONITORING ('ALL');
exec FND_STATS.ENABLE_SCHEMA_MONITORING ('SCHEMA_NAME');
2.2. degree
통계 수집 시 속도 향상을 위해 문서에서 제안하는 파라미터이다.
통계 수집 시 사용 될 병렬 처리 값이며,
특정 값의 지정을 해주지 않으면 cpu_count 및 parallel_max_servers 파라미터 중 더 작은 값을 사용한다.
2.3. options
통계정보 수집 세부 범위에 대한 파라미터이다.
입력 가능한 값은 아래와 같다.
GATHER : Default 값 이며, schemaname 파라미터에서 지정한 스키마의 모든 테이블 및 인덱스에 대하여 통계정보를 수집한다.
GATHER AUTO : 특정 비율(Default 10%) 이상 변경이 일어난 테이블과 해당 테이블의 인덱스들에 대하여 통계정보를 수집한다.
GATHER EMPTY : 통계정보 수집이 되어있지 않은 테이블 및 인덱스에 대해서만 수집한다.
LIST AUTO : 통계정보 수집은 수행하지 않으며, GATHER AUTO 수행 시 수집될 대상들에 대한 목록을 출력한다.
LIST EMPTY : 통계정보 수집은 수행하지 않으며, GATHER EMPTY 수행 시 수집될 대상들에 대한 목록을 출력한다.
2.4. hmode
History Mode에 사용되는 파라미터이다.
History Mode는 FND_STATTAB에 통계정보 데이터를 저장하며, 아래의 값으로 제어 가능하다.
FULL : 모두 저장
LASTRUN : 마지막 수집 분량까지만 저장
NONE : 저장하지 않음
2.5. estimate_percent
Auto Sampling Option에 사용되는 파라미터 이다.
문서에서는 기존에는 10%로 사용되어 왔다는 내용과 함께, 10g 이후로 DBMS_STATS.AUTO_SAMPLE_SIZE를 사용한다고 명시되어 있다.
다만, 실제 패키지를 확인해 보면 bug 11835452 으로 인해 11.1 버전부터 사용하도록 정의되어 있다.
문서에서는 필요에 따라 일부 테이블에는 더 높은 ESTIMATE_PERCENT를 부여하도록 안내하고 있다.
※ DBMS_STATS.AUTO_SAMPLE_SIZE
ESTIMATE 파라미터를 auto-sample algorithm을 이용하여 오라클에서 최적이라고 판단되는 크기로 지정한다.
DBMS_STATS.AUTO_SAMPLE_SIZE 세부 내용은 공식 문서에서 확인되지 않으나,
수집 후 몇가지 Metric에 의해(NDV가 지나치게 적거나 및 NULL이 많이 포함된 경우 등) 내부적으로 여러차례 재 수집 하는 경우도 있다고 알려져 있다.
2.6. invalidate
Cursor Invalidation 기능으로 소개된다.
Y or YES : Defualt 값이며, NO_INVALIDATE=FALSE 옵션으로 수행 된다.
N or NO : NO_INVALIDATE=TRUE 옵션으로 수행 된다.
통계정보 수집 시, 수집된 오브젝트에 종속된 Cursor들에 대해 Shared pool의 Library Cache에서 Invalidation 하여 기존 수행하였던 SQL이 하드 파싱이 일어 날 수 있다.
문서에서는 자주 수집되지 않았던 오브젝트를 수집하였다면 Cursor를 INVALIDATE후에 플랜을 새로 생성하도록 권장하고 있다.
DBMS_STATS.GATHER~ 패키지에서 NO_INVALIDATE 파라미터로 지원하며, 기본값은 DBMS_STATS.AUTO_INVALIDATE에 의해 오라클이 알아서 결정하도록 한다.
FND_STAT 에서는 invalidate 파라미터로 지원하며, 기본값은 'Y' (NO_INVALIDATE=FALSE) 이다.
※ 따라서 트랜잭션이 많은 시간대에 FND_STAT을 옵션 조정 없이 사용하면 갑작스럽게 Library Cache가 비워지면서 장애가 발생 할 수 있다.
3. Histogram
문서에서는 Histogram 수집에 대해 아래와 같이 설명 하고 있다.
1. 데이터가 불균형한 상태라면 Histogram 수집이 카디널리티 추정에 도움을 주어 유용하다.
2. Bind Peeking 및 이를 보완한 Adaptive Cursor Sharing을 통해 Histogram을 Bind 변수를 사용 할 경우에도 이용 할 수 있다.
3. Histogram을 수집하지 않으면, Bind 변수 값의 변경이 있더라도 플랜 변경이 일어나지 않는다.
4. NON-UNIQUE Index에서 특정 NDV가 전체 ROW의 1/75(약 1.33%)를 차지한다면 Hitogram 수집을 하는 것이 좋다. 이 때, 전체 Row는 3,000 Row 이상이어야 한다. FND_STATS 패키지에는 이러한 값을 조사하는 프로시저가 포함되어 있다.
※ "3,000 Row 정도라면 고려대상이 아니다"라고 보는것으로 예상된다.
패키지를 확인해 보면, 해당 조사에 사용되는 쿼리는 아래와 같은 형식이다.
4. Locking Statistics
4.1. 통계정보 수집 방지
원하는 경우 통계 정보가 수집되지 않도록 테이블을 지정할 수 있다.
FND_STATS.LOAD_XCLUD_TAB :
특정 테이블의 통계정보 수집을 방지한다.
설정된 테이블의 목록은 FND_EXCLUDE_TABLE_STATS 테이블에 저장된다.
DBMS_STATS.LOCK_TABLE_STATS :
특정 테이블의 통계정보 수집을 방지한다. UNLOCK_TABLE_STATS로 해제 할 수 있다.
설정된 테이블은 DBA_TAB_STATISTICS에서 STATTYPE_LOCKED가 NULL이 아닌 'ALL'로 지정되어 있다.
DBMS_STATS.LOCK_SCHEMA_STATS :
특정 스키마의 통계정보 수집을 방지한다. UNLOCK_SCHEMA_STATS로 해제 할 수 있다.
스키마 자체에 설정하는 것이 아닌, 현재 스키마가 소유한 모든 테이블에DBMS_STATS.LOCK_TABLE_STATS을 설정한다.
참고사항으로 DBMS_STATS.GATHER~ 로 통계를 수집하는 경우,
FND_STAT.LOAD_XCLUD_TAB으로 통계정보 수집을 막아 두어도 수집이 가능하다. 문서에 따르면 이는 의도된 것이며, DBMS_STATS를 수동으로 수행하는 경우에는 FND_STATS에 영향을 받지 않도록 했다고 한다.
4.2. 대상 선정
문서에서는 아래와 같은 경우 통계정보 Lock을 고려할 수 있다고 한다.
매우 큰 테이블이며 데이터가 변하지 않는(Static) 경우.
자주 변경되지 않는 테이블의 경우 굳이 통계정보 수집이 필요하지 않으며, 큰 테이블의 경우 통계수집 자체에 큰 비용이 들어가므로 한번 수집 후 Lock을 고려할 수 있다.
휘발성 테이블
크기가 자주 변하는 테이블의 경우 통계정보 Lock을 고려할 수 있다.
※ Dynamic Sampling을 이용하거나 해당 테이블 사용되는 시간 대중 가장 부하가 큰 시점(또는 가장 많은 데이터를 가진 시점)의 통계를 수집하고 Lock 설정을 하는 사이트들이 있다.
Temporary/Interim 테이블
일정 배치 작업 시에만 사용 되는 테이블의 경우 0건인 순간에 통계정보 수집이 될 수 있으므로 Lock을 고려할 수 있다.
Global Temporary 테이블은 원래 통계정보 수집 대상이 아니다.
※ 휘발성 테이블 및 Temporary/Interim 테이블의 경우.
Dynamic Sampling을 이용하거나 해당 테이블 사용되는 시간 대중 가장 부하가 큰 시점(또는 가장 많은 데이터를 가진 시점)의 통계를 수집하고 Lock 설정을 하는 사이트들이 있다.
특히 Temporary 테이블의 경우 FND_STATS.SET_TABLE_STATS 또는 DBMS_STATS.SETTABLE_STATS 를 통해 수동으로 특정 값으로 지정해버리는 방법도 권장하고 있다.
※ Global Temporary 테이블의 경우.
문서에서는 혹시나 통계정보가 수집되어 있다면 DBMS_STATS.delete_table_stats를 사용하여 통계 삭제 및 Dynamic Sampling을 사용하도록 권장한다.
12c 부터 세션별로 수집 및 세션 종료시 수집데이터가 삭제되는 기능이 추가되었다.
DBA_TAB_STATISTICS의 SCOPE 컬럼을 통해 확인 할 수 있다.
5. Dictionary와 Fixed Objects의 통계정보 수집
FND_STATS는 Dictionary와 Fixed Objects의 통계정보 수집을 하지 않는다.
5.1. Dictionary 통계정보
Dictionary 통계 정보는 아래의 경우에 수동으로 수집을 고려 할 수 있다.
DB Upgrade, ERP Upgrade 등의 대량의 DDL 작업 이후
AWR 출력 시 Recursive SQL의 Wait Time이 긴 경우
5.2. Fixed Objects 통계정보
v$뷰에 사용되는 데이터는 in-memory 구조인 x$를 이용한다.
x$에 대한 통계 정보는 오라클이 기본적으로 가지고 있으므로 dynamic sampling을 수행하지 않는다. 즉, v$뷰에 보이는 데이터가 많이 변경된 경우에는 최선의 플랜이 생성되지 않을 수 있다.
Fixed Objects의 통계 정보는 아래의 경우에 수집을 고려 할 수 있다.
주요 데이터베이스 또는 애플리케이션 업그레이드한 경우.
플랫폼 업그레이드(특히 Exadata)한 경우.
새로운 애플리케이션 모듈이 배포된 경우.
V$ 뷰에 대한 쿼리 시 성능 문제가 있는 경우.
작업 부하 또는 세션 수의 상당한 변화가 있는 경우.
데이터베이스 구조 변경 후 (예를 들어 SGA/PGA의 값이 매우 상이하게 변경된 경우)
6. Incremental Statistics for Partitioned Tables
DBMS_STATS 패키지는 신규 파티션이나 변경이 있는 파티션에 대해서만 통계정보를 수집 할 수 있는 기능을 지원하며, FND_STATS으로 사용 역시 가능하다.
이를 사용하기 위한 조건은 아래와 같다.
파티션 테이블에 대한 INCREMENTAL 기본 설정(prefs) 값이 TRUE.
ex)
DBMS_STATS.GATHER_*_STATS의 GRANULARITY 파라미터 값이 "ALL" 또는 "AUTO".
ESTIMATE_PERCENT 파라미터를 Default 값인 AUTO_SAMPLE_SIZE 로 설정
