수동통계수집(gathering statistics manually)

■ 통계를 수동으로 수집해야 하는 시기

 

다음에 대해 수동으로 수집해야 한다.
- 휘발성 객체

- batch 처리 작업에서 수정되는 객체: 배치 처리 작업의 일부로 통계를 수집

- external table, system통계, fixed object

- 새로운 객체: 객체 생성 후 통계 수집

 

external 테이블은 샘플링이 지원되지 않아 estimate_percent 옵션을 명시적으로 null 설정해야 한다.
external 테이블에 대한 데이터 조작이 허용되지 않기에 해당 파일이 변경될 때만 통계 수집하면 충분한다.

 

■ 수동 통계 수집

dbms_stats는 optimize에 필요한 통계만 수집하고 다른 통계 수집은 하지 않는다.

- 예를 들어 테이블이라면 num_rows, 데이터가 있는 block count, 평균 행 길이는 포함되지만 체인된 row수, 평균 사용 가능 공간, 사용되지 않은 데이터 블럭 수는 미포함한다.

 

analyze문의 compute와 estimate 절은 역호환성을 위해서만 지원된다.

analyze문의 validate, list chained, free list 블록에 대한 정보 수집은 계속 사용 가능하다.

 

■ 수동 통계 수집 프로시저

-gather_index_stats
-gather_table_stats
-gather_schema_stats
-gather_dictionary_stats
-gather_database_stats
-gather_system_stats

-gather_fixed_objects_stats

 

PROCEDURE GATHER_DATABASE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
 METHOD_OPT                     VARCHAR2                IN     DEFAULT
 DEGREE                         NUMBER                  IN     DEFAULT
 GRANULARITY                    VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 OPTIONS                        VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 GATHER_SYS                     BOOLEAN                 IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 GATHER_TEMP                    BOOLEAN                 IN     DEFAULT
 GATHER_FIXED                   BOOLEAN                 IN     DEFAULT
 STATTYPE                       VARCHAR2                IN     DEFAULT
 OBJ_FILTER_LIST                OBJECTTAB               IN     DEFAULT

 

PROCEDURE GATHER_SCHEMA_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
 METHOD_OPT                     VARCHAR2                IN     DEFAULT
 DEGREE                         NUMBER                  IN     DEFAULT
 GRANULARITY                    VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 OPTIONS                        VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 GATHER_TEMP                    BOOLEAN                 IN     DEFAULT
 GATHER_FIXED                   BOOLEAN                 IN     DEFAULT
 STATTYPE                       VARCHAR2                IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
 OBJ_FILTER_LIST                OBJECTTAB               IN     DEFAULT

 

PROCEDURE GATHER_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
 METHOD_OPT                     VARCHAR2                IN     DEFAULT
 DEGREE                         NUMBER                  IN     DEFAULT
 GRANULARITY                    VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 STATTYPE                       VARCHAR2                IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT

 

PROCEDURE GATHER_INDEX_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 INDNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 DEGREE                         NUMBER                  IN     DEFAULT
 GRANULARITY                    VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 STATTYPE                       VARCHAR2                IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT

 

PROCEDURE GATHER_DICTIONARY_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 COMP_ID                        VARCHAR2                IN     DEFAULT
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
 METHOD_OPT                     VARCHAR2                IN     DEFAULT
 DEGREE                         NUMBER                  IN     DEFAULT
 GRANULARITY                    VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 OPTIONS                        VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 STATTYPE                       VARCHAR2                IN     DEFAULT
 OBJ_FILTER_LIST                OBJECTTAB               IN     DEFAULT

 

PROCEDURE GATHER_FIXED_OBJECTS_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT

 

PROCEDURE GATHER_SYSTEM_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GATHERING_MODE                 VARCHAR2                IN     DEFAULT
 INTERVAL                       NUMBER(38)              IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT