| 统计信息的导入导出 | DBA之路：OoNiceDream |

统计信息的导入导出

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/11/export-import-stats.html

由于Bind Peeking导致执行计划变化，生产系统已经近四个月未进行过统计信息分析。最近系统变更较大，要进行一次统计信息的分析。计划是考虑在BC库上进行分析，再把统计信息导到生产库上应用。做个简单的实验，测试下统计信息的导入导出：

测试数据的准备：

sys@TESTDBA>CREATE TABLE TEST1.T1 (A NUMBER);
 
Table created.
 
sys@TESTDBA>CREATE TABLE TEST2.T1 (A NUMBER);
 
Table created.
 
sys@TESTDBA>alter table test1.T1 monitoring;
 
Table altered.
 
sys@TESTDBA>alter table test2.T1 monitoring;
 
Table altered.
 
 
sys@TESTDBA>begin for i in 1..10000 loop
  2  insert into test1.T1 values(i);
  3  commit;
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
sys@TESTDBA>begin for i in 1..5000 loop
  2  insert into test2.T1 values(i);
  3  commit;
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
sys@TESTDBA>select OWNER,TABLE_NAME,TABLESPACE_NAME,MONITORING,
  2  num_rows,blocks,last_analyzed from dba_tables 
  3  where table_name ='T1' and owner IN ('TEST1','TEST2');
 
OWNER   TABLE_NAME TABLESPACE_NAME  MONITORING NUM_ROWS BLOCKS LAST_ANALYZED
------- ---------- ---------------- ---------- -------- ------ -------------
TEST1   T1         TESTDBA_DATA     YES
TEST2   T1         TESTDBA_DATA     YES
 
2 rows selected.
 
sys@TESTDBA>select table_owner,table_name,inserts  from dba_tab_modifications;
 
no rows selected
 
sys@TESTDBA>exec dbms_stats.flush_database_monitoring_info;
 
PL/SQL procedure successfully completed.
 
sys@TESTDBA>select table_owner,table_name,inserts  from dba_tab_modifications;
 
TABLE_OWNER              TABLE_NAME    INSERTS
------------------------ ---------- ----------
TEST1                    T1              10000
TEST2                    T1               5000
 
2 rows selected.

获取统计信息：

sys@TESTDBA>Execute DBMS_STATS.gather_schema_stats(ownname => 'TEST1',
 options => 'GATHER',estimate_percent => 10,
 method_opt => 'for all columns size auto',cascade=>true);
 
PL/SQL procedure successfully completed.
 
sys@TESTDBA>select table_owner,table_name,inserts  from dba_tab_modifications;
 
TABLE_OWNER           TABLE_NAME    INSERTS
--------------------- ---------- ----------
TEST2                 T1               5000
 
1 row selected.
 
sys@TESTDBA>select OWNER,TABLE_NAME,TABLESPACE_NAME,MONITORING,
  2  num_rows,blocks,last_analyzed from dba_tables 
  3  where table_name ='T1' and owner IN ('TEST1','TEST2');
 
OWNER    TABLE_NAME TABLESPACE_NAME   MONITORING NUM_ROWS  BLOCKS LAST_ANALYZED
-------- ---------- ----------------- ---------- -------- ------- ----------------
TEST1    T1         TESTDBA_DATA      YES           10000      20 2008-11-05 16:53
TEST2    T1         TESTDBA_DATA      YES
 
2 rows selected.
 
sys@TESTDBA>select table_owner,table_name,inserts  from dba_tab_modifications;
 
TABLE_OWNER           TABLE_NAME    INSERTS
--------------------- ---------- ----------
TEST2                 T1               5000
 
1 row selected.

导出统计信息：

sys@TESTDBA>Execute DBMS_STATS.create_stat_table(ownname=>'PERFSTAT', 
stattab=>'TEST1_STAT_BAK');
 
PL/SQL procedure successfully completed.
 
sys@TESTDBA>Execute  DBMS_STATS.export_schema_stats(ownname =>'TEST1', 
stattab =>'TEST1_STAT_BAK', statid=>'N1',statown=>'PERFSTAT');
 
PL/SQL procedure successfully completed.
 
 
sys@TESTDBA>select statid,type,c1,c5,n4,d1 from perfstat.TEST1_STAT_BAK;
 
STATI TYPE       C1         C5                 N4 D1
----- ---------- ---------- ---------- ---------- ----------------
N1    T          T1         TEST1           10000 2008-11-05 16:53
N1    C          T1         TEST1           10000 2008-11-05 16:53
 
sys@TESTDBA>update perfstat.TEST1_STAT_BAK set c5='TEST2';
 
2 rows updated.
 
sys@TESTDBA>commit;
 
Commit complete.
 
sys@TESTDBA>select statid,type,c1,c5,n4,d1 from perfstat.TEST1_STAT_BAK;
 
STATI TYPE       C1         C5                 N4 D1
----- ---------- ---------- ---------- ---------- ----------------
N1    T          T1         TEST2           10000 2008-11-05 16:53
N1    C          T1         TEST2           10000 2008-11-05 16:53
 
2 rows selected.

导入统计信息：

sys@TESTDBA>Execute  DBMS_STATS.import_schema_stats (ownname=>'TEST2',
stattab=>'TEST1_STAT_BAK',statid=>'N1', statown=>'PERFSTAT', no_invalidate=>true );
 
PL/SQL procedure successfully completed.

查看结果，可以看出统计信息已导入：

sys@TESTDBA>select table_owner,table_name,inserts  from dba_tab_modifications;
 
no rows selected
 
sys@TESTDBA>exec dbms_stats.flush_database_monitoring_info;
 
PL/SQL procedure successfully completed.
 
sys@TESTDBA>select table_owner,table_name,inserts  from dba_tab_modifications;
 
no rows selected
 
sys@TESTDBA>select OWNER,TABLE_NAME,TABLESPACE_NAME,MONITORING,
  2  num_rows,blocks,last_analyzed from dba_tables 
  3  where table_name ='T1' and owner IN ('TEST1','TEST2');
 
 
OWNER      TABLE_NAME TABLESPACE_NAME      MONITORING NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------------- ---------- -------- ------ ----------------
TEST1      T1         TESTDBA_DATA         YES           10000     20 2008-11-05 16:53
TEST2      T1         TESTDBA_DATA         YES           10000     20 2008-11-05 16:53
 
2 rows selected.
 
sys@TESTDBA>