행위

"오라클 통계정보 이동"의 두 판 사이의 차이

DB CAFE

(→‎DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신(쿼리 생성))
(→‎통계정보 EXPORT/IMPORT)
124번째 줄: 124번째 줄:
 
  </source>
 
  </source>
  
−
== 통계정보 EXPORT/IMPORT ==
+
== 통계정보 추출 후 입력 시나리오  ==
 +
* 통계정보 EXPORT/IMPORT
  
 
=== 통계정보 EXPORT ===
 
=== 통계정보 EXPORT ===

2021년 6월 4일 (금) 10:43 판

thumb_up 추천메뉴 바로가기

1 통계정보 수집/추출/입력[편집]

1.1 통계정보수집[편집]

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;

android method_opt의 값 예시

  1. for all column size 1: 모든 컬럼에 대해 Histogram을 수집하지 않는다.
  2. for all column size auto: Oracle이 Data의 분포를 고려하여 Bucket Size를 계산.(단, 해당 Coloumn이 Predicate로 사용된 적이 있는 경우에만 Histogram 수집)
  3. for all column size skewonly: Oracle이 Data의 분포를 고려하여 Bucket Size를 계산.(단, 해당 Coloumn이 Predicate로 사용된 적이 없어도 Histogram 수집)
  4. for all indexed column size skewonly: Index가 존재하는 모든 컬럼
  5. for all indexed column size auto: Index가 존재하는 모든 컬럼
  6. for columns c1 size 5 c2 size skewonly: 개별 컬럼
  7. for all hidden columns size skewonly: Function Based Index에 의해 생성된 Hidden Cloumn
  8. for columns SYS_NC00003$ size skewonly: Hidden Column 이름을 직접명시


android 모든 컬럼, 모든 Index대상 컬럼, 개별컬럼, Hidden컬럼들에 대해서, Bucket Size를 정할 수 있는 여러가지 옵션(skewonly, auto, 숫자)을 주어 히스토그램을 생성할 수 있다.


1.1.2 테이블[편집]

android 파라미터

  • Ownname => 스키마 이름
  • Indname => 인덱스 이름
  • Partname => 파티션 이름
  • Tabname => 테이블 이름
  • Statown => 통계 테이블이 속한 스키마의 이름
  • Stattab => 특정 통계 테이블의 이름
  • Statid => stattab 내의 통계 정보의 ID 설정
  • no_validate => TRUE로 설정할 시 해당 객체를 참조하는 커서의 재 파싱 실행.
  • gather_sys => 'SYS' 유저의 객체에 대해 통계 정보 생성
  • interval => 지정 분 동안의 DB 실시간 SGA 사용량의 의거한 통계 정보 생성. Gathering_mode='INTERVAL' 일 때에만 사용 가능.
  • Degree => 병렬 처리 개수 설정. NULL 시 해당 객체의 생성시에 명시된 DEGREE 값이 적용. DBMS_STATS.DEFAULT_DEGREE 를 사용하여 INIT 파라미터 값 사용 가능.
  • Cascade => 테이블 대상 시에 해당 테이블의 모든 인덱스에 대해서도 통계 정보 생성. 이 때 인덱스 통계 정보는 병렬 사용 불가능.
  • gather_temp => global temporary table에 통계 정보 저장. 해당 테이블은 “on commit ---serve rows” 옵션을 사용해야만 함. 이 테이블에 저장된 통계 정보는 모든 세션들에 대해 공유 가능
  • tblspace => 통계 테이블이 생성될 테이블스페이스의 이름. 지정하지 않을 시에는 실행 유저의 Default 테이블스페이스 사용.


  • 예시 :
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 스키마[편집]

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

1.1.5 인덱스[편집]

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

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

DBMS_STATS.GATHER_SYSTEM_STATS

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

DBMS_STATS.GENERATE_STATS

1.2 통계정보 추출 후 입력 시나리오[편집]

  • 통계정보 EXPORT/IMPORT

1.2.1 통계정보 EXPORT[편집]

1.2.1.1 통계정보를 임시로 저장할 테이블 생성[편집]

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

1.2.1.2 통계추출테이블에 EXPORT[편집]

1.2.1.2.1 테이블 정보 EXPORT[편집]
  • dbms_stats.export_table_stats ('table_owner', 'table name', null, 'stat table','stat_id',true,'stat table owner');
EXEC DBMS_STATS.EXPORT_TABLE_STATS(
  'CYKIM'         -- 스키마명 
, 'TB_DICT'     -- 테이블명 
, NULL            -- 파티션명 
, 'TB_MGR_STATS'   -- 통계테이블명 
, 'TB_DICT_20161101' -- statid  옵션 식별자(버전)
, TRUE            -- cascade 인덱스포함하여
, 'CYKIM'         -- 소유자명 
) ;
1.2.1.2.2 스키마 정보 EXPORT[편집]
  • dbms_stats.export_schema_stats ('schema name', 'stat table','stat_id','stat table owner');
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS (
  'SCOTT' -- schema name
, 'TB_MGR_STATS' -- stat table
, 'STATS_BAK_2020' -- stat_id
, 'CYKIM' -- stat table owner
);

1.2.2 통계정보 IMPORT[편집]

1.2.2.1 테이블 통계정보 입력[편집]

dbms_stats.import_table_stats ('table_owner', 'table name', null, 'stat table','stat_id',true,'stat table owner'); 

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

1.2.2.2 스키마 정보 입력[편집]

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.2.2.3 통계정보 복구[편집]

EXEC DBMS_STATS.IMPORT_TABLE_STATS ( 
  'CYKIM'
, 'TB_DICT'
, NULL
, 'TB_MGR_STATS'
,' TB_MGR_STATS_20161101' -- 통계식별ID
, TRUE
, 'CYKIM'
);

1.3 통계정보 백업[편집]

  • dbms_stats.export_table_stats
  • dbms_stats.export_schema_stats

1.3.1 스키마별 통계 백업[편집]

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


1.4 해당 스키마에 해당하는 테이블의 통계정보 조회[편집]

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

1.5 해당 스키마에 해당하는 인덱스의 통계정보 조회[편집]

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

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

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

1.6.1 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신(쿼리 생성)[편집]

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

1.6.2 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신[편집]

ANALYZE INDEX 스키마명.인덱스명 VALIDATE STRUCTURE;
SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS 
  FROM INDEX_STATS;

​

1.7 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 생성[편집]

SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;' 
  FROM DBA_INDEXES WHERE OWNER = '스키마명';
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_통계정보_이동&oldid=6222"