ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 10g rebuild index,索引原来统计信息丢失

Oracle 10g rebuild index,索引原来统计信息丢失

原创 Linux操作系统 作者:ljm0211 时间:2012-07-04 18:44:33 0 删除 编辑

1、创建测试表及索引

create table t as select * from dba_objects where rownum<=100;
create index ind_t_object_id on t(object_id);

create table tt (id number,name varchar2(
30));
insert into tt values(
1,'aaa');
insert into tt values(
2,'bbb');
insert into tt values(
3,'ccc');

commit;

 

2、查看创建对象当前的统计信息

select * from dba_tab_statistics where wner='DBMGR' and table_name='T';

统计信息为空;
select * from dba_ind_statistics where wner='DBMGR' and index_name='IND_T_OBJECT_ID';

统计信息存在; rowcnt100
select * from dba_tab_statistics where wner='DBMGR' and table_name='TT';

统计信息为空;

 

3、收集表的统计信息

begin
dbms_stats.gather_table_stats(ownname => 'DBMGR',tabname => 'T', cascade => true);
end;

 

4、查看备份的统计信息
select * from sys.WRI$_OPTSTAT_tab_HISTORY where obj#=357525 order by savtime desc;

备份正常,备份了一条空的统计信息;备份记录中savtime04-JUL-12 04.56.21.319354 PM +08:00

 

select * from sys.WRI$_OPTSTAT_IND_HISTORY where obj#=357526 order by savtime desc;

备份正常,备份了一条rowcnt100analyzetime2012/7/4 16:50:50 的统计信息;备份记录中savtime04-JUL-12 04.56.21.495591 PM +08:00

 

5、查看两个对象的信息

select * from dba_objects where wner='DBMGR' and object_name in ('T','IND_T_OBJECT_ID');

createdlast_ddl_time 都是2012/7/4 16:50:50

 

6、查看创建对象当前的统计信息

select * from dba_tab_statistics where wner='DBMGR' and table_name='T';

统计信息:num_rows: 100 last_analyzed2012/7/4 16:56:21
select * from dba_ind_statistics where wner='DBMGR' and index_name='IND_T_OBJECT_ID';

统计信息:num_rows: 100 last_analyzed2012/7/4 16:56:21

 

7、为了便于观察,删除表中1条数据,重新收集统计信息,查看统计信息

select * from dba_tab_statistics where wner='DBMGR' and table_name='T';

统计信息:num_rows: 99 last_analyzed2012/7/4 17:12:44
select * from dba_ind_statistics where wner='DBMGR' and index_name='IND_T_OBJECT_ID';

统计信息:num_rows: 99 last_analyzed2012/7/4 17:12:44

 

 

7、执行restore,恢复最初的统计信息

begin
dbms_stats.restore_table_stats(ownname => 'DBMGR',tabname => 'T', AS_OF_TIMESTAMP => '04-JUL-12 04.56.20.495591 PM +08:00');
end;

 

8、查看创建对象当前的统计信息

select * from dba_tab_statistics where wner='DBMGR' and table_name='T';

统计信息为空; 恢复正确
select * from dba_ind_statistics where wner='DBMGR' and index_name='IND_T_OBJECT_ID';

统计信息存在; rowcnt100 last_analyzed2012/7/4 16:50:50  恢复正确

 

9、查看当前备份的索引统计信息,如图,都是正确的

 

10、索引当前统计信息:rowcnt100 last_analyzed2012/7/4 16:50:50;为了便于观察,再删除表中10条数据,然后rebuild index

alter index dbmgr.IND_T_OBJECT_ID rebuild online;

我们期望看到新增一条rowcnt100的统计信息备份,但是实际新增统计信息如下,都是空的。

 

11、当前的统计信息情况

select * from dba_tab_statistics where wner='DBMGR' and table_name='T';

统计信息为空;
select * from dba_ind_statistics where wner='DBMGR' and index_name='IND_T_OBJECT_ID';

统计信息存在; rowcnt89 last_analyzed2012/7/4 18:20:41rebuild index更新了统计信息

 

12、尝试恢复索引统计信息

begin
dbms_stats.restore_table_stats(ownname => 'DBMGR',tabname => 'T', AS_OF_TIMESTAMP => ' 04-JUL-12 06.20.40.126087 PM +08:00');
end;

 

13、查看当前的统计信息情况

select * from dba_tab_statistics where wner='DBMGR' and table_name='T';

统计信息为空;
select * from dba_ind_statistics where wner='DBMGR' and index_name='IND_T_OBJECT_ID';

统计信息为空;索引统计信息无法恢复到04-JUL-12 06.20.41.126087 PM +08:00 备份前的统计信息;

 

=============================================================================================

补充测试:

再删除10条数据,

begin
dbms_stats.gather_table_stats(ownname => 'DBMGR',tabname => 'T', cascade => true);
end;

 

select * from dba_tab_statistics where wner='DBMGR' and table_name='T';

统计信息:num_rows: 79 last_analyzed2012/7/4 18:31:17
select * from dba_ind_statistics where wner='DBMGR' and index_name='IND_T_OBJECT_ID';

统计信息:num_rows: 79 last_analyzed2012/7/4 18:31:18

 

索引备份的统计信息:

 

再删除10条数据

alter index dbmgr.IND_T_OBJECT_ID rebuild online;

期望看到新增一条rowcnt79的统计信息备份,索引新的统计信息变为num_rows69last_analyzed2012/7/4 18:37:51

结果备份了一条空统计信息:

执行恢复

begin
dbms_stats.restore_table_stats(ownname => 'DBMGR',tabname => 'T', AS_OF_TIMESTAMP => ' 04-JUL-12 06.35.30.958529 PM +08:00');
end;

 

索引统计信息被恢复为空了,无法在恢复出num_rows:79 last_analyzed2012/7/4 18:31:18的统计信息;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11411056/viewspace-734586/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    442038