개요

방법

1. DBMS_STATS 패키지

DBMS_STATS 패키지는 다음과 같은 기능이 있습니다.

• 오브젝트 통계를 수집(Gather)하고 선택사항으로 현재 통계를 덮어쓰기 전에 백업 테이블로 저장 
• Data Dictionary에 저장된 통계를 잠그거나 잠금을 해제(Lock/Unlock)
• 오브젝트 통계를 파티션이나 서브파티션에서 다른 파티션이나 서브파티션으로 복사(Copy)
• 오브젝트 통계를 Data Dictionary로 복원(Restore) 
• Data Dictionary나 백업 테이블에 저장된 오브젝트 통계 삭제(Delete) 
• 오브젝트 통계를 Data Dictionary에서 백업 테이블로 export
• 오브젝트 통계를 백업 테이블에서 Data Dictionary로 import
• Data Dictionary나 백업 테이블에 저장된 오브젝트 통계를 얻음(Get) 
• Data Dictionary나 백업 테이블에 저장된 오브젝트 통계를 설정(Set)

통계 정보 수집 관련 주요 항목은 다음과 같습니다.

• DBMS_STATS.GATHER_DATABASE_STATS : 데이터베이스의 모든 객체의 통계 정보를 수집
• DBMS_STATS.GATHER_DICTIONARY_STATS : 스키마(SYS, SYSCAT) 객체의 통계 정보 수집
• DBMS_STATS.GATHER_INDEX_STATS : 인덱스의 통계 정보 수집 
• DBMS_STATS.GATHER_SCHEMA_STATS : 스키마의 모든 객체의 통계 정보 수집
• DBMS_STATS.GATHER_SYSTEM_STATS : 시스템의 통계 정보를 수집 
• DBMS_STATS.GATHER_TABLE_STATS : 테이블의 통계 정보를 수집

참고

DBMS_STATS 패키지에서 제공하는 아래의 다양한 프로시저에 대한 자세한 내용은 "Tibero tbPSM 참조 안내서" 의 "DBMS_STATS" 참고

• ALTER_STATS_HISTORY_RETENTION : 통계 정보 히스토리 보유 기간 변경. “PURGE_STATS” 참고 
• COPY_TABLE_STATS :테이블 및 로컬 인덱스의 원본 파티션 통계 정보를 대상 파티션의 통계 정보로 복사 
• CREATE_STAT_TABLE : 통계 정보를 저장할 통계 테이블을 생성 
• DELETE_COLUMN_STATS : 컬럼의 통계 정보를 삭제 
• DELETE_DATABASE_STATS : 데이터베이스에 있는 모든 테이블의 통계 정보를 삭제 
• DELETE_DICTIONARY_STATS : 모든 데이터 사전의 스키마(SYS, SYSCAT)의 통계 정보를 삭제 
• DELETE_INDEX_STATS : 인덱스의 통계 정보를 삭제 
• DELETE_SCHEMA_STATS : 스키마 전체의 통계 정보를 삭제 
• DELETE_SYSTEM_STATS : workload 시스템의 통계 정보를 삭제하고, 시스템의 통계 정보를 초기화 
• DELETE_TABLE_STATS : 테이블의 통계 정보를 삭제 
• DROP_STAT_TABLE : 통계 테이블을 삭제 
• EXPORT_COLUMN_STATS : 지정한 컬럼의 통계 정보를 통계 테이블에 저장 
• EXPORT_DATABASE_STATS : 데이터베이스에 있는 테이블의 통계 정보를 통계 테이블에 저장 
• EXPORT_INDEX_STATS : 지정한 인덱스의 통계 정보를 통계 테이블에 저장 
• EXPORT_SCHEMA_STATS : 스키마 전체의 통계 정보를 통계 테이블에 저장 
• EXPORT_SYSTEM_STATS : 시스템의 통계 정보를 통계 테이블에 저장 
• EXPORT_TABLE_STATS : 테이블의 통계 정보를 통계 테이블에 저장 
• GET_COLUMN_STATS : DD(Data Dictionary) 또는 통계 테이블에 있는 지정한 컬럼의 통계 정보 가져옴 
• GET_INDEX_STATS : DD(Data Dictionary) 또는 통계 테이블에 있는 지정한 인덱스의 통계 정보 가져옴 
• GET_TABLE_STATS : DD(Data Dictionary) 또는 통계 테이블에 있는 지정된 테이블의 통계 정보 가져옴 
• IMPORT_COLUMN_STATS : 통계 테이블에 있는 지정한 컬럼의 통계 정보를 데이터 사전에 IMPORT 
• IMPORT_DATABASE_STATS : 통계 테이블에 있는 모든 테이블의 통계 정보를 데이터 사전에 IMPORT 
• IMPORT_INDEX_STATS : 통계 테이블에 있는 지정한 인덱스의 통계 정보를 데이터 사전에 IMPORT 
• IMPORT_SCHEMA_STATS : 통계 테이블에 있는 스키마 전체의 통계 정보를 데이터 사전에 IMPORT 
• IMPORT_SYSTEM_STATS : 통계 테이블에 있는 시스템의 통계 정보를 데이터 사전에 IMPORT 
• IMPORT_TABLE_STATS : 통계 테이블에 있는 테이블의 통계 정보를 데이터 사전에 IMPORT 
• LOCK_TABLE_STATS : 해당 테이블과 소속된 인덱스, 파티션, 컬럼의 통계 정보 
• LOCK LOCK_SCHEMA_STATS : 해당 스키마에 소속된 테이블, 인덱스, 파티션, 컬럼의 통계 정보들 
• LOCK SET_COLUMN_STATS : DD(Data Dictionary) 또는 통계 테이블의 지정된 컬럼에 통계정보 변경
• SET_INDEX_STATS : DD(Data Dictionary) 또는 통계 테이블의 지정된 인덱스에 대한 임의의 통계 정보 변경 
• SET_TABLE_STATS : DD(Data Dictionary) 또는 통계 테이블의 지정된 테이블에 대한 임의의 통계 정보 변경 
• PURGE_STATS : 지정된 시점보다 오래된 통계 정보 히스토리들을 제거 
• RESTORE_SCHEMA_STATS : 스키마의 모든 객체의 통계 정보를 지정된 시간대(as_of_timestamp)로 복원 
• RESTORE_TABLE_STATS : 테이블의 통계 정보를 지정된 시간대(as_of_timestamp)로 복원 
• SET_PARAM : DBMS_STATS 패키지의 디폴트 파라미터의 값을 변경 
• SET_SYSTEM_STATS : 시스템의 통계 값을 수동으로 설정 
• UNLOCK_TABLE_STATS : 해당 테이블과 소속된 인덱스, 파티션, 컬럼의 통계 정보들에 대한 잠금 해제 
• UNLOCK_SCHEMA_STATS : 해당 스키마에 소속된 테이블, 인덱스, 파티션, 컬럼의 통계 정보에 대한 잠금 해제

1.2 설정 항목

DBMS_STATS별 설정 가능한 파라미터입다.

DBMS_STATS.SET_PARAM에 의해서 파라미터 설정 변경하는 방법입니다.

call dbms_stats.set_param('STAT_ESTIMATE_PERCENT',0) ;
call dbms_stats.set_param('STAT_METHOD_OPT','FOR ALL COLUMNS SIZE 1') ;
call dbms_stats.set_param('STAT_DEGREE',1) ;
call dbms_stats.set_param('STAT_NO_INVALIDATE','TRUE') ;

DBMS_STATS.GET_PARAM에 의해서 파라미터 값 확인하는 방법입니다.

ESTIMATE_PERCENT : select dbms_stats.get_param ('STAT_ESTIMATE_PERCENT') from dual;
METHOD_OPT : select dbms_stats.get_param ('STAT_METHOD_OPT') from dual;
DEGREE : select dbms_stats.get_param('STAT_DEGREE') from dual;
NO_INVALIDATE : select dbms_stats.get_param ('STAT_NO_INVALIDATE') from dual;

다음은 통계 정보를 수집할 때 사용하는 옵션들에 대한 설명입니다.

CALL DBMS_STATS.GATHER_TABLE_STATS(USER,'ARMY',estimate_percent=100);

TABNAME

처리할 테이블의 이름을 지정합니다.

IDXNAME

처리할 인덱스의 이름을 지정합니다.

PARTNAME

처리할 파티션이나 서브파티션의 이름을 지정한다. 값을 지정하지 않으면, granularity(granularity 항목 참고) 파라미터 값에 의해 전체 파티션 및 서브파티션에 대한 오브젝트 통계가 수집됩니다. (기본값 : NULL)

ESTIMATE_PERCENT

전체 중 몇 %의 데이터를 샘플링할 것인가를 결정하는 파라미터입니다. (기본값: get_param ('STAT_ESTIMATE_PERCENT'), 설정값: 0.000001 ~ 100)

GATHER_TABLE_STATS, GATHER_SCHEMA_STATS 등의 GATHER 계열 프로시저들에서 사용됩니다. 통계 정보의 정확도를 높이기 위해 샘플링 비율을 높이는게 좋지만 통계 수집 시간이 더 소요될 수 있습니다.

설정값이 0인 경우 Optimizer가 Table에 크기에 따라 적합한 Sampling rate를 결정하여 수행합니다. 다음은 Table row 개수에 따른 sampling rate에 대한 설명입니다.

샘플링은 수집 성능을 위해 재공됩니다. 만약 대용량 시스템이라면 Granularity값 조정도 함께 고려되어야 합니다.

BLOCK_SAMPLE

블록 샘플링 방식을 사용할지 결정하는 파라미터입니다. 로우 샘플링보다 빠르지만 정확도는 떨어집니다. (기본값 : FALSE)

METHOD_OPT

통계 정보와 히스토그램 수집 범위 결정합니다. (기본값 : get_param ('STAT_METHOD_OPT'))

• FOR ALL

전체 Column에 대해 수집을 합니다.

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

• FOR ALL INDEXED COLUMNS

해당 옵션을 사용하는게 무난합니다. 이는 Index 걸린 Column들만 수집하는 것인데 대부분의 경우는 Index 걸린 Column들만 수집하는 것인데 대부분의 경우는 Index 걸린 Column들이 선택도를 좌우하므로 크게 문제가 되지 않습니다.

• FOR COLUMNS

특정 Column에 대해 수집을 합니다.

FOR COLUMNS [size_clause] column [size_clause] [,column [size_clause]...]

• SIZE CLAUSE

Column 히스토그램 생성 여부를 선택합니다.

SIZE_CLAUSE := SIZE {integer | REPEAT | AUTO}

참고

히스토그램에 대한 자세한 내용은 “3.1. 히스토그램” 참고

DEGREE

병렬 쿼리의 처리 개수를 설정합니다. (기본값 : get_param('STAT_DEGREE'))

degree [설정값]

설정값이 NULL인 경우 CREATE TABLE, ALTER TABLE할 때 Table의 기본값을 사용합니다.

CASCADE('CASECADE_INDEXES'도 사용 가능)

Index에 대해서도 통계 정보를 수집합니다.

GATHER_SYS

SYS 스키마의 처리 여부를 지정합니다. (기본값 : FALSE)

NO_INVALIDATE

운영 중 통계 정보의 갱신 시 Cursor가 Invalidation되면 과다한 파싱으로 인하여 문제가 발생할 수 있기 때문에 no_invalidate 옵션으로 제어합니다. (기본값 : get_param('STAT_NO_INVALIDATE'))

 no_invalidate [FALSE | TRUE]

OPTIONS

처리할 인덱스의 이름을 지정합니다. 이 파라미터는 필수이다. 수집할 테이블의 기준을 정합니다. (기본값 : GATHER)

GRANULARITY

통계 정보는 Table, Index, Table 파티션, Index 파티션, column 레벨로 구분됩니다. Granularity는 이들 중 어떠한 레벨의 통계 정보를 수집할 것인지를 정하는 Statistics level을 결정한다. 만약 대용량 System을 운영 중이라면 Granularity 조정이 필수입니다.

 granularity VARCHAR2 DEFAULT '[ ALL | GLOBAL | PARTITION | GLOBAL AND PARTITION | 
APPROX_GLOBAL AND PARTITION | SUBPARTITION]'

FORCE

TRUE로 설정하면 테이블의 통계 정보가 잠겨 있어도 수집합니다.