缘起: 复制库的某个SQL因统计信息原因执行计划与主库不一致,而复制库重新收集统计信息在性能消耗和时间上不可接受,使用此方法直接从主库导入相关表的统计信息。
省时省力。
源库:
EXEC DBMS_STATS.drop_stat_table(OWNNAME => 'PAYADM',STATTAB => 'PART_STAT');
EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME => 'PAYADM',TABNAME => '&TABNAME',PARTNAME => '&TABNAME'||'_20170126',STATTAB => 'PART_STAT',statid => 'TAB_&TABNAME'||'_20170126');
目标库:
EXEC DBMS_STATS.drop_stat_table(OWNNAME => 'PAYADM',STATTAB => 'PART_STAT');
INSERT INTO PAYADM.PART_STAT ( STATID ,
TYPE ,
VERSION ,
FLAGS ,
C1 ,
C2 ,
C3 ,
C4 ,
C5 ,
N1 ,
N2 ,
N3 ,
N4 ,
N5 ,
N6 ,
N7 ,
N8 ,
N9 ,
N10 ,
N11 ,
N12 ,
D1 ,
R1 ,
R2 ,
CH1 )
SELECT STATID ,
TYPE ,
VERSION ,
FLAGS ,
C1 ,
C2 ,
C3 ,
C4 ,
C5 ,
N1 ,
N2 ,
N3 ,
N4 ,
N5 ,
N6 ,
N7 ,
N8 ,
N9 ,
N10 ,
N11 ,
N12 ,
D1 ,
R1 ,
R2 ,
CH1 FROM PAYADM.PART_STAT@MPRACDG;
EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME => 'PMCADM',TABNAME => '&TABNAME',PARTNAME => '&TABNAME'||'_20170126',STATTAB => 'PART_STAT',statid => 'TAB_&TABNAME'||'_20170126',cascade => TRUE,force => TRUE);
省时省力。
源库:
EXEC DBMS_STATS.drop_stat_table(OWNNAME => 'PAYADM',STATTAB => 'PART_STAT');
EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME => 'PAYADM',TABNAME => '&TABNAME',PARTNAME => '&TABNAME'||'_20170126',STATTAB => 'PART_STAT',statid => 'TAB_&TABNAME'||'_20170126');
目标库:
EXEC DBMS_STATS.drop_stat_table(OWNNAME => 'PAYADM',STATTAB => 'PART_STAT');
INSERT INTO PAYADM.PART_STAT ( STATID ,
TYPE ,
VERSION ,
FLAGS ,
C1 ,
C2 ,
C3 ,
C4 ,
C5 ,
N1 ,
N2 ,
N3 ,
N4 ,
N5 ,
N6 ,
N7 ,
N8 ,
N9 ,
N10 ,
N11 ,
N12 ,
D1 ,
R1 ,
R2 ,
CH1 )
SELECT STATID ,
TYPE ,
VERSION ,
FLAGS ,
C1 ,
C2 ,
C3 ,
C4 ,
C5 ,
N1 ,
N2 ,
N3 ,
N4 ,
N5 ,
N6 ,
N7 ,
N8 ,
N9 ,
N10 ,
N11 ,
N12 ,
D1 ,
R1 ,
R2 ,
CH1 FROM PAYADM.PART_STAT@MPRACDG;
EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME => 'PMCADM',TABNAME => '&TABNAME',PARTNAME => '&TABNAME'||'_20170126',STATTAB => 'PART_STAT',statid => 'TAB_&TABNAME'||'_20170126',cascade => TRUE,force => TRUE);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-2136307/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/308563/viewspace-2136307/