| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-1653: unable to extend table - Why?
How is the insert being used?
Is it one row per insert?
Is the column values hardcoded or passed in variables?
What is the maximum row length?
-----Original Message-----
Sent: Friday, December 06, 2002 11:14 AM
To: Multiple recipients of list ORACLE-L
Very interesting!
I was out sick yesterday so I'm just getting caught up on the email today.
There are currently 898334 rows and 2654300 blocks in the table (the number of rows will grow over the next 2 months before the next big delete which is done quarterly). But this does seem to imply that it is only allocating one row/block.
Might adding more freelists enable it to make more use of the available blocks by avoiding timeouts while walking the freelist?
I've also been investigating the application and have come across some annoying features that unfortunately the developer assures me can't change for various reasons. One is that the loading process is doing frequent commits (it commits on the account level which will be usually be one insert but unlikely to be more than 6). Also it is doing a Select from Dual for every insert. Don't know if this is relevant to anything other than performance though.
Matt: You suggest "changing the storage parameters or by changing the block size". Changing the block size isn't really an option just now (though once I upgrade to 9i I'll seriously consider changing it for just this tablespace). What storage parameter changes did you have in mind?
Waleed: The table is not partitioned. Extent size is 25Meg.
Jay Miller
x48355
-----Original Message-----
Sent: Thursday, December 05, 2002 1:14 PM
To: Multiple recipients of list ORACLE-L
Dan, I think you nailed it!
It will be interesting to see the # of blocks and # of rows in this table.
-----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).
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).
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 Fri Dec 06 2002 - 10:51:13 CST
 |
 |