Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
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 [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
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 [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
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Yes, I think I need to test it for myself and see what I get. As for the busy table in a production environment [24x7, so there's no time to rebuild the table normally], I've put in a request for 30minutes downtime [I am allowed an occassional 15 to 30 minutes every two months] to rebuild the table. Hemant At 08:33 AM 25-09-02 -0800, you wrote: Hemant wrote 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 ? I would guess the following Hemant, After changing the PCTFREE/PCTUSED values I do not think existing blocks will be changed until sufficient rows are deleted so that a block will be available on the freelist. If as you say there is only 1 row to a block then that would need to be deleted so that the block became available on the freelist and the new values would apply once a new row was inserted. Moving on from there it seems logical to me (that may be where I am going wrong!!) that an existing empty block will have it's values changed and will remain on the freelist but with revised parameters. I cannot say for certain without performing some tests but that is my gut feel HTH John -- 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).
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Yes you can move within the same tablespace. I had to do this when one of the other developers created a table with the default pctfree. Updates were causing rows to chain, so I issued the alter table move command with a new pctfree. Rebuilt the indexes and analyzed the table again and so far everything is working great. -- Alan Davey [EMAIL PROTECTED] 212-604-0200 x106 On 9/26/2002 12:33 PM, Hemant K Chitale [EMAIL PROTECTED] wrote: 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 [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
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 [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
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
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
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Hemant wrote 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 ? I would guess the following Hemant, After changing the PCTFREE/PCTUSED values I do not think existing blocks will be changed until sufficient rows are deleted so that a block will be available on the freelist. If as you say there is only 1 row to a block then that would need to be deleted so that the block became available on the freelist and the new values would apply once a new row was inserted. Moving on from there it seems logical to me (that may be where I am going wrong!!) that an existing empty block will have it's values changed and will remain on the freelist but with revised parameters. I cannot say for certain without performing some tests but that is my gut feel HTH John -- 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).
Is the effect of modifying PCTFREE/PCTUSED immediate ?
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: hkchital 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).
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
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: hkchital 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).
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
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: Jared Still 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).
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).
Re: Is the effect of modifying PCTFREE/PCTUSED immediate ?
I'm not sure either as I am rereading a document by Craig Shallamaher where he is saying to change pctused and pctfree in order to reduce data block fragmentation. I have to test that. At my new job, the DBAs are doing massive export/import to reduce fragmentation... (with their dictionnary managed tablespace) --- Jared Still [EMAIL PROTECTED] a écrit : 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: Jared Still 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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).
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
John, Someone asked a question a month or so ago about changing PCTUSED and PCTFREE: When do the blocks go back on the free list, when the 'ALTER TABLE ... PCTFREE N' command was issued, or did the blocks go back on the free list when the next insert was issued. I don't remember what my conclusion was, and IIRC, it wasn't definite. But, testing shows that blocks do go back on the free list when PCTUSED is increased to a a value greater than the amount of data in the block. This was on 8.1.7 on Linux. It's in the archives if you care to look for it. Jared [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).
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Jared, So, that means that to remedy a case of data block fragmentation we just need to increase the pctused for the fragmented tables. Of course, things won't change as fast as an export/import but it's certainly less work to do. --- [EMAIL PROTECTED] a écrit : John, Someone asked a question a month or so ago about changing PCTUSED and PCTFREE: When do the blocks go back on the free list, when the 'ALTER TABLE ... PCTFREE N' command was issued, or did the blocks go back on the free list when the next insert was issued. I don't remember what my conclusion was, and IIRC, it wasn't definite. But, testing shows that blocks do go back on the free list when PCTUSED is increased to a a value greater than the amount of data in the block. This was on 8.1.7 on Linux. It's in the archives if you care to look for it. Jared [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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
John, You are right, I just find out note 1029850.6 on metalink : A block is relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTUSED. --- [EMAIL PROTECTED] a écrit : 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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).
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
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).
