Oracle-L: RE: ORA-1653: unable to extend table

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Thu, 05 Dec 2002 10:13:53 -0800
Message-ID: <F001.00513712.20021205101353@fatcity.com>

Dan, I think you nailed it!

It will be interesting to see the # of blocks and # of rows in this table.

Kirti

-----Original Message-----
Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L

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: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.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 Thu Dec 05 2002 - 12:13:53 CST

RE: ORA-1653: unable to extend table - Why?