1.1.1 테이블,인덱스포함 통계정보 갱신[편집]

EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name', method_opt => 'for all indexed columns',cascade => true);

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ROWNER',
                                  TABNAME => 'TB_XXXX',
                                  CASCADE => TRUE,
                         ESTIMATE_PERCENT => NULL, 
                               METHOD_OPT =>'FOR ALL COLUMNS SIZE 120');
END;

1.1.2 테이블 DBMS_STATS.GATHER_TABLE_STATS[편집]

                 [, column|attribute [ size clause ]…]
Size_clause := SIZE { integer | REPEAT | AUTO | SKEWONLY }

   모든 컬럼에 대해서 Histogram bucket 의 수를 1로 한다.
   즉, 컬럼 내에 존재하는 여러 가지 값들의 cardinality 는 모두 동일한
    값으로 간주한다. ( histogram 을 사용하지 않는다.)
    이것은 where 조건에 들어오는 특정 컬럼에 대한 값의 변화에 따라서
     PLAN 이 변경될 가능성을 없애고자 함이다.

즉, 통계 정보 수집에 의해 통계 정보가 변경된 후 약 5시간에 걸쳐 랜덤한 시점에  해당 Cursor가 실행될 때 invalidation이 발생한다.

• 예시 : EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'스키마명', tabname=>'테이블명', DEGREE => 8);

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname=>'CYKIM'
  , stattab=>'TB_MGR_STATS'
  , tabname=>'TB_DICT'
  , method_opt=>'FOR ALL INDEXED COLUMNS SIZE 74'
  , cascade=>TRUE);

보다자세히

exec DBMS_STATS.GATHER_TABLE_STATS (
  'CYKIM'
,'TB_DICT5'
 , estimate_percent => 100
, method_opt => 'FOR ALL COLUMNS SIZE 1'
 , degree=> 10
, granularity => 'ALL'
, cascade => TRUE
, no_invalidate => FALSE);

1.1.3 파티션테이블[편집]

exec DBMS_STATS.GATHER_TABLE_STATS (
  'CYKIM'
, 'TB_DICT5'
, partname => 'P201102'   -- 파티션명
, estimate_percent => 5
, method_opt => 'FOR ALL COLUMNS SIZE 1'
, degree=> 10
, granularity => 'ALL'
, cascade => TRUE
, no_invalidate => FALSE);

1.1.4 스키마 DBMS_STATS.GATHER_SCHEMA_STATS[편집]

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
      ownname => 'CYKIM'
    , method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
    , cascade=>TRUE);

1.1.5 인덱스 DBMS_STATS.GATHER_INDEX_STATS[편집]

EXEC DBMS_STATS.GATHER_INDEX_STATS(
'CYKIM'
,'IX_DICT_01');

1.1.6 파티션인덱스[편집]

EXEC DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'CYKIM'
, indname => 'IX_DICT_01'
, partname => 'P20110222');

1.1.7 데이터베이스 DBMS_STATS.GATHER_DATABASE_ STATS[편집]

DBMS_STATS.GATHER_DATABASE_ STATS

1.1.8 시스템통계정보 DBMS_STATS.GATHER_SYSTEM_STATS[편집]

DBMS_STATS.GATHER_SYSTEM_STATS

1.1.9 보다 상세한 통계정보[편집]

DBMS_STATS.GENERATE_STATS

1.2.1 통계정보를 임시로 저장할 Table생성.[편집]

EXEC DBMS_STATS.CREATE_STAT_TABLE (
           '스키마명'
          ,'TB_MGR_STATS'
          ,'테이블스페이스명-생략가능'
          );

1.2.2 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.[편집]

EXEC DBMS_STATS.EXPORT_TABLE_STATS(
  'CYKIM'         -- 스키마명 
, 'TB_DICT'     -- 테이블명 
, NULL            -- 파티션명 
, 'TB_MGR_STATS'   -- 통계테이블명 
, 'TB_DICT_20161101' -- statid  옵션 식별자(버전)
, TRUE            -- cascade 인덱스포함하여
, 'CYKIM'         -- 소유자명 
) ;

EXEC DBMS_STATS.EXPORT_SCHEMA_STATS (
  'CYKIM' -- schema name
 ,'TB_MGR_STATS' -- stat table
 ,'CYKIM_SCHEMA_20160101' -- stat_id(추후 복구용으로 사용할 ID)
 ,'CYKIM' -- stat table owner
);

1.2.3 통계정보 백업 테이블 통계 수집[편집]

EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE);

1.2.4 임시 table을 export, 그리고 target db로 import.[편집]

-- 만약 동일 DB라면 이 과정은 생략 가능합니다.
$exp scott/tiger tables=STATS file=expstat.dmp
$imp scott/tiger file=expstat.dmp full=y log=implog.txt

1.2.5 임시 Table로 부터 통계치를 원하는 table에 넣는다.[편집]

EXEC DBMS_STATS.IMPORT_TABLE_STATS(
          'CYKIM'     -- 스키마명
        , 'TB_DICT'    -- 테이블명
        , NULL         -- 파티션명 
        , 'TB_MGR_STATS'   -- 통계테이블
           );

EXEC DBMS_STATS.IMPORT_SCHEMA_STATS (
  'CYKIM' -- schema name
, 'TB_MGR_STATS' -- stat table
, 'CYKIM_TB_MGR_STATS' -- stat_id
, 'CYKIM' -- stat table owner
);

1.3 통계정보 테이블 삭제 시[편집]

EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');

1.4 ORA-38029: object statistics are locked 에러 발생시[편집]

exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER','TABLE명');

1.5 테이블/인덱스 통계정보조회[편집]

SELECT TABLE_NAME, NUM_ROWS, CHAIN_CNT
     , BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN 
  FROM DBA_TABLES 
 WHERE OWNER = '스키마명';

SELECT TABLE_NAME, INDEX_NAME, STATUS, NUM_ROWS, LEAF_BLOCKS, BLEVEL 
  FROM DBA_INDEXES 
 WHERE OWNER = '스키마명';

1.6 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신[편집]

ANALYZE TABLE 스키마명.테이블명 COMPUTE STATISTICS;

SELECT 'ANALYZE TABLE 스키마명.' || TABLE_NAME || ' COMPUTE STATISTICS;' 
  FROM DBA_TABLES 
 WHERE OWNER = '스키마명';

ANALYZE INDEX 스키마명.인덱스명 VALIDATE STRUCTURE;

SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS 
  FROM INDEX_STATS;

SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;' 
  FROM DBA_INDEXES WHERE OWNER = '스키마명';