兩隻小虎,一隻豬

Oracle 提供的DBMS_STATS 這個package 除了可以用來收集Optimizer 所需要的統計資料之外,更可以透過他來Export/Import COLUMN, DATABASE, DIRCTIONARY, FIXED_OBJECTS, INDEX, SCHEMA, SYSTEM, TABLE 的statistic data, 以下就以 Export/Import Table statistic 例子來說明

1. Create User-deined Statistic table

在Export/Import Table statistc 之前,需要先透過DBMS_STAT.CREATE_STAT_TABLE procedure 來建立用來存放statistic資料的表格,語法如下

DBMS_STATS.CREATE_STAT_TABLE (
   ownname  VARCHAR2, 
   stattab  VARCHAR2,
   tblspace VARCHAR2 DEFAULT NULL);

DBMS_STATS.CREATE_STATS_TABLE (
   ownname => USER, 
   stattab => 'TEST_TABLE_STAT'
)

TEST_TABLE_STAT 是用來存放statistic table name

2.Export Statistics into User-defined table

要將Test table statistic export 到TEST_TABLE_STAT,有兩個方式

2.1 DBMS_STATS.GATHER_TABLE_STATS

使用DBMS_STATS.GATHER_TABLE_STATS package 除了可以將statistic 寫至Oracle Dictionary tables 外,還可以寫至我們指定的table

將statistic 寫入Oracle Dictionary tables 語法如下

DBMS_STATS.GATHER_TABLE_STATS(
   ownname => USER, 
   tabname => 'TEST'
);

將statistic 同時寫入Oracle Dictionary tables和user-defined table語法

DBMS_STATS.GATHER_TABLE_STATS(
   ownname => USER, 
   tabname => 'TEST' 
   method_opt =>'for all indexed columns', 
   cascade => true, 
   stattab => 'CASE_HDR_STAT_TAB', 
   statid => 1
);

2.2 DBMS_STATS.EXPORT_TABLE_STATS

如果要備份目前Oracle dictionary tables 裡面的資料可以透過下面方式,直接將目前的statistic資料寫入user-defined table

DBMS_STATS.EXPORT_TABLE_STATS(
   ownname => USER, 
   tabname => 'TEST',
   stattab =>'TEST_TABLE_STAT', 
   statid => 1, 
   cascade => true
);

3. Import statistics from User-defined table into Dictionary

將資料從TEST_TABLE_STAT 寫回 Oracle dictionary tables

DBMS_STATS.IMPORT_TABLE_STATS(
   ownname => USER, 
   tabname =>  'TEST , 
   stattab => 'TEST_TABLE_STAT', 
   statid => 1, 
   cascade => true, 
   no_invalidate => true
);

下面的例子說明如何將Table statistic export/import 到不同DB

1. Create User-defined table

在source DB create user-defined table

SQL> connect user/pwd
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=> 'USER', stattab=>'STAT_TABLE');

2. Export table statistics data from user-defined table

EXEC DBMS_STATS.EXPORT_TABLE_STATS('USER','TEST',NULL,'STAT_TABLE');

3. Use exp command to dump STAT_TABLE data

exp USER_ID/PASSWORD tables=USER.STATS_TABLE file=stats.dmp  log=stats.log

4. Transfer dump file from source DB to target DB

5. Use imp command to import statistics data from dump file to target DB

imp USER_ID/PASSWORD tables=STATS fromuser=USER touser=USER file=stats.dmp ignore=y

6. Import statistic data from STAT_TABLE to dictionary tales on target DB

EXEC DBMS_STATS.IMPORT_TABLE_STATS(
   ownname => 'USER', 
   tabname => 'TEST', 
   stattab => 'STAT_TABLE'
);

Reference:
http://decipherinfosys.wordpress.com/2008/02/19/exportimport-of-optimizer-statistics-in-oracle/
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm