"오라클 자동 통계정보 스케줄"의 두 판 사이의 차이
DB CAFE
14번째 줄: | 14번째 줄: | ||
WHERE OWNER = '스키마명'; | WHERE OWNER = '스키마명'; | ||
</source> | </source> | ||
+ | |||
== 테이블의 통계정보 갱신 == | == 테이블의 통계정보 갱신 == | ||
<source lang=sql> | <source lang=sql> | ||
19번째 줄: | 20번째 줄: | ||
</source> | </source> | ||
+ | == 테이블,인덱스포함 통계정보 갱신 == | ||
+ | <source lang=sql> | ||
+ | EXEC DBMS_STATS.GATHER_TABLE_STATS(schema_name, ‘table_name’, method_opt => ‘for all indexed columns’,cascade => true); | ||
+ | </source> | ||
<source lang=sql> | <source lang=sql> | ||
BEGIN | BEGIN |
2019년 8월 13일 (화) 09:18 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 Oracle 통계정보 갱신
- 1.1 해당 스키마에 해당하는 테이블의 통계정보 조회
- 1.2 해당 스키마에 해당하는 인덱스의 통계정보 조회
- 1.3 테이블의 통계정보 갱신
- 1.4 테이블,인덱스포함 통계정보 갱신
- 1.5 스키마안의 모든 세그먼트에 대한 통계정보 갱신
- 1.6 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신
- 1.7 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신(쿼리 생성)
- 1.8 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신
- 1.9 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신(쿼리 생성)
- 2 Oracle Table의 통계정보를 옮기는 방법.
1 Oracle 통계정보 갱신[편집]
1.1 해당 스키마에 해당하는 테이블의 통계정보 조회[편집]
SELECT TABLE_NAME, NUM_ROWS, CHAIN_CNT
, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN
FROM DBA_TABLES
WHERE OWNER = '스키마명';
1.2 해당 스키마에 해당하는 인덱스의 통계정보 조회[편집]
SELECT TABLE_NAME, INDEX_NAME, STATUS, NUM_ROWS, LEAF_BLOCKS, BLEVEL
FROM DBA_INDEXES
WHERE OWNER = '스키마명';
1.3 테이블의 통계정보 갱신[편집]
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'스키마명', tabname=>'테이블명', DEGREE => 8);
1.4 테이블,인덱스포함 통계정보 갱신[편집]
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.5 스키마안의 모든 세그먼트에 대한 통계정보 갱신[편집]
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('스키마명');
1.6 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신[편집]
ANALYZE TABLE 스키마명.테이블명 COMPUTE STATISTICS;
1.7 DBMS_STATS 패키지로 갱신되지 않는 테이블 통계 정보 갱신(쿼리 생성)[편집]
SELECT 'ANALYZE TABLE 스키마명.' || TABLE_NAME || ' COMPUTE STATISTICS;'
FROM DBA_TABLES
WHERE OWNER = '스키마명';
1.8 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신[편집]
ANALYZE INDEX 스키마명.인덱스명 VALIDATE STRUCTURE;
SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS
FROM INDEX_STATS;
</source>
1.9 DBMS_STATS 패키지로 갱신되지 않는 인덱스 통계정보 갱신(쿼리 생성)[편집]
SELECT 'ANALYZE INDEX 스키마명.' || INDEX_NAME || ' VALIDATE STRUCTURE;'
FROM DBA_INDEXES WHERE OWNER = '스키마명';
2 Oracle Table의 통계정보를 옮기는 방법.[편집]
보통, 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 1) 통계정보를 임시로 저장할 Table생성.[편집]
exec dbms_stats.create_stat_table('SCOTT','STATS');
2.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 3) 통계정보 백업 테이블 통계 수집[편집]
EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE);
2.4 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 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 6) 통계정보 테이블 삭제[편집]
EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');