DBMS_STATS 패키지

DBMS_STATS 패키지는 옵티마이저에서 스키마나 DB에 있는 모든 스키마 객체에 대한 통계뿐만 아니라 클러스터를 제외한 인덱스, 테이블, 컬럼과 파티션에 대한 통계정보를 수집하고 통계정보에 대한 검색, 수정, 익스포트, 임포트 등이 가능하다.

dbms_stats 패키지는 통계정보를 수집하기 위해 다음 프로시저들을 사용한다.

프로시저	설 명
GATHER_INDEX_STATS	인덱스 통계정보 수집
GATHER_TABLE_STATS	테이블, 컬럼, 인덱스 통계정보 수집
GATHER_SCHEMA_STATS	스키마 내의 모든 객체에 대한 통계정보 수집
GATHER_DATABASE_STATS	DB 내의 모든 객체에 대한 통계정보 수집
GATHER_SYSTEM_STATS	시스템에 대한 CPU, I/O 통계정보 수집

SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_system_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP',NULL,50,TRUE,'FOR ALL COLUMNS',2,'DEFAULT',TRUE);

PL/SQL procedure successfully completed.
SQL>

여기서 ('SCOTT','EMP',NULL,50,TRUE,'FOR ALL COLUMNS',2,'DEFAULT',TRUE)가 의미하는 내용은 다음과 같다.

'SCOTT'	통계 정보를 수집할 DB 사용자의 이름
'EMP'	통계 정보를 수집할 테이블
NULL	분할 테이블인지 여부
50	테이블 행의 50% 데이터로 통계정보 수집
TRUE	불록에 대한 통계정보 수집 여부
'FOR ALL COLUMNS'	모든 컬럼에 대한 통계정보 수집
2	통계정보 수집에서 병렬 질의 프로세스 2개 사용
'DEFAULT'	통계정보 수집시 테이블과 분할 정보 이용
TRUE	인덱스가 있으면 함께 통계정보 수집

dbms_stats.gather_table_stats(ownname, tabname, partname, estimate_percent, block_sample, method_opt, degree, granularity, cascade, stattab, statid, statown);

   1. ownname - owner
   2. tabname - table name
   3. partname - partition name
   4. estimate_percent - sample percent ratio
   5. block_sample - consider random blocks sampling rather than rows sampling. TRUE/FALSE
   6. method_opt - method options. FOR ALL COLUMNS/FOR ALL INDEXED COLUMNS. Append the phase SIZE 1 if it is required to generate statistics in parallel.
   7. degree - degree of parallelism.
   8. granularity - for partitioned tables. DEFAULT/SUBPARTITION/PARTITION/GLOBAL/ALL.
   9. cascade - gather statistics for indexes also. TRUE/FALSE
  10. stattab, statid, statown - required for user statistics, covered below in this section.


ANALYZE 명령어

ANALYZE 명령어는 옵티마이저에서 비용기반 방식에 사용할 통계 데이터를 모아 주기 위해 사용되는 튜닝 도구로 테이블과 인덱스에 대한 통계정보를 수집한다.

analyze 명령어가 수행될 때마다 수집되는 정보는 다음과 같다.

종류	수집 정보
TABLE	테이블의 행 수, 사용된 블록 수, 한번도 사용되지 않은 불럭 수, 사용 가능한 공간의 평균, 변경된 행 수, 컬럼당 distinct value의 수, 컬럼당 두 번째로 가장 작은 값, 컬럼당 두 번째로 가장 큰 값
INDEX	인덱스 레벨, 레벨 불럭 수, distinct key 수, 키당 leaf 블럭 수의 평균, 키당 데이터 블럭 수의 평균, clustering factor, 최소 키 값, 최대 키 값

저장된 통계자료는 데이터 사전 뷰를 사용하여 확인할 수 있다.

- USER_INDEXES,	ALL_INDEXES,	DBA_INDEXES
- USER_TABLES,	ALL_TABLES,	DBA_TABLES
- USER_TAB_COLUMNS,	ALL_TAB_COLUMNS,	DBA_TAB_COLUMNS

예를 들어, 테이블에 대한 통계 정보를 수집하기 위해서는 다음과 같이 입력하면 된다.

        ANALYZE TABLE 테이블명 COMPUTE STATISTICS;

        ANALYZE TABLE 테이블명 ESTIMATE STATISTICS;

ANALYZE 명령어는 통계자료뿐만 아니라 DB 객체에 대한 유효성을 검사하는 기능을 제공한다. 이 기능을 사용하기 위해서는 VALIDATE STRUCTURE 옵션을 사용하고 테이블, 클러스터, 인덱스의 이상을 알 수 있다

        ANALYZE TABLE 테이블명 VALIDATE STRUCTURE [CASCADE];

검사하려는 테이블과 관련된 모든 테이블을 검사하려면 CASCADE 옵션을 사용한다.

【예제】

SQL> analyze table emp validate structure cascade;

Table analyzed.

SQL> analyze table emp compute statistics;

Table analyzed.

SQL> analyze table emp estimate statistics;

Table analyzed.