一个index在几分钟内大小增加了6G，归档不多

tolywang

已经提交SR ,  SR号  3-2127227881 ， Index space expand to 17.5G suddenly .

Yong Huang

What you described is very much like what alter index allocate extent does. If no human has explicitly done that, there could be a bug that essentially does the same but then the new extents are not used.

We can't view your SR. So you have to tell us what Oracle says. Thanks for keeping us posted.

Your message #9 has "Unformatted Blocks = 3405". It should be 1137328. You're using 16k db_block_size.

Yong Huang

tolywang

原帖由 Yong Huang 于 2010-9-21 01:23 发表
What you described is very much like what alter index allocate extent does. If no human has explicitly done that, there could be a bug that essentially does the same but then the new extents are not used.

We can't view your SR. So you have to tell us what Oracle says. Thanks for keeping us posted.

Your message #9 has "Unformatted Blocks = 3405". It should be 1137328. You're using 16k db_block_size.

Yong Huang

  


haah, yes, i make a mistake on the script (copied from line #8 but forgot to modify some string).  
    dbms_output.put_line(' Unformatted Blocks = '||l_full_blocks||' and Bytes = '||l_unformatted_bytes);

Actually ,  we had encountered the similar isssue two times,  and we found so many "insert and delete" operations
(logminer analysis) at first time , an index space expanded very fast (80m-->3G) but count of table rows was small ,  
so we suspected that maybe mass "insert and delete" operation was the root cause , we also did not find "alter index "
statements in the logminer content table ,  because the growth was not too big ,  we didn't think it may be a bug .

Until the second similar issue happened again , we did not find "alter index" or mass "insert and delete" operations,  and
index size expanded from 80M  to 17.5G ,  we began to doubt it was a bug  .  

I will continue to update reply from Oracle support  .

[ 本帖最后由 tolywang 于 2010-9-21 09:12 编辑 ]

Yong Huang

> maybe mass "insert and delete" operation was the root cause

Will the index grow larger and larger if you keep doing insert and delete from your sqlplus session? Do it at a quiet time so it won't drag down the performance of the production database, or do it in test or dev. You probably don't even need a commit between insert and delete, but each new insert must be a different key.

Yong Huang