Re: Problem with table size

Ian Butt wrote:
<snip>
> Our DBA has provided the following information from dba_tables:
>
> OWNER PROD
> TABLE_NAME TEXT_TBL
> TABLESPACE_NAME PRODUCTS
> CLUSTER_NAME
> PCT_FREE 10
> PCT_USED 40
> INI_TRANS 1
> MAX_TRANS 255
> INITIAL_EXTENT 26357760
> NEXT_EXTENT 20807680
> MIN_EXTENTS 1
> MAX_EXTENTS 249
> PCT_INCREASE 5
> FREELISTS 1
> FREELIST_GROUPS 1
> BACKED_UP N
> NUM_ROWS 29030
> BLOCKS 113384
> EMPTY_BLOCKS 1075
> AVG_SPACE 3936
> CHAIN_CNT 135
> AVG_ROW_LEN 183
> DEGREE 1
> INSTANCES 1
> CACHE N
> TABLE_LOCK ENABLED
>
<snip>

Something to remember about pctfree and pctused and free lists, although this may not be the problem you are having. However, the symptoms are the same.

Oracle will attempt to insert a row into a block that is on the free list. If, in inserting the row, the free space in the block would fall below the pctfree parameter, the row isn't inserted. Here's the kicker: It now checks to see if the block is at least PCTUSED full. If it is, it takes the block off the free list. If it isn't, it leaves the block on the free list for more insert attempts. Normally this works fine, but the following scenario will cause a table to continually allocate new extents and not reuse old blocks, even if they are empty:

pctfree is 10 pctused is 85 4K block is 80% full. new row is 800 bytes (20%). row cannot be inserted because pctfree is 10. But the block isn't 85% used yet, so it stays on the free list. If every new row was 800, you would never remove new blocks from the free list, and Oracle only searches for new blocks for so long before allocating a new extent (for performance reasons). I believe this happened to us on a table similar to yours, and it continually grabbed new blocks and extents and would never reuse old blocks, even after they had been totally emptied. This typically happens when pctused and pctfree add up close to 100, but with your large variable row size it MIGHT be applicable here as well.

You might also consider inserting the row with a fixed, known size so you can control the number of rows inserted into each block, then update the long column to what it's supposed to be.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.