一般重新收集table 或index 都会有风险，需要把之前的统计信息备份下，万一收集后performance 没有之前好，已经将备份的统计信息导入快速恢复。
创建统计信息备份表
begin
dbms_stats.create_stat_table(ownname => 'SYSTEM',stattab => 'STAT_BK');
end;
/
生成表统计信息导出备份脚本
select 'begin dbms_stats.export_table_stats(ownname => ' || chr(39) ||
b.owner || chr(39) || ',tabname =>' || chr(39) || b.table_name ||
chr(39) || ',stattab =>' || chr(39) || 'STAT_BK' || chr(39) ||
',statown =>' || chr(39) || 'SYSTEM' || chr(39) ||
',cascade => true); end; /'
from dba_segments a, dba_tables b
where a.tablespace_name IN ('DW_100M_DT')
and a.segment_type = 'TABLE'
and a.owner = b.owner
and a.segment_name = b.table_name;
begin
SYS.dbms_stats.export_table_stats(ownname => 'MESTST',tabname =>'ACTIONMAP',stattab =>'STAT_BK',cascade => true,statown=>'SYSTEM');
end;
/
生成索引统计信息导出备份脚本
select 'begin dbms_stats.export_index_stats(ownname => ' || chr(39) ||
b.owner || chr(39) || ',indname =>' || chr(39) || b.index_name ||
chr(39) || ',stattab =>' || chr(39) || 'STAT_BK' || chr(39) ||
',statown =>' || chr(39) || 'SYSTEM' || chr(39) || '); end; / '
from dba_segments a, dba_indexes b
where a.tablespace_name IN ('GW_100M_DATN', 'GW_100M_IDXN')
and a.segment_type = 'INDEX'
and a.owner = b.owner
and a.segment_name = b.index_name
union
select 'begin dbms_stats.export_index_stats(ownname => ' || chr(39) ||
b.owner || chr(39) || ',indname =>' || chr(39) || b.index_name ||
chr(39) || ',stattab =>' || chr(39) || 'STAT_BK' || chr(39) ||
',statown =>' || chr(39) || 'SYSTEM' || chr(39) || '); end; / '
from dba_segments a, dba_indexes b
where a.tablespace_name IN ('GW_100M_DATN', 'GW_100M_IDXN')
and a.segment_type = 'TABLE'
and a.owner = b.table_owner
and a.segment_name = b.table_name;
IMPORT 表的统计信息
begin
SYS.dbms_stats.IMPORT_TABLE_STATS(ownname => 'MESTST',tabname =>'ACTIONMAP',stattab =>'STAT_BK',cascade => true,statown=>'SYSTEM');
end;
/
