RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?

Let me clarify my original question.

I do not expect the FreeList for a table to get updated instantaneously after I change the PCTFREE/PCTUSED.
What I meant by "is the effect ... immediate" is that do the new values come into play immediately -- even for existing blocks.

Suppose I have a table where PCTFREE was high (40) and PCTUSED high (50 or 60). Thus, ignoring deletes [and overheads], there would have been about 60% [100-40] usage in the block -- these could mean a large number of rows. Now, I want to reduce the number of rows in a block -- the particular table is a "hot" table where some blocks become "very hot" spots [extremely high rate of updates to existing rows, updates which do not increase the size of existing rows]. My "cache buffer chains latch" contention is high. I further introduce the possibility of deletes [e.g. a purge job running daily]. Because PCTUSED is high, and not very many rows in a block get deleted at each purge, the block is unlikely to come into the FreeList early. It would be a number of days before enough rows are deleted from the block.

Therefore, to reduce the contention for the "hot blocks", I decide to have only 1 row in each block. Normally, with a *NEW* table, PCTFRE 99 and PCTUSED 1 would ensure that I have only 1 row per block. But if I have a large number of blocks in a few extents created when PCTFREE was 40 and PCTUSED 50 or 60. When would these existing blocks start behaving as if they were created with PCTFREE 99 and PCTUSED 1 ?
How about new blocks ? Would new [empty] blocks in existing extents immediately behave such that they allow only one row per block ? Or would only new blocks in new extents take the PCTFREE 99 and PCTUSED 1 attributes ?

Hemant

At 03:53 PM 24-09-02 -0800, you wrote:
>I replied too soon earlier, I think.
>
>Yes, what you state is correct.
>
>Jraed
>
>
>
>
>
>
>John.Hallas_at_vodafone.co.uk
>Sent by: root_at_fatcity.com
> 09/24/2002 09:08 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: Is the effect of modifying PCTFREE/PCTUSED
> immediate ?
>
>
>Well I was sure about it until you had the temerity to question me :)
>I think we agree on extents sizes not being changed after the event so it
>is
>now a discussion on whether changes to a pctfree/pctused are
>retrospective.
>
>I contend that if a table is fully loaded upto its pctfree/pctused limits
>and there are no available blocks on the freelist then by changing the
>pctfree/pctused values no additional blocks will suddenly appear on the
>freelist.
>I do agree however that if a block is amended by having a row deleted or a
>row updated then the new values come into play and the blockcould then be
>available on the freelist.
>
>I think I am correct on this but as with anything I am always ready to be
>proved wrong - it has happened before and wil lhappen may times in the
>future
>
>John
>
>
>-----Original Message-----
>Sent: 24 September 2002 15:47
>To: ORACLE-L_at_fatcity.com; John.Hallas_at_vodafone.co.uk
>
>
>
>Are you sure about that John?
>
>On Tuesday 24 September 2002 04:28, John.Hallas_at_vodafone.co.uk wrote:
> > No, it is not retrospective.
> > You are setting parameters to be used when the next extent is created.
> > A better example is when setting next extent size to be different than
>the
> > existing extent size (dictionary managed tablespaces only).
> > It does not alter all the existing extents it only works on the next one
> > that is created.
> >
> > HTH
> >
> > John
> >
> > -----Original Message-----
> > Sent: 24 September 2002 10:58
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > Is the effect of modifying PCTFREE/PCTUSED immediate ?
> >
> >
> > If I do an "ALTER TABLE <tablename> PCTFREE 99 PCTUSED1",
> > does this take effect immediately, even for existing blocks.
> > [If so, existing blocks would not get new rows inserted].
> > Or is it effective only in new Extents ? In that case,
> > existing blocks in existing Extents still use the old
> > PCTFREE/PCTUSED parameters and keep re-entering the
> > FreeList.
> >
> > Hemant K Chitale
> > http://hkchital.tripod.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: John.Hallas_at_vodafone.co.uk
>
>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: Jared.Still_at_radisys.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).

Hemant K Chitale
My web site page is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

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).