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