由于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>
|
|
