Re: Very Large Table extending after mass deletes

I couldn't help but notice that her Pct_free was 10 and her Pct_used was 90. This strikes me as a bit unusual, though I can't claim any great experience. All I've ever seen had some "space" between pctfree and pctused. What is the effect of having these two "areas" bumping each other like this?

• Ed Stevens

In article <949527429.8178.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> 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
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Feb 03 2000 - 11:59:17 CST