统计信息的导入导出

                    作者 ：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>