"오라클 통계정보 이동"의 두 판 사이의 차이
DB CAFE
(→통계정보 EXPORT) |
(→통계정보 추출 후 입력 시나리오) |
||
125번째 줄: | 125번째 줄: | ||
== 통계정보 추출 후 입력 시나리오 == | == 통계정보 추출 후 입력 시나리오 == | ||
− | |||
− | === 통계정보 | + | = 테이블/인덱스 통계정보를 옮기는 방법. = |
− | + | * DBMS_STATS.GATHER_TABLE_STATS 패키지 이용 | |
− | + | {{틀:고지 상자 | |
+ | |내용=:1) 통계정보를 임시로 저장할 Table생성. | ||
+ | :2) 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관. | ||
+ | :3) 통계정보 백업 테이블 통계 수집 | ||
+ | :4) 임시 table을 export, 그리고 target db로 import. | ||
+ | :5) 임시 Table로 부터 통계치를 대상 테이블에 입력. | ||
+ | :6) 통계정보 테이블 삭제 | ||
+ | }} | ||
− | ==== | + | |
+ | |||
+ | 보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, | ||
+ | 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우. | ||
+ | |||
+ | * 통계정보 추출 | ||
+ | <source lang=sql> | ||
+ | -- 테이블 별 추출 일때 | ||
+ | DBMS_STATS.EXPORT_TABLE_STATS( | ||
+ | ownname VARCHAR2, -- schema name | ||
+ | tabname VARCHAR2, -- table name | ||
+ | partname VARCHAR2 DEFAULT NULL, -- partition name | ||
+ | stattab VARCHAR2, -- stat table name | ||
+ | statid VARCHAR2 DEFAULT NULL, -- optional identifier | ||
+ | cascade BOOLEAN DEFAULT TRUE, -- TRUE = indexes too | ||
+ | statown VARCHAR2 DEFAULT NULL); -- stat table schema | ||
+ | |||
+ | 예시) | ||
+ | EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'MIG_ADM',TABNAME=>'EMP' ,STATTAB=>'MIG_TAB_STATS_BAK' ,STATOWN=>'MIG_ADM', STATID=>'MIG_20181118', CASCADE=>TRUE) ; | ||
+ | |||
+ | -- 스키마 별 추출 일때 | ||
+ | 예시) STATS 은 통계 추출 한 임시테이블 | ||
+ | EXEC DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname =>'XX_DBA' , stattab => 'STATS', statid => '12C_20190514', statown =>'DBA_cykim'); | ||
+ | </source> | ||
+ | |||
+ | * 통계정보 입력 | ||
<source lang=sql> | <source lang=sql> | ||
− | + | DBMS_STATS.IMPORT_TABLE_STATS( | |
− | + | ownname VARCHAR2, | |
− | + | tabname VARCHAR2, | |
− | + | partname VARCHAR2 DEFAULT NULL, | |
− | + | stattab VARCHAR2, | |
− | </source> | + | statid VARCHAR2 DEFAULT NULL, |
+ | cascade BOOLEAN DEFAULT TRUE, | ||
+ | statown VARCHAR2 DEFAULT NULL, | ||
+ | no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')), | ||
+ | force BOOLEAN DEFAULT FALSE); | ||
+ | |||
+ | 예시) | ||
+ | EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'MIG_ADM',TABNAME=>'EMP' ,STATTAB=>'MIG_TAB_STATS_BAK' ,STATOWN=>'MIG_ADM', STATID=>'MIG_20181118', CASCADE=>TRUE) ; | ||
+ | |||
+ | </source> | ||
− | + | *예 : SCOTT의 EMP 통계를 SCOTT2의 EMP2로 이관 | |
− | ==== | + | == 통계정보를 임시로 저장할 Table생성. == |
− | |||
<source lang=sql> | <source lang=sql> | ||
− | + | exec dbms_stats.create_stat_table('SCOTT','STATS'); | |
− | |||
− | , ' | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | ) ; | ||
</source> | </source> | ||
− | == | + | == 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관. == |
− | |||
<source lang=sql> | <source lang=sql> | ||
− | EXEC DBMS_STATS.EXPORT_SCHEMA_STATS ( | + | -- 테이블 |
− | ' | + | exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE); |
− | + | -- 스키마 | |
− | + | EXEC DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname =>'XX_DBA' , stattab => 'STATS', statid => 'XX_20190514', statown =>'XX_DBA'); | |
− | |||
− | ); | ||
</source> | </source> | ||
+ | * 참고 (updated: 2010/08/03) | ||
+ | "emp stats"로 중간에 space가 들어가면 에러발생함 | ||
+ | |||
+ | <source lang=sql> | ||
+ | exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP_STATS',TRUE,'SCOTT'); | ||
+ | BEGIN dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP STATS',TRUE,'SCOTT'); END; | ||
+ | * | ||
+ | ERROR at line 1: | ||
+ | ORA-20001: EMP STATS is an invalid identifier | ||
+ | ORA-06512: at "SYS.DBMS_STATS", line 8240 | ||
+ | ORA-06512: at "SYS.DBMS_STATS", line 9188 | ||
+ | ORA-06512: at line 1 | ||
− | === | + | * 주의 (updated: 2010/08/03) |
+ | STATS table의 데이터를 쿼리 해보면 EMP table의 원래 소유자인 SCOTT이 C5 column에 명시되어 있음. | ||
+ | 만약 다른 계정으로, 즉 SCOTT2 계정으로 통계정보를 옮기려면 STATS table의 SCOTT값을 SCOTT2로 update 해주어야 함. | ||
+ | 두개의 DB의 계정이 동일하다면 변경해 줄 필요 없음 | ||
+ | </source> | ||
+ | |||
+ | == 통계정보 백업 테이블 통계 수집 == | ||
+ | <source lang=sql> | ||
+ | EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE); | ||
+ | </source> | ||
+ | == 임시 table을 export, 그리고 target db로 import. == | ||
− | |||
− | |||
<source lang=sql> | <source lang=sql> | ||
− | + | $exp scott/tiger tables=STATS file=expstat.dmp | |
− | + | $imp scott/tiger file=expstat.dmp full=y log=implog.txt | |
− | + | (만약 동일 DB라면 이 과정은 생략 가능합니다.) | |
− | + | </source> | |
− | |||
− | |||
+ | == 임시 Table로 부터 통계치를 원하는 table에 넣는다. == | ||
+ | <source lang=sql> | ||
+ | EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'MIG_ADM',TABNAME=>'EMP' ,STATTAB=>'MIG_TAB_STATS_BAK' ,STATOWN=>'MIG_ADM', STATID=>'MIG_20181118', CASCADE=>TRUE) ; | ||
</source> | </source> | ||
− | == | + | |
+ | == 통계정보 테이블 삭제 == | ||
<source lang=sql> | <source lang=sql> | ||
− | EXEC DBMS_STATS. | + | EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB'); |
− | + | </source> | |
− | , ' | ||
− | |||
− | |||
− | ); | ||
− | </source> | ||
− | == | + | == ORA-38029: object statistics are locked 에러 발생시 == |
<source lang=sql> | <source lang=sql> | ||
− | + | exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER','TABLE명'); | |
− | |||
− | , ' | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | ); | ||
</source> | </source> | ||
+ | [[category:oracle]] | ||
== 해당 스키마에 해당하는 테이블의 통계정보 조회 == | == 해당 스키마에 해당하는 테이블의 통계정보 조회 == |
2021년 6월 4일 (금) 11:47 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 통계정보 수집/추출/입력
- 2 테이블/인덱스 통계정보를 옮기는 방법.
- 2.1 통계정보를 임시로 저장할 Table생성.
- 2.2 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.
- 2.3 통계정보 백업 테이블 통계 수집
- 2.4 임시 table을 export, 그리고 target db로 import.
- 2.5 임시 Table로 부터 통계치를 원하는 table에 넣는다.
- 2.6 통계정보 테이블 삭제
- 2.7 ORA-38029: object statistics are locked 에러 발생시
- 2.8 해당 스키마에 해당하는 테이블의 통계정보 조회
- 2.9 해당 스키마에 해당하는 인덱스의 통계정보 조회
- 2.10 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신
- 2.11 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 생성
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의 값 예시
- for all column size 1: 모든 컬럼에 대해 Histogram을 수집하지 않는다.
- for all column size auto: Oracle이 Data의 분포를 고려하여 Bucket Size를 계산.(단, 해당 Coloumn이 Predicate로 사용된 적이 있는 경우에만 Histogram 수집)
- for all column size skewonly: Oracle이 Data의 분포를 고려하여 Bucket Size를 계산.(단, 해당 Coloumn이 Predicate로 사용된 적이 없어도 Histogram 수집)
- for all indexed column size skewonly: Index가 존재하는 모든 컬럼
- for all indexed column size auto: Index가 존재하는 모든 컬럼
- for columns c1 size 5 c2 size skewonly: 개별 컬럼
- for all hidden columns size skewonly: Function Based Index에 의해 생성된 Hidden Cloumn
- 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 통계정보 추출 후 입력 시나리오[편집]
2 테이블/인덱스 통계정보를 옮기는 방법.[편집]
- DBMS_STATS.GATHER_TABLE_STATS 패키지 이용
android :1) 통계정보를 임시로 저장할 Table생성.
- 2) 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.
- 3) 통계정보 백업 테이블 통계 수집
- 4) 임시 table을 export, 그리고 target db로 import.
- 5) 임시 Table로 부터 통계치를 대상 테이블에 입력.
- 6) 통계정보 테이블 삭제
보통, Test 시스템에서 가동 시스템으로 데이타를 이관하는 경우, 데이타 뿐만 아니라 통계정보도 이관하여, Test계와 가동계를 동일하게 유지하고자 하는 경우.
- 통계정보 추출
-- 테이블 별 추출 일때
DBMS_STATS.EXPORT_TABLE_STATS(
ownname VARCHAR2, -- schema name
tabname VARCHAR2, -- table name
partname VARCHAR2 DEFAULT NULL, -- partition name
stattab VARCHAR2, -- stat table name
statid VARCHAR2 DEFAULT NULL, -- optional identifier
cascade BOOLEAN DEFAULT TRUE, -- TRUE = indexes too
statown VARCHAR2 DEFAULT NULL); -- stat table schema
예시)
EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'MIG_ADM',TABNAME=>'EMP' ,STATTAB=>'MIG_TAB_STATS_BAK' ,STATOWN=>'MIG_ADM', STATID=>'MIG_20181118', CASCADE=>TRUE) ;
-- 스키마 별 추출 일때
예시) STATS 은 통계 추출 한 임시테이블
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname =>'XX_DBA' , stattab => 'STATS', statid => '12C_20190514', statown =>'DBA_cykim');
- 통계정보 입력
DBMS_STATS.IMPORT_TABLE_STATS(
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
cascade BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
예시)
EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'MIG_ADM',TABNAME=>'EMP' ,STATTAB=>'MIG_TAB_STATS_BAK' ,STATOWN=>'MIG_ADM', STATID=>'MIG_20181118', CASCADE=>TRUE) ;
- 예 : SCOTT의 EMP 통계를 SCOTT2의 EMP2로 이관
2.1 통계정보를 임시로 저장할 Table생성.[편집]
exec dbms_stats.create_stat_table('SCOTT','STATS');
2.2 통계정보 이관할 스키마/테이블 의 통계정보를 임시 table로 이관.[편집]
-- 테이블
exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE);
-- 스키마
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname =>'XX_DBA' , stattab => 'STATS', statid => 'XX_20190514', statown =>'XX_DBA');
- 참고 (updated: 2010/08/03)
"emp stats"로 중간에 space가 들어가면 에러발생함
exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP_STATS',TRUE,'SCOTT');
BEGIN dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','EMP STATS',TRUE,'SCOTT'); END;
*
ERROR at line 1:
ORA-20001: EMP STATS is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 8240
ORA-06512: at "SYS.DBMS_STATS", line 9188
ORA-06512: at line 1
* 주의 (updated: 2010/08/03)
STATS table의 데이터를 쿼리 해보면 EMP table의 원래 소유자인 SCOTT이 C5 column에 명시되어 있음.
만약 다른 계정으로, 즉 SCOTT2 계정으로 통계정보를 옮기려면 STATS table의 SCOTT값을 SCOTT2로 update 해주어야 함.
두개의 DB의 계정이 동일하다면 변경해 줄 필요 없음
2.3 통계정보 백업 테이블 통계 수집[편집]
EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE);
2.4 임시 table을 export, 그리고 target db로 import.[편집]
$exp scott/tiger tables=STATS file=expstat.dmp
$imp scott/tiger file=expstat.dmp full=y log=implog.txt
(만약 동일 DB라면 이 과정은 생략 가능합니다.)
2.5 임시 Table로 부터 통계치를 원하는 table에 넣는다.[편집]
EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'MIG_ADM',TABNAME=>'EMP' ,STATTAB=>'MIG_TAB_STATS_BAK' ,STATOWN=>'MIG_ADM', STATID=>'MIG_20181118', CASCADE=>TRUE) ;
2.6 통계정보 테이블 삭제[편집]
EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');
2.7 ORA-38029: object statistics are locked 에러 발생시[편집]
exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER','TABLE명');
2.8 해당 스키마에 해당하는 테이블의 통계정보 조회[편집]
SELECT TABLE_NAME, NUM_ROWS, CHAIN_CNT
, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN
FROM DBA_TABLES
WHERE OWNER = '스키마명';
2.9 해당 스키마에 해당하는 인덱스의 통계정보 조회[편집]
SELECT TABLE_NAME, INDEX_NAME, STATUS, NUM_ROWS, LEAF_BLOCKS, BLEVEL
FROM DBA_INDEXES
WHERE OWNER = '스키마명';
2.10 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신[편집]
ANALYZE TABLE 스키마명.테이블명 COMPUTE STATISTICS;
2.10.1 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신(쿼리 생성)[편집]
SELECT 'ANALYZE TABLE 스키마명.' || TABLE_NAME || ' COMPUTE STATISTICS;'
FROM DBA_TABLES
WHERE OWNER = '스키마명';
2.10.2 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신[편집]
ANALYZE INDEX 스키마명.인덱스명 VALIDATE STRUCTURE;
SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS
FROM INDEX_STATS;
2.11 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 생성[편집]
SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;'
FROM DBA_INDEXES WHERE OWNER = '스키마명';