개발자(開發者) a developer

출처 : [한국 Oracle Technology Network]의 기술 지원 게시판

========================================================================================

No. 22383 : DBMS_STATS.GATHER_SYSTEM_STATS를 이용한 통계정보 수집 가이드
========================================================================================

PURPOSE
-------

   이 자료는 DBMS_STATS.GATHER_SYSTEM_STATS 함수를 사용하여 system table에 대한

   통계 정보 수집 시 stats table과 AUX_STATS$ table에 나타나는 정보에 대한 분석 및 
   통계 정보 생성 시 필요한 INTERVAL에 대한 내용과 통계정보 수집 상태를 나타내는

   NOWORKLOAD, BADSTATS, AUTOGATHERING 상태에 대한 설명입니다.

Explanation
-----------

다음과 같은 문서를 참고하여 test 후, 확인하였습니다.
<Note:153761.1> Gathering System Statistics Returns only 'NOWORKLOAD'.
<Note:149560.1> Collect and Display System Statistics (CPU and IO) for CBO usage.

DBMS_STATS.GATHER_SYSTEM_STATS를 사용하여 20분 동안의 통계정보를 생성하는

간단한 절차를 test를 통해 알아보기로 한다. 통계정보 수집 시간이 최소 권장 사항인 60분

이상이 되지 않아도 오라클에서 통계정보를 측정하기 위한 기준치 만큼의 자원 소비가 있으면 
PVAL2가 badstats로 나오지 않고 autogathering으로 정상으로 나오는 것을 알 수 있다.

Step 
-----
1. sqlplus "/as sysdba"
2. EXECUTE DBMS_STATS.CREATE_STAT_TABLE( ownname => 'SYSTEM',

    stattab => 'mystats'); 
3. ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 1;

4. EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( interval => 20, stattab => 'mystats',

     statid => 'DAY', statown => 'SYSTEM'); 
    EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', 20);

< 참고 > statid 로 DAY 와 NIGHT 이 있으므로, DBMS_STATS.GATHER_SYSTEM_STATS 
             함수의 파라미터로 선택하여 사용 가능함.

5. select * from system.mystats; 
    select count(*) from system.mystats;

   위의 select 수행 결과 no rows selected로 나오면 아래와 같이 수행한다. 

   EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'START',

    stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');

   또한 START 를 MANUAL하게 해주지 않으면 다음과 같이 NOWORKLOAD로 나타난다.

SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

PNAME     PVAL2
---------  --------------------------------------------------------------------
STATUS    NOWORKLOAD

6. DBMS_STATS.GATHER_SYSTEM_STATS 함수에서 START 를 해주면 아래와 같이 
   AUTOGATHERING 상태로 바뀐다.

SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

PNAME    PVAL2
--------  ---------------------------------------------------------------------
STATUS   AUTOGATHERING

7. 아직 20분이 경과되지 않으면 아래와 같이 진행 상황을 확인할 수 있다.

SQL> alter session set nls_date_format='mm-dd-YYYY HH24:MI';
SQL> select c1, to_date(c2), to_date(c3) from system.mystats;

C1                               TO_DATE(C2)        TO_DATE(C3)
--------------------------------------------------------------
MANUALGATHERING      12-21-2004 02:46   12-21-2004 02:46
 

8. MANUAL하게 STOP을 하려면 아래와 같이 수행한다.

EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'STOP',

stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM'); 

<주의> 만약, DBMS_STATS.GATHER_SYSTEM_STATS 함수를 이용하여 STOP을
실행한 이후에 아래 9번과 같은 명령을 수행 시 PVAL2가 badstats 로 나온다면 이것은

interval 이 충분하지 않아 그 기간 동안에 작업 WORKLOAD가 부족하기 때문입니다.

따라서, interval 동안 SINGLE BLOCK I/O와 multiblock I/O에 대한 workload가 오라클에서

통계정보를 측정하기 위한 기준치 만큼의 자원 소비가 없으면 정보가 부족하여 발생하는

현상으로 보입니다. 즉, 지속적인 i/o에 대한 통계치를 제대로 수집하지 못해서 발생하는

것입니다. 시스템에 대한 통계 정보 수집 시 interval은 최소한 60분, default로 120분입니다. 
  

9. 20분이 지나면 아래와 같은 spool 결과를 볼 수 있고, sys.aux_stats$ table에서

    PVAL2 가 AUTOGATHERING에서 COMPLETED 로 변경됨을 알 수 있다. 
     
SQL> select SNAME, PNAME, PVAL1, PVAL2 from SYS.AUX_STATS$

         where pname ='STATUS';

SNAME                  PNAME              PVAL1        PVAL2                    
--------------------------------------------------------------------------------
SYSSTATS_INFO     STATUS                               COMPLETED     

10. 통계정보를 보관하기 위해 생성한 'mystats' 라는 stats table을 drop하려면 
     이와 같이 DBMS_STATS.DROP_STAT_TABLE procedure를 수행한다. 

EXECUTE DBMS_STATS.DROP_STAT_TABLE( ownname => 'SYSTEM', stattab =>'mystats');

< 권장 사항 >

dbms_stats.gather_system_stats package 사용 시 이 두 개의 파라미터를 Tuning 후

parameter file에 설정하면 보다 나은 CBO의 효과를 얻을 수 있다.

  ~ optimizer_index_caching  (range of value : 0 to 100)
  ~ optimizer_index_cost_adj (range : 1 to 10000)

< 참고 >

이와 같은 시스템 통계정보 생성 작업을 dbms_job.submit 프로시져를 이용하여 수행할 수도 있다.

이렇게 job으로 등록했을 경우에는 통계정보 생성 중에 dba_jobs 와 dba_jobs_running 뷰를

조인하여 확인해 보면 WHAT 컬럼에 아래와 같은 job scheduling이 걸리는 것을 볼 수 있다.

sql>dbms_stats.gather_system_stats(gathering_mode => 'AUTO_STOP', statown => 'SYS');

Example
----------

위의 작업에 대한 spool on 수행 결과입니다.

SQL> select * from sys.aux_stats$;

SQL> SELECT count(*) from system.mystats;

SQL> select job, what from user_jobs;                                    
SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

SQL> select * from sys.aux_stats$;

SQL> exec DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM');

BEGIN DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM'); END;

*
ERROR at line 1:
ORA-20003: Unable to import system statistics 
ORA-06512: at "SYS.DBMS_STATS", line 5437 
ORA-06512: at line 1

SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'STOP',

          stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');

SQL> select c1, to_date(c2), to_date(c3) from system.mystats;

C1                             TO_DATE(C2)      TO_DATE(C3)                     
------------------------------ ---------------- ----------------                
COMPLETED              12-21-2004 02:46   12-21-2004 03:10                
                                                                               

SQL> select SNAME, PNAME, PVAL1, PVAL2 from SYS.AUX_STATS$

         where pname ='STATUS';

SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'START',

         stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');

SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

SQL> select count(*) from system.mystats;

SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

SQL> exec DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM');

BEGIN DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM'); END;

*
ERROR at line 1:
ORA-20003: Unable to import system statistics 
ORA-06512: at "SYS.DBMS_STATS", line 5437 
ORA-06512: at line 1

SQL> spool off

위의 DBMS_STATS.IMPORT_SYSTEM_STATS 수행 시 ORA-20003 error에 대해서는

이 문서 <Note:154601.1>을 참조하시기 바랍니다. User table로부터의 system 통계 정보를

Data Dictionary로 transfer할 때 ORA-20003 에러에 대한 해결방법입니다.

위 과정 전체를 JOB으로 등록하여 DBMS_JOB.SUBMIT으로 수행하는 방법은 아래

참고 노트에 나와 있습니다.

Reference Documents
-------------------
<Note:153761.1>
<Note:149560.1>

출처 : http://blog.naver.com/darkturtle/50070269004

B. GATHER_SCHEMA_STATS Procedure

: 스키마 OWNER 의 모든OBJECT 에 대한 통계정보를 수집한다.

전체적으로 GATHER_TABLE_STATS 와 같고 아래 부분이 다르다.

스키마 유저만 지정 하면 GATHER OPTION  이 작동하여 모든 Object 의 정보가 수집 된다.

DBMS_STATS.GATHER_SCHEMA_STATS (

   ownname          VARCHAR2,

   estimate_percentNUMBER   DEFAULT to_estimate_percent_type

                 (get_param('ESTIMATE_PERCENT')),

   block_sample     BOOLEAN DEFAULT FALSE,

   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),

   degree           NUMBER   DEFAULT

 to_degree_type(get_param('DEGREE')),

  granularity      VARCHAR2 DEFAULTGET_PARAM('GRANULARITY'),

   cascade          BOOLEAN  DEFAULT

to_cascade_type(get_param('CASCADE')),

   stattab          VARCHAR2 DEFAULT NULL,

   statid           VARCHAR2 DEFAULT NULL,

   options          VARCHAR2 DEFAULT 'GATHER',

   objlist          OUT      ObjectTab,

   statown          VARCHAR2 DEFAULT NULL,

   no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type (

                                     get_param('NO_INVALIDATE')),

  force             BOOLEAN DEFAULT FALSE);

DBMS_STATS.GATHER_SCHEMA_STATS (

   ownname          VARCHAR2,

   estimate_percentNUMBER   DEFAULT to_estimate_percent_type

                                               (get_param('ESTIMATE_PERCENT')),

   block_sample     BOOLEAN DEFAULT FALSE,

   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),

   degree           NUMBER  DEFAULT to_degree_type(get_param('DEGREE')),

  granularity      VARCHAR2 DEFAULTGET_PARAM('GRANULARITY'),

   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),

   stattab          VARCHAR2 DEFAULT NULL,

   statid           VARCHAR2 DEFAULT NULL,

   options          VARCHAR2 DEFAULT 'GATHER',

   statown          VARCHAR2 DEFAULT NULL,

   no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type (

                                     get_param('NO_INVALIDATE'),

   force            BOOLEAN DEFAULT FALSE);

1. 통계정보가맛간 테이블 리스트 출력

<!--[if !supportLists]--><!--[endif]-->

SQL> setserveroutput on

SQL> declare

    mystaleobjs dbms_stats.objecttab;

    begin

    -- check whether there is any stale objects

   dbms_stats.gather_schema_stats(ownname=>'SCOTT', -

 options=>'LISTSTALE',objlist=>mystaleobjs);

    for i in 1 .. mystaleobjs.count loop

   dbms_output.put_line(mystaleobjs(i).objname);

    end loop;

    end;

  /

<!--[if !supportLists]-->2. 자동으로 통계정보수집 시에 대상 테이블 리스트 출력

SQL> setserveroutput on

SQL>declare

     mystaleobjs dbms_stats.objecttab;

    begin

    dbms_stats.gather_schema_stats(ownname=>‘SCOTT’,

options=>'LISTAUTO',objlist=>mystaleobjs);

    for i in 1 .. mystaleobjs.count loop

   dbms_output.put_line(mystaleobjs(i).objname);

    end loop;

   end ;

    /

PL/SQL proceduresuccessfully completed.

 -- 통계정보 생성이 필요한 테이블 리스트업 (DATA 이행등, STALE TABLE LIST 추출) 
SELECT *
FROM ALL_TAB_MODIFICATIONS
WHERE TABLE_OWNER IN ( 'SCOTT' )
and INSERTS > 100000 ;

-- 통계정보 생성 스크립트 작성 
SELECT 'exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'''||table_owner||''',TABNAME=>'''||table_name||''');'
FROM ALL_TAB_MODIFICATIONS
WHERE TABLE_OWNER IN ( 'SCOTT' )
and INSERTS > 100000 ;

출처: 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들과 그들의 통계정보를 얼마든지 추출하여 새로운 시스템을 구축할 수 있고, 이를 이용하면 운영계시스템에서 작업하는 동일한 수준의 작업을 수행할 수 있음을 알 수 있다.