Xref: alice comp.databases.oracle.server:83128 From: "Jane Schrock" Newsgroups: comp.databases.oracle.server References: <3898a01d_3@news3.prserv.net> <949527429.8178.0.pluto.d4ee154e@news.demon.nl> Subject: Re: Very Large Table extending after mass deletes Date: Wed, 2 Feb 2000 16:45:08 -0600 Lines: 82 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: <3898a676_2@news3.prserv.net> X-Trace: 2 Feb 2000 21:49:42 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!hammer.uoregon.edu!logbridge.uoregon.edu!newsfeed.us.ibm.net!ibm.net!news3.prserv.net!198.133.22.71 Here is the information from my last analyze doing estimate statistics. I cannot do compute statistics as it takes too long. I do have chained rows, but not all rows are chained. My block size is 8192 so I can fit many rows in a block. %free %used ChnCt AvgRwLn AvgSpc NumRws EmpBlks 10 90 103750 759 5513 8948416 5055 Sybrand Bakker wrote in message news:949527429.8178.0.pluto.d4ee154e@news.demon.nl... > Start looking for chaining first (ie rows not fitting in one block, which is > VERY likely with a long raw). Simply ANALYZE the table first and look at > chain_cnt in user_tables. > If you want to dump them in a separate table use the list chained rows > clause of the ANALYZE command. > Most likely space is wasted in your table. The result of your ANALYZE will > also show you the average row_length. > As you might be aware, an inappropiate block size can't be changed without > rebuilding the database. > > Hth, > > -- > Sybrand Bakker, Oracle DBA > Jane Schrock wrote in message > news:3898a01d_3@news3.prserv.net... > > 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 > > > > > >