Re: Very Large Table extending after mass deletes

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 <postmaster_at_sybrandb.demon.nl> wrote in message news:949527429.8178.0.pluto.d4ee154e_at_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 <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 - 16:45:08 CST