통계정보 생성

/*사이즈 확인*/
SELECT SEGMENT_NAME,
             SUM(BYTES)/1024/1024    SSIZE_MB
    FROM USER_SEGMENTS
  WHERE SEGMENT_TYPE LIKE 'TABLE%'
 GROUP BY SEGMENT_NAME ;
 
 
/*통계정보 DDL생성 */
select 'BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => '''||OWNER||''''||', tabname =>'||''''||table_name||''''||
       ', method_opt => ''for all columns size 1'''||','||
       CASE  when US.SSIZE_MB > 10000000 then ' estimate_percent =>0.0001, '
           when US.SSIZE_MB > 1000000 then ' estimate_percent =>0.001, '
           when US.SSIZE_MB > 100000 then ' estimate_percent =>0.01, '
            when US.SSIZE_MB > 10000 then ' estimate_percent =>0.1, '
            when US.SSIZE_MB >  5000 then ' estimate_percent =>1, '
            when US.SSIZE_MB >  1000 then ' estimate_percent =>5, '
            when US.SSIZE_MB >   500 then ' estimate_percent =>10, '
            when US.SSIZE_MB >   100 then ' estimate_percent =>30, '
            when US.SSIZE_MB >     0 then ' estimate_percent =>100, '
            else ''
       end||'granularity => ''all'', degree => ''8'', cascade=> TRUE ); END;'    --물리 4코어 논리 8코어
       ||'  --> '||US.SSIZE_MB||'MB,'||' PARTITIONED: '||PARTITIONED
  from DBA_tables t,
       (SELECT SEGMENT_NAME,
               SUM(BYTES)/1024/1024 SSIZE_MB
          FROM DBA_SEGMENTS
         WHERE SEGMENT_TYPE LIKE 'TABLE%' AND OWNER ='FTDATA' AND SEGMENT_NAME ='FTB_TRADE'
         GROUP BY SEGMENT_NAME) US
 Where t.TABLE_NAME = US.SEGMENT_NAME
 AND T.OWNER ='FTDATA'
 ORDER BY US.SSIZE_MB DESC,
          t.table_name;

 

통계정보 생성은  6개월~1년 정도(또는 1~2년) 주기로 해주는게 좋음

하지만,  대용량의 테이블일 경우, 통계 정보 생성에 시간이 많이 들기 때문에 시스템 정기 점검 시간을 고려해서 하는 것이 필요함.

데이터가 큰 변화없이 일정하게 증가하는 시스템이라면 통계정보 생성 주기를 더 늦추어도 상관없음

단, 새로운 파티션 추가 or 새로운 index / table이 추가되면 통계 정보 생성을 해주어야 함

(신규 index나 table은 데이터 입력 후 통계 정보 신규 작업을 해야 함)

 

대용량 테이블의 경우 통계정보 생성시 estimate statistics의 비율을 작게 설정해서 시간을 단축할 수 있음.

100G 이상의 대용량 테이블이면 1%로만 통계정보를 만들어도 충분함.

 

 

1) 통계정보 생성하는 DBMS_STATS 패키지

 

일반적으로 CREATE_STAT_TABLE / DROP_STAT_TABLE을 사용하여 딕셔너리가 아닌, 사용자 스키마 통계정보를 저장할 수 있는 통계 테이블을 생성한다.

그 이유는, 딕셔너리는 기존의 통계정보를 삭제 후 입력하는 반면에, 유저의 통계테이블은 추가적으로 입력되기 때문에 기존의 통계정보를 보존 할 수가 있기 때문이다.

 

--통계 테이블 생성

DBMS_STAT.CREATE_STAT_TABLE (

ownername VARCHAR2, --스키마

stattab        VARCHAR2, --테이블명

tblspace     VARCHAR2 DEFAULT NULL ); --통계테이블이 생성될 테이블 스페이스

 

--통계 테이블 삭제

DBMS_STAT.DROP_STAT_TABLE (

ownername VARCHAR2,

stattab        VARCHAR2 );

 

 

 

2) 통계정보 생성

 

GATHER_INDEX_STATS : 인덱스 통계정보 생성

GATHER_TABLE_STATS : 테이블, 테이블 내 컬럼 및 인덱스 통계정보 생성

GATHER_SCHEMA_STATS : 해당 스키마 내 모든 객체들의 통계정보 생성

GATHER_DATABASE_STATS : DB내 모든 객체들의 통계정보 생성

GATHER_SYSTEM_STATS : 시스템 통계정보 생성

 

# 파라미터#

ownname : 스키마명

indname : 인덱스명

partname : 파티션명

tabname : 테이블명

interval : 지정된 분 동안의 DB 실시간 SGA 사용량에 의거한 통계정보 생성

estimate_percent : 통계될 ROW의 퍼센트 (100 ~ 0.000001)

method_opt : FOR ALL [INDEX | HIDEN] COLUMNS [size_clause]

또는 FOR COLUMNS [size_clause] column | attribute[size_clause] [column | attribute [size_clause]]

degree : 병렬처리 개수 설정

granularity : 해당 객체가 파티션 화 되어 있을시에만 사용

 ALL - global, partiton, subpartition 레벨의 통계정보

 DEFAULT - global, 및 partition 레벨의 통계정보

cascade : 테이블 대상시에 해당 테이블의 모든 인덱스에 대해서도 통계정보 생성

이 때 인덱스 통계정보는 병렬사용 불가능

 

예)

BEGIN

dbms_stats.gather_table_stats( ownname => 'SCOTT',

    tabname => 'EMP',

    estimate_percent => '10',

    method_opt => 'FOR ALL INDEXED columns',

    degree => 8,

    granularity => ALL,

    cascade => true );

END

 

 

 

3) 통계정보 이동

 

필요에 따라 IMPORT를 사용하여 유저의 통계테이블에서 원하는 레벨의 통계정보를 딕셔너리에 로딩시켜 현재 메모리 내의

기존 통계정보를 삭제한 후, 새로운 통계정보를 옵티마이저에 적용되도록 하거나, EXPORT를 사용하여 현재 메모리 내의

딕셔너리의 통계정보를 특정 테이블에 복사 할수도 있다.

 

--각 옵션에 맞는 해당 통계정보를 추출하여 딕셔너리에 저장

IMPORT_COLUMN_STATS

IMPORT_INDEX_STATS

IMPORT_TABLE_STATS

IMPORT_SCHEMA_STATS

IMPORT_DATABASE_STATS

IMPORT_SYSTEM_STATS

 

--각 옵션에 맞는 해당 통계정보를 추출하여 stattab에 저장

EXPORT_COLUMN_STATS

EXPORT_INDEX_STATS

EXPORT_TABLE_STATS

EXPORT_SCHEMA_STATS

EXPORT_DATABASE_STATS

EXPORT_SYSTEM_STATS