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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Table not reusing deleted space

Re: Table not reusing deleted space

From: Daniel Fink <Daniel.Fink_at_sun.com>
Date: Fri, 03 Oct 2003 07:19:26 -0800
Message-ID: <F001.005D1F55.20031003071926@fatcity.com>


Leng,

I am going to have to defer to others on the freelist algorithm. I have not done any examination/testing and don't want to make assertions that would cause more problems.

Daniel

"Kaing, Leng" wrote:

> Hi Daniel,
>
> Thank you so much for solving part of the mystery. This is the best explanation I've got yet. I think you may be right. Our avg. row len. is more than 5K and our database is 8k block. I can't believe that the 1G is spread EVERY single 4G block. So how does Oracle determine which 5 blocks to check first before giving up, or is it just a random sample and it happens be those half filled?
>
> We may have to build a special 16K block database just for this table in the end. Now I see why we need 9I :-} But upgrading is probably not an option at the moment.
>
> Thank you!!
>
> Leng.
>
> -------------------
>
> From: Daniel Fink <Daniel.Fink_at_Sun.COM>
> Date: Wed, 01 Oct 2003 07:53:14 -0600
> Subject: Re: Table not reusing deleted space
>
> This is a multi-part message in MIME format.
> --------------D366F924536517C4833C5849
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Leng,
>
> I recall a similar scenario some months ago. It had to do with the average row size (quite large) and the block size. The average row size was just under 1/2 of the block size, so the chances of a new row finding a spot in an existing block was slim. Add in that there is a limit (5 I think) of blocks on the freelist that a transaction will attempt to allocate space in before it says "I can't find a block with enough free space so I'm going to
> allocate a new extent.". I think we looked at dba_tables.avg_row_len and dba_tables.avg_space_freelist_blocks. (?) A quick calculation (1048576k /200000) indicates that your average row length is over 5k. If you have 8k blocks, this means an average of 1 row per block (perhaps less depending on the variance in row length).
>
> Daniel Fink
>
> "Kaing, Leng" wrote:
>
> > Hello everyone,
> >
> > Env: 8.1.7.4, SunOs 5.8 64 Bit
> >
> > We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows.
> >
> > We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space.
> >
> > In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem?
> >
> > Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot.
> >
> > TIA,
> >
> > Leng.
>
> ----------------------------------------------------------
> Leng Kaing
> Email: leng.kaing_at_team.telstra.com
> Phone: +61-3-9203-7589
> Mobile: +61-417-371-348


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  INET: Daniel.Fink_at_Sun.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Oct 03 2003 - 10:19:26 CDT

Original text of this message

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