有时需要恢复统计信息来恢复以前的执行计划

以test用户登录

--创建测试表
create table test as select decode(object_id, 1,1,999) test_id, object_name test_name from dba_objects;
create index test_id_ix on test (test_id);

--查看记录数
select test_id,count(*) from test group by test_id;

--收集统计信息
begin
  dbms_stats.gather_table_stats(
    wnname => user ,
    tabname => 'TEST' ,
    estimate_percent => 100 ,
    cascade => true);
end;
/

--查看执行计划
EXPLAIN PLAN FOR
  SELECT test_id, test_name FROM test where test_id = 999;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

--查看统计信息
select
  COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS,
  to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='TEST'
/

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS LAST_ANALYZED
------------------------------ ------------ ----------- -------------------
TEST_ID                                   1           1 2012-25-02 20:03:29
TEST_NAME                             17930           1 2012-25-02 20:03:29

以scott用户登录

conn scott/tiger

--创建测试表
create table test as select decode(object_id, 1,1,999) test_id, object_name test_name from dba_objects;
create index test_id_ix on test (test_id);

--查看记录数
select test_id,count(*) from test group by test_id;

--查看执行计划
EXPLAIN PLAN FOR
  SELECT test_id, test_name FROM test where test_id = 999;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

--查看统计信息
select
  COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS,
  to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='TEST'
/

以scott用户登录

conn test/test

--创建统计信息表
exec dbms_stats.create_stat_table('TEST','STATS_BAK');

--导出统计信息
exec dbms_stats.export_table_stats('TEST','TEST',NULL,'STATS_BAK','TEST_1',TRUE);

exp userid=test/test file=stats_bak.dmp query=\" where statid='TEST_1'\" tables=stats_bak

--导入统计信息
imp userid=scott/tiger file=stats_bak.dmp tables=stats_bak fromuser=test touser=scott

--更新schema
update STATS_BAK set c5 = 'SCOTT'
where c5 = 'TEST'
  and statid = 'TEST_1';

commit;

exec dbms_stats.import_table_stats('SCOTT','TEST',NULL,'STATS_BAK','TEST_1',TRUE);

--查看统计信息
select
  COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS,
  to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='TEST'
/

--查看执行计划
EXPLAIN PLAN FOR
  SELECT test_id, test_name FROM test where test_id = 999;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

--备份统计信息
insert into test select decode(object_id, 1000,1000,1999) test_id, object_name test_name from dba_objects;
commit;

--导出统计信息
exec dbms_stats.export_table_stats('TEST','TEST',NULL,'STATS_BAK','TEST_2',TRUE);

--收集统计信息
begin
  dbms_stats.gather_table_stats(
    wnname => user ,
    tabname => 'TEST' ,
    estimate_percent => 100 ,
    cascade => true);
end;
/

--查看统计信息
select
  COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS,
  to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='TEST'
/

--恢复统计信息
exec dbms_stats.import_table_stats('TEST','TEST',NULL,'STATS_BAK','TEST_2',TRUE);

--查看统计信息
select
  COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS,
  to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='TEST'
/

select table_name, to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tables
where table_name='TEST'
/

--查看执行计划
EXPLAIN PLAN FOR
  SELECT test_id, test_name FROM test where test_id = 1000;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

EXPLAIN PLAN FOR
  SELECT test_id, test_name FROM test where test_id = 999;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());