Re: Very Large Table extending after mass deletes

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 <jschrock_at_us.ibm.com> wrote in message news:3898a01d_3_at_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
>
>
Received on Wed Feb 02 2000 - 15:35:00 CST