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 > > > > > > >[EMAIL PROTECTED] >Sent by: [EMAIL PROTECTED] > 09/24/2002 09:08 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > >Are you sure about that John? > >On Tuesday 24 September 2002 04:28, [EMAIL PROTECTED] 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: [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). 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: [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).
