10g统计信息的管理(转)

禁用10自动统计分析特性：

exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

exec dbms_stats.gather_table_stats(username,tablename,method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => TRUE,estimate_percent => 25,granularity=>'all',degree => 4,no_invalidate => false);

oracle9i生产库上在导出表的统计信息时有部分索引的统计信息导不出来，开始认为是索引的统计列GLOBAL_STAT=NO导致索引统计信息无法导出，在这里做一个演示来说明导致这种问题的原因。

create table T1 (A NUMBER,B NUMBER);

declare

begin

for i in 1..10000 loop

insert into T1 (a) values (i);

end loop;

commit;

update T1 set b = 9991;

update T1 set b = 1 where a = 1;

update T1 set b = 2 where a = 2;

update T1 set b = 3 where a = 3;

update T1 set b = 4 where a = 4;

update T1 set b = 9996 where a = 9996;

update T1 set b = 9997 where a = 9997;

update T1 set b = 9998 where a = 9998;

update T1 set b = 9999 where a = 9999;

update T1 set b = 10000 where a = 10000;

commit;

end;

/

create index ind_t1_b on t1(b);

select i.index_name,i.global_stats from dba_indexes i where i.index_name = 'IND_T1_B';

INDEX_NAME GLOBAL_STATS

IND_T1_B NO

单独对索引进行分析

analyze index IND_T1_B compute statistics;

select i.index_name,i.global_stats from dba_indexes i where i.index_name = 'IND_T1_B';

INDEX_NAME GLOBAL_STATS

IND_T1_B NO

exec dbms_stats.create_stat_table(user,'STAT_TIMESTAMP');

exec dbms_stats.export_table_stats(user,'T1',NULL,'STAT_TIMESTAMP');

select C1,D1 from STAT_TIMESTAMP;

C1 D1

T1 2009-2-3 15:04:45

IND_T1_B 2009-2-3 15:13:47

可以看到有对应的统计信息的导出

delete from STAT_TIMESTAMP;

执行对表的分析

exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T1',estimate_percent => 30,granularity=>'all',method_opt=> 'for all indexed columns size auto',cascade=> TRUE,degree => 2);

select i.index_name,i.global_stats,i.last_analyzed from dba_indexes i where i.index_name = 'IND_T1_B';

INDEX_NAME GLOBAL_STATS LAST_ANALYZEDYZED

IND_T1_B YES 2009-2-3 15:17:59

select o.object_name,o.last_ddl_time from dba_objects o where o.object_name = 'IND_T1_B';

OBJECT_NAME LAST_DDL_TIME

IND_T1_B 2009-2-3 15:13:07

select i.index_name,i.global_stats from dba_indexes i where i.index_name = 'IND_T1_B';

INDEX_NAME GLOBAL_STATS

------------------------------ ---

IND_T1_B YES

alter index IND_T1_B rebuild;

select i.index_name,i.global_stats from dba_indexes i where i.index_name = 'IND_T1_B';

INDEX_NAME GLOBAL_STATS

------------------------------ ---

IND_T1_B NO

exec dbms_stats.export_table_stats(user,'T1',NULL,'STAT_TIMESTAMP');

STAT_TIMESTAMP查询不到关于IND_T1_B的信息,怀疑是在对索引重建后对该索引没有进行统计信息的收集导致导出时无法导出索引统计信息。

在oracle9i和oracle10g上分别做了实验

查看metalink上对应文档(Doc ID: 331665.1)

该文档明确说明为什么在导出index统计信息的时候注意那些问题

Rebuild of indexes is causing problems in export of stats

'Alter index index_name rebuild' does not automatically recompute the
statistic for the index in 8.1.7. This feature was never implement until
10g release, where a hidden parameter _optimizer_compute_index_stats
is implemented in combination with the transaction mtakahar_paramtest
that fix this problem. With this parameter, the statistic is collected
when index is created or alter with or without compute statistics.

Solution

The fix here is to add 'COMPUTE STATISTICS' clause to the rebuild
statement like : 'alter index name rebuild compute statistics'
for the statistics to be collected. Rebuild index is like coalescing
the index, so the statistic's information is no longer valid.

alter index INDX1 rebuild online tablespace users compute statistics;

例如在10g上做下列实验，可以看到在编译索引的同时在收集索引统计信息

SQL> alter index ind_t1_b rebuild;

select i.index_name,i.global_stats,i.last_analyzed from dba_indexes i where i.index_name = 'IND_T1_B';

INDEX_NAME GLOBAL_STATS LAST_ANALYZED

IND_T1_B NO 2009-2-3 15:37:18

select o.object_name,o.last_ddl_time from dba_objects o where o.object_name = 'IND_T1_B';

OBJECT_NAME LAST_DDL_TIME

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

转载于:http://blog.itpub.net/104446/viewspace-545533/