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 [EMAIL PROTECTED] On 9/25/2002 11:38 AM, Hemant K Chitale <[EMAIL PROTECTED]> 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 >> >> >> >> >> >> >>[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). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alan Davey 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).
