索引空间三倍于表大小

 

INDEX_HISTORY表空间空间占用很大，检查里面的segment使用情况

select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments where tablespace_name = 'INDEX_HISTORY' order by GB desc;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC IND_SUBSCRIBER_LASTCHADATE  INDEX 99.3359375
MISC IDX_SUBSMODE_ID  INDEX 13.625
MISC PK_SUBSOTHERINFO_MID  INDEX 12.4375

发现有三个索引占用较多的空间
select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments
where (owner, segment_name) in (select owner, table_name from dba_indexes where wner = 'MISC' and index_name = 'IND_SUBSCRIBER_LASTCHADATE')
 order by GB desc ;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC SUBSCRIBER SUBSCRIBER_P1 TABLE PARTITION 8.4033203125
MISC SUBSCRIBER SUBSCRIBER_P2 TABLE PARTITION 8.4033203125
MISC SUBSCRIBER SUBSCRIBER_P3 TABLE PARTITION 8.4013671875
MISC SUBSCRIBER SUBSCRIBER_P4 TABLE PARTITION 8.3974609375

select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments
where (owner, segment_name) in (select owner, table_name from dba_indexes where wner = 'MISC' and index_name = 'IDX_SUBSMODE_ID')
 order by GB desc ;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC SUBSMODE  TABLE 21.58984375

select owner, segment_name, partition_name, segment_type, (bytes)/1024/1024/1024 GB
from dba_segments
where (owner, segment_name) in (select owner, table_name from dba_indexes where wner = 'MISC' and index_name = 'PK_SUBSOTHERINFO_MID')
 order by GB desc ;

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE GB
MISC SUBSOTHERINFO  TABLE 9.6630859375

 

索引空间三倍于表大小，这东西可不是一般生产系统能见到的。

运行6年的系统，加上缺乏有效的维护才可以形成这一奇特现象，纯天然啊。

 

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

转载于:http://blog.itpub.net/13605188/viewspace-687242/