Re: Very Large Table Extending after mass delete

following your bulk deletes, did you do anything to coalesce the freespace extents (ie. is pctincrease != 0, or alter tablespace TS coalesce) ?

In article <38989849_4_at_news3.prserv.net>,   "Jane Schrock" <jschrock_at_us.ibm.com> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 04 2000 - 10:13:17 CST