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

Yes, you can!

• Kirti

-----Original Message-----
Sent: Thursday, September 26, 2002 11:34 AM To: Multiple recipients of list ORACLE-L

Well, yes, that is one option. Can I issue an ALTER TABLE <table> MOVE to the same tablespace, I wonder.

Thanks
Hemant
At 08:23 AM 25-09-02 -0800, you wrote:
>Hemant,
>
>If I understand your question correctly, trying using the alter table move
>command and specify new values for pctfree and pctused. This should
>affect existing blocks. Make sure to rebuild any indicies.
>
>HTH,
>--
>Alan Davey
>adavey_at_competitrack.com
>
>
>
>On 9/25/2002 11:38 AM, Hemant K Chitale <hkchital_at_singnet.com.sg> wrote:
> >
> >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).
> >
> >
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Alan Davey
> INET: adavey_at_competitrack.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).
-- 
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).