Jane, what is the conclusion of your question(s) ? thanks ted
In article <3898a676_2_at_news3.prserv.net>,
"Jane Schrock" <jschrock_at_us.ibm.com> wrote:
> 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
> > >
> > >
> >
> >
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Feb 03 2000 - 08:44:15 CST
