Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Very Large Table extending after mass deletes

Re: Very Large Table extending after mass deletes

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Thu, 3 Feb 2000 21:54:00 +0100
Message-ID: <949611370.14132.0.pluto.d4ee154e@news.demon.nl>


The pctfree indicates 10 percent of each block should be left free for updates of existing rows.
The pctused indicates the block should go to the free list if less than 90 percent of it is used.
Depending on how many rows exactly fit into one block (AFAIK if a row doesn't fit initially that block will not be used), many blocks might be on the freelist. Evidently, she wants every block to be used to the maximum potential.
Chances are this will result in continuous spacemanagement.

In the past I experimented with changing these parameters. My conclusion showed you should be reasonably sure how many rows are fitting completely in one block, otherwise you are only making things worse, and you would be better of with the standard PCTUSED. Lowering PCTFREE however for a table that 'never' changes doesn't seem to be harmful though.

Regards,

--
Sybrand Bakker, Oracle DBA
Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com> wrote in message news:87cfl9$kt2$1_at_nnrp1.deja.com...
> 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 - 14:54:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US