Xref: alice comp.databases.oracle.misc:52387 From: "Jane Schrock" Newsgroups: comp.databases.oracle.misc Subject: Very Large Table Extending after mass delete Date: Wed, 2 Feb 2000 15:44:45 -0600 Lines: 35 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 Message-ID: <38989849_4@news3.prserv.net> X-Trace: 2 Feb 2000 20:49:13 GMT, 198.133.22.71 Organization: Global Network Services - Remote Access Mail & News Services X-Complaints-To: abuse@prserv.net Path: alice!news-feed.fnsi.net!netnews.com!feeder.qis.net!news.maxwell.syr.edu!newsfeed.us.ibm.net!ibm.net!news3.prserv.net!198.133.22.71 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.