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 <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > Phone: +61-3-9203-7589 > Mobile: +61-417-371-348
begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;;;;;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
