행위

"오라클 자동 통계정보 스케줄"의 두 판 사이의 차이

DB CAFE
(→‎Oracle Table의 통계정보를 옮기는 방법.)
86번째 줄: 86번째 줄:
 
*예 : SCOTT의 EMP  통계를 SCOTT2의 EMP2로 이관
 
*예 : SCOTT의 EMP  통계를 SCOTT2의 EMP2로 이관
  
−
== 1. 통계정보를 임시로 저장할 Table생성. ==
+
== 1) 통계정보를 임시로 저장할 Table생성. ==
 
<source lang=sql>
 
<source lang=sql>
 
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');
 
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');
 
</source>
 
</source>
  
−
== 2. 원하는 table의 통계정보를 임시 table로 이관. ==
+
== 2) 원하는 table의 통계정보를 임시 table로 이관. ==
 
<source lang=sql>
 
<source lang=sql>
 
SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE);
 
SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE);
114번째 줄: 114번째 줄:
 
</source>
 
</source>
  
−
== 3. 통계정보 백업 테이블 통계 수집 ==
+
== 3) 통계정보 백업 테이블 통계 수집 ==
 
<source lang=sql>
 
<source lang=sql>
 
EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE);
 
EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG_ADM','MIG_TAB_STATS_BAK',DEGREE=>4,GRANULARITY=>'ALL',CASCADE=>TRUE);
 
</source>
 
</source>
  
−
== 4. 임시 table을 export, 그리고 target db로 import. ==
+
== 4) 임시 table을 export, 그리고 target db로 import. ==
  
 
<source lang=sql>
 
<source lang=sql>
127번째 줄: 127번째 줄:
 
</source>
 
</source>
  
−
== 5. 임시 Table로 부터 통계치를 원하는 table에 넣는다. ==
+
== 5) 임시 Table로 부터 통계치를 원하는 table에 넣는다. ==
 
<source lang=sql>
 
<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) ;
 
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>
  
−
== 6. 통계정보 테이블 삭제 ==
+
== 6) 통계정보 테이블 삭제 ==
 
<source lang=sql>
 
<source lang=sql>
 
SQL> EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');
 
SQL> EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');
 
</source>
 
</source>

2019년 4월 24일 (수) 11:07 판

thumb_up 추천메뉴 바로가기

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('스키마명', '테이블명');

1.4 스키마안의 모든 세그먼트에 대한 통계정보 갱신[편집]

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('스키마명');

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

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

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

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

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

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

SELECT NAME, BLOCKS, LF_ROWS, DEL_LF_ROWS 

 FROM INDEX_STATS;

</source>​

1.8 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) ;
  • 통계정보 입력
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생성.[편집]

SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');

2.2 2) 원하는 table의 통계정보를 임시 table로 이관.[편집]

SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE);
  • 참고 (updated: 2010/08/03)

"emp stats"로 중간에 space가 들어가면 에러발생함 

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의 계정이 동일하다면 변경해 줄 필요 없음

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) 통계정보 테이블 삭제[편집]

SQL> EXEC DBMS_STATS.DROP_STAT_TABLE('SCOTT2','STAT_TAB');
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_자동_통계정보_스케줄&oldid=1113"