출처: http://egloos.zum.com/bosoa/v/1402860
다른 Database로의 통계정보 이관하기
성능개선 혹은 Database Migration 작업등 운영계시스템에서 직접 작업하기가 곤란하여 별도 시스템을 구해 데이터이관작업을 수행하여야 하는 경우, 운영계시스템과는 별도의 개발 프로젝트를 진행해야 하고 이를 지원하기 위해 기존 운영계시스템의 SCHEMA를 동일한 환경에서 별도의 장비에 구성해야 하는 경우가 종종 발생한다.
대량의 데이터를 보관하고 있는 운영계 시스템과 동일한 사이즈의 별도의 시스템을 구성하여 개발 및 테스트를 할 수만 있다면 가장 최상의 선택이겠지만, 여러 문제로 인해 도저히 운영계시스템의 H/W SPEC과 동일하게 가져 갈 수 없는 것이 보통 현장에서 겪는 현실이며, 심지어는 데이터는 한 건도 넣지
못하고, 빈 그릇만 가지고 테스트하거나 개발을 해야 하는 경우도 심심치
않게 발생하곤 한다.
한가지 고민을 하게 된 것은 이관할 데이터량은 1/10 수준 혹은 그 이하이며,
데이터의 분포도 또한 운영계시스템과 동일하다 할 수 없는 상황에서도, 통계정보는 그대로 옮길 수 잇는 방법이 없는가 하는 것이다.
오라클을 사용하는 유저중에 많이 사용되지는 않지만 NewFeature안에 포함된
Package 및 utility중에서 위의 고민을 해결할 수 있는 방법이 제시되었는데,실제로Filed는 많이 활용되지 않는 것으로 판단된다.
본 문서에서는 데이터는 한건도 옮기지 않은 상황에서 UNIX 머신의 Schema를
Windows 머신에 옮겨 테스트를 한 것이며, Optimizer Mode는 Cost Base이다.
자세한 테스트 시스템 환경은 다음과 같다.
FROM SERVER
Machine : O/S - HP UNIX
RDBMS Ver : Oracle 9.2.0.1
DBMS USER : SCOTT
TO SERVER
Machine : O/S - Windows2000
RDBMS Ver : Oracle 9.2.0.4
DBMS USER : SCOTT
1. DBMS_STATS Package 를 이용한 통계정보 이관
dbms_stats PL/SQL Package(Oracle 8.1.5 이상부터) 는 CBO환경에서 통계정보를 생성하고 관리할 수 있도록 지원하는 Utility이다. 이것을 통해 수집,수정,조회,export,import및 통계정보의 삭제가 가능해진다. 또한 index와 table, 컬럼,및 partitions 및 모든 schema object에 대하여 이 유틸리티를 사용할 수 있다.
이 유틸리티의 자세한 사용방법은 Oracle Supplied PL/SQL Packages and Types Reference메뉴얼을 참고하기 바라며, 본 장에서는 통계정보의 export및 import를 사용하여 다른 시스템으로의 동일한 통계정보이관을 가능하도록 하는 방법에 대해 예시와 함께 간략하게 설명하고자 한다.
통계정보를 이관하기 위한 STEP을 간단히 설명하면
가. 통계정보를 임시로 담기 위한 임시 테이블 생성
나. 최신의 통계정보를 gather_table_stat package를 이용하여 수집하도록 한다.
exec dbms_stats.gather_table_stats('SCOTT','TEST2',NULL,NULL,
FALSE,'FOR ALL COLUMNS SIZE 74',
NULL,'DEFAULT',TRUE,'STATS',NULL,NULL);
다. 이미 생성되어 있는 Object별 통계정보를 Data Dictionary에서 읽어 step가) 에서 생성한 임시 테이블에 INSERT
라. Oracle의 export/import를 이용하여 임시 Table에 저장된 data(통계정보)를 이관작업 수행
마. 이관된 Database내의 임시테이블에서 FROM database의 통계정보를 dbms_stats.import_table_stats Package를 이용하여 data dictionary로 이관작업을 수행.
가. 통계정보를 임시로 담기 위한 임시 테이블 생성 및 테스트 환경 생성
SQL> create table test2
2 (co1 varchar2(10) not null,
3 co4 varchar2(10) not null,
4 ch5 number not null);
Table created.
SQL> select count(*) from test2;
COUNT(*)
----------
1262240
SQL> analyze table test2 compute statistics;
Table analyzed.
SQL> select table_name,avg_row_len,num_rows from user_tables
2* where table_name = 'TEST2'
TABLE_NAME AVG_ROW_LEN NUM_ROWS
------------------------------ ----------- ----------
TEST2 16 1262240
analyze수행 후 나타는 실행 계획은 아래와 같다.
SQL> set autot trace exp
SQL> select co1 from test2
2 where co1 like '0%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=499 Card=510510 Bytes=1531530)
1 0 TABLE ACCESS (FULL) OF 'TEST2' (Cost=499 Card=510510 Bytes=1531530)
SQL> select co1 from test2
2* where co1 = '001'
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=182 Card=54880 Bytes=164640)
1 0 INDEX (RANGE SCAN) OF 'PK_TEST2' (UNIQUE) (Cost=182 Card=54880 Bytes=164640)
임시 통계정보를 담을 테이블 생성
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');
EXEC DBMS_STATS.CREATE_STAT_TABLE('계정명','생성할 TABLE명');
PL/SQL procedure successfully completed.
SQL> select * from tab where tname = 'STATS';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STATS TABLE
나. 최신의 통계정보를 gather_table_stat package를 이용하여 수집
SQL> exec dbms_stats.gather_table_stats('SCOTT',
'TEST2',
NULL,
NULL,
FALSE,
'FOR ALL COLUMNS SIZE AUTO',
NULL, 'DEFAULT',
TRUE,
'STATS',
NULL,
NULL);
PL/SQL procedure successfully completed.
혹은
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',
stattab=>'STATS',
tabname=>'TEST2',
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 74',
cascade=>TRUE);
전체 SCHEMA에 대한 통계정보를 수집하려 한다면 아래와 같이 "gather_schema_stats"를 수행하면 된다.
SQL> EXECUTE dbms_stats.gather_schema_stats(
ownname => 'Username',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
DBMS_STATS.EXPORT_SCHEMA_STATS (
ownname VARCHAR2,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
ownname Name of the schema.
stattab User stat table identifier describing where to store the statistics.
statid Identifier (optional) to associate with these statistics within stattab.
statown Schema containing stattab (if different than ownname).
다. 이미 생성되어 있는 Object별 통계정보를 Data Dictionary에서 읽어 step 가)에서 생성한 임시 테이블에 INSERT
SQL> exec dbms_stats.export_table_stats(ownname=>'SCOTT',stattab=>'STATS',tabname=>'TEST2',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from stats;
COUNT(*)
----------
108
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> desc stats
이름 널? 유형
----------------------------------------- -------- ---------------------
STATID VARCHAR2(30)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(30)
C2 VARCHAR2(30)
C3 VARCHAR2(30)
C4 VARCHAR2(30)
C5 VARCHAR2(30)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
D1 DATE
R1 RAW(32)
R2 RAW(32)
CH1 VARCHAR2(1000)
라. Oracle의 export/import를 이용하여 임시 Table에 저장된 data(통계정보)를 이관작업 수행
[encuxdb:/oracle/app/oracle/product/9.2.0]exp scott/tiger file=stats.dmp tables=stats
Export: Release 9.2.0.1.0 - Production on Thu Apr 8 10:47:32 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in KO16KSC5601 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table STATS 108 rows exported
Export terminated successfully without warnings.
이 이후 작업은 이관할 시스템에서 작업하도록 한다.
TEST2라는 테이블과 임시 통계자료를 보관할 STATS 테이블은 만들어져 있는 상황이며,
TEST2라는 테이블에는 한건도 없는 상황이다.
마. 이관된 Database내의 임시테이블에서 FROM database의 통계정보를 dbms_stats.import_table_stats Package를 이용하여 data dictionary로 이관작업을 수행.
D:>imp scott/tiger file=stats.dmp log=stats_imp.log commit=y ignore=y full=y
다음에 접속됨: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
엑스포트 파일은 규정 경로를 거쳐 EXPORT:V09.02.00 에 의해 생성되었습니다
KO16MSWIN949 문자집합과 AL16UTF16 NCHAR 문자 집합에 임포트가 완성되었습니다
KO16KSC5601 문자 집합을 사용해서 클라이언트를 엑스포트합니다 (문자집합 변환이 가능합니다)
. SCOTT 객체를 SCOTT(으)로 임포트하는 중입니다
. . 테이블 "STATS"(를)을 임포트 중 108 행이 임포트되었습니다
임포트가 경고 없이 정상적으로 종료되었습니다.
SQL> select count(*) from stats;
COUNT(*)
----------
108
SQL> select count(*) from test2;
COUNT(*)
----------
0
SQL> set autot trace exp
SQL> select co1 from test2
2 where co1 like '0%';
Execution Plan
--------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'PK_TEST2' (UNIQUE)
SQL> select co1 from test2
2 where co1 = '001';
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'PK_TEST2' (UNIQUE)
위 제시된 실행계획에서 보는 바와 같이 Test2 Table에는 데이터가 한건도 없으며,
기존에 analyze작업을 수행한 적이 없는 상황이다.
마. 이관된 Database내의 임시테이블에서 FROM database의 통계정보를 dbms_stats.import_table_stats Package를
이용하여 data dictionary로 이관작업을 수행.
SQL> exec dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'TEST2',stattab=>'STATS',cascade=>TRUE);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select count(*) from test2;
COUNT(*)
----------
0
SQL> set autot trace exp
SQL> select co1 from test2
2 where co1 like '0%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=317 Card=548801 Bytes=2195204)
1 0 TABLE ACCESS (FULL) OF 'TEST2' (Cost=317 Card=548801 Bytes=2195204)
SQL> select co1 from test2
2 where co1 = '001';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=182 Card=54880 Bytes=219520)
1 0 INDEX (RANGE SCAN) OF 'PK_TEST2' (UNIQUE) (Cost=182 Card=54880 Bytes=219520)
위 예에서 보는 바와 같이 stats테이블에 수집된 test2 table에 대한 통계정보가
import_table_stats package를 통해 data dictionary에 저장되고, 데이터 건수가 전혀 없는 상황에서도
FROM SERVER와 동일한 실행계획과 COST를 볼 수 있다.
2. Export & Import Utility를 이용한 통계정보 이관
DBMS Migration 및 기타 data backup의 목적으로 사용하는 Export & Import의 Option중에 잘 사용하지 않는 statistics라는 Option을 사용하면 1번과 같이 여러 STEP을 거쳐 작업하지 않아도 통계정보를 그대로 이관할 수 있다.
물론 이런 기능을 사용하는데는 몇가지 제약조건이 있는데, 오라클 Version이 9i이상이어야 하며(export시 statistics는 현재 8i에서도 제공하지만, import시에는 제공되지 않고 있음), 동일한 Character Set을 가지고 있어야하는 export & import 제약조건과 동일하다.
가. Export하기
[encuxdb:/oracle/app/oracle/product/9.2.0]exp scott/tiger file=test2.dmp log=test2.log rows=n statistics=compute tables=test2
rows=n 파라메터를 이용하여 test2라는 테이블에 있는 row는 전혀 추출하지 않고 export를 수행하였다.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in KO16KSC5601 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table TEST2
Export terminated successfully without warnings.
나. Import하기
D:>imp scott/tiger file=test2.dmp log=imp.log commit=y ignore=y full=y statistics=always
다음에 접속됨: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
엑스포트 파일은 규정 경로를 거쳐 EXPORT:V09.02.00 에 의해 생성되었습니다
KO16MSWIN949 문자집합과 AL16UTF16 NCHAR 문자 집합에 임포트가 완성되었습니다
KO16KSC5601 문자 집합을 사용해서 클라이언트를 엑스포트합니다 (문자집합 변환이 가능합니다)
. SCOTT 객체를 SCOTT(으)로 임포트하는 중입니다
임포트가 경고 없이 정상적으로 종료되었습니다.
다. Import후 실행계획 확인
SQL> set autot trace exp
SQL> select co1 from test2
2 where co1 like '0%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=317 Card=548801 Bytes=2195204)
1 0 TABLE ACCESS (FULL) OF 'TEST2' (Cost=317 Card=548801 Bytes=2195204)
SQL> select co1 from test2
2 where co1 = '001';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=182 Card=54880 Bytes=219520)
1 0 INDEX (RANGE SCAN) OF 'PK_TEST2' (UNIQUE) (Cost=182 Card=54880 Bytes=219520)
기타 사용자 계정이 달라질 경우에는 FROMUSER, TOUSER 파라메터를 사용하여 Import를 수행하도록한다.
이와같이 시스템상황에 따라서는 전혀 옮겨질 수 없는 대형 시스템 SPEC이라 하더라도 빈 Table들과 그들의 통계정보를 얼마든지 추출하여 새로운 시스템을 구축할 수 있고, 이를 이용하면 운영계시스템에서 작업하는 동일한 수준의 작업을 수행할 수 있음을 알 수 있다.
