I came across the param in my Internals handbook (I'd never heard of it before). It could be different for the release/platform. The concept is probably the same. (?)
-----Original Message----- Sent: Thursday, December 05, 2002 11:59 AM To: Multiple recipients of list ORACLE-L I think you may have meant _walk_insert_threshold which by default is set to 0 (not set). Waleed > 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: [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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan 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).
