I think you're right: There's exactly one row in each block and the rest of the space is wasted. No wonder the table is growing. So make it possible for Oracle to put more than one row into a block (or rather: to pack data more tightly into the blocks) either by changing the storage parameters or by changing the block size. I'm not sure fiddling with the _-parameter would help any.
Mogens
Fink, Dan wrote:
Vitals:
Average Row Length = 1895
Block Size = 4096
pct_free = 10%
Threshold to put block off freelist = 3686
pct_used = 75%
Threshold to put block on freelist = 3072
Average free space = 3895
Working with averages, there could be at most 2 rows per block. The Average
free space is also very close to the block size, which indicates to me that
the blocks on the free list are probably empty.
Will a transaction insert a row into a block when it knows that the insert
will push the block above the pct_free threshold? I can see logic on both
sides. Don't insert because an update is more likely to cause row migration.
Do insert because the space is wasted otherwise.
After deleting 2 million rows, the # of blocks on the freelist is slightly
over 2 million. Is this a coincidence? I'll take a guess and say that the
insert processes are probably trying to acquire 1 block per 2 rows. Add in
the other processes doing inserts, each one needs its own block if it is
reusing it.
I'm wondering if the insert transaction started walking the freelist, could
not find an open block (because they were being used by other transactions)
within a certain period (# of blocks checked or timeout) and decided to
simply allocate another extent in order to enable the transaction to
complete. In reviewing my notes/docs from the Internals Seminar (8i), there
is a threshold (_release_insert_threshold) that will cause a new extent to
be allocated even when there are blocks on the master free list. This seems
a very likely scenario, given the large row size in comparison to the block
size.
Dan Fink
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).