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: 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: wkhedr_at_attbi.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:59:28 CST
