Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Very Large Table extending after mass deletes
I'm on Oracle 7.3.4.5. I have very large table that continues to extend
even though about two weeks ago half of the records were deleted from the
table. The allocated size of the table is 12.5G. However, I believe there
is only about 7G of data in the table. This is based on the row count,
apprx 9 million, and the average row length from estimating statistics,
around 850 bytes. This is a heavy insert table. It currently has only 1
process freelist. The table has a LONG RAW column. I have done 5 random
data block dumps and have seen a peak of 17 interested transactions. The
extent growth has been as follows:
12-13 - 247 01-20 - 248 01-24 - 251 01-27 - 258 02-02 - 259
Note that the mass delete was done in the time period of 01-20 thru 01-27, when we see the most rapid extension. I believe this may have been due to deletes and inserts being concurrent. However, the deletes were done in multiple long running transactions. Once one is committed, the free blocks from that transaction should be available. Another change that occured in the 01-20 to 01-27 timeframe is that I revamped the rollback segments. I resized and added more to improve concurrency. The pctfree is 10 and the pctused is 90 for this table.
Does anyone have any clues as to why the table must extend to find free space? Are there any stats I can look at to figure this out? In my dreams, I want to reorg this table and create at least 20 freelists, but I'm going to have to get creative because I cannot get 48 hours of downtime. For now, I just need to understand why this table is extending and not finding free space on the free list. When you delete half the rows of the table you gotta figure that a good portion of the blocks that were deleted from have enough space to fit some more rows. Any insight is greatly appreciated.
Jane Received on Wed Feb 02 2000 - 16:18:06 CST