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
