由于Bind Peeking導(dǎo)致執(zhí)行計劃變化，生產(chǎn)系統(tǒng)已經(jīng)近四個月未進行過統(tǒng)計信息分析。最近系統(tǒng)變更較大，要進行一次統(tǒng)計信息的分析。計劃是考慮在BC庫上進行分析，再把統(tǒng)計信息導(dǎo)到生產(chǎn)庫上應(yīng)用。做個簡單的實驗，測試下統(tǒng)計信息的導(dǎo)入導(dǎo)出：
測試數(shù)據(jù)的準(zhǔn)備：
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. |
獲取統(tǒng)計信息：
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. |
導(dǎo)出統(tǒng)計信息：
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. |
導(dǎo)入統(tǒng)計信息：
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. |
查看結(jié)果，可以看出統(tǒng)計信息已導(dǎo)入：
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> |
