RE: PCTUSED - when is block added to freelist?
So my understanding from this is that a) A table has a PCTUSED of 40 which is changed to 75 b) A block is 60% free c) This block will be added to the freelist if an update or delete affects it (it was not added when the PCTUSED was changed). d) So my next question (which I may have to try and test myself): Would a global update on the table that didn't change anything move the blocks that fit the new PCTUSED criteria to the freelist? For example: update big_table set column1=column1; This would touch every block, at the end of the update the block would be below the new PCTUSED, but no data actually changes. Would this make the block available for inserts? Hmm, this is getting interesting! Jay Miller -Original Message- Sent: Wednesday, September 04, 2002 12:33 PM To: Multiple recipients of list ORACLE-L See Note: 1029850.6 on MetaLink for more details but here is algorithm used for freelist A block is put on free list if the free space in the block is greater than the space reserved by PCTFREE. Blocks linked in a free list are available for future updates or inserts. A block is unlinked from a free list if the free space in the block is not enough to allow a new row insert, and if the percentage of the used space remains above PCTUSED. A block is relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTUSED. Each time a block is added to a free list, it is linked at the head of the chain. Rick Miller, Jay JayMiller@TDWater To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] house.com cc: Sent by: Subject: RE: PCTUSED - when is block added to freelist? [EMAIL PROTECTED] 09/04/2002 11:03 AM Please respond to ORACLE-L Yes, that's what I intended to ask :). Thanks, Jared. Jay -Original Message- Sent: Tuesday, September 03, 2002 9:18 PM To: Multiple recipients of list ORACLE-L If I understand Jay's question correctly, what he's asking is not how PCTUSED and PCTFREE work, but what action or actions trigger Oracle to put a block back on the freelist after changing PCTUSED to a higher value? Is that correct Jay? Jared On Tuesday 03 September 2002 15:38, Miller, Jay wrote: I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage
RE: PCTUSED - when is block added to freelist?
Yes, that's what I intended to ask :). Thanks, Jared. Jay -Original Message- Sent: Tuesday, September 03, 2002 9:18 PM To: Multiple recipients of list ORACLE-L If I understand Jay's question correctly, what he's asking is not how PCTUSED and PCTFREE work, but what action or actions trigger Oracle to put a block back on the freelist after changing PCTUSED to a higher value? Is that correct Jay? Jared On Tuesday 03 September 2002 15:38, Miller, Jay wrote: I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled. TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: PCTUSED - when is block added to freelist?
See Note: 1029850.6 on MetaLink for more details but here is algorithm used for freelist A block is put on free list if the free space in the block is greater than the space reserved by PCTFREE. Blocks linked in a free list are available for future updates or inserts. A block is unlinked from a free list if the free space in the block is not enough to allow a new row insert, and if the percentage of the used space remains above PCTUSED. A block is relinked to a free list if after DELETE or UPDATE operations, the percentage of the used space falls below PCTUSED. Each time a block is added to a free list, it is linked at the head of the chain. Rick Miller, Jay JayMiller@TDWater To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] house.com cc: Sent by: Subject: RE: PCTUSED - when is block added to freelist? [EMAIL PROTECTED] 09/04/2002 11:03 AM Please respond to ORACLE-L Yes, that's what I intended to ask
RE: PCTUSED - when is block added to freelist?
for r in 1..1029
09:34:50 4 loop
09:34:50 5 insert into fb(mydata)
09:34:50 6 values(rpad('',100,'X'));
09:34:50 7 end loop;
09:34:50 8 commit;
09:34:50 9 end;
09:34:50 10 /
PL/SQL procedure successfully completed.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL analyze table fb
compute statistics;
Table analyzed.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL select blocks,
empty_blocks
09:34:50 2 from user_tables
09:34:50 3 where table_name = 'FB'
09:34:50 4 /
BLOCKS EMPTY BLOCKS
---
70
1 row selected.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL alter table fb pctused
80 pctfree 20;
Table altered.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL insert into fb(mydata)
09:34:50 2 values(rpad('',100,'X'));
1 row created.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL commit;
Commit complete.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL analyze table fb
compute statistics;
Table analyzed.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL select blocks,
empty_blocks
09:34:50 2 from user_tables
09:34:50 3 where table_name = 'FB'
09:34:50 4 /
BLOCKS EMPTY BLOCKS
---
70
1 row selected.
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL
09:34:50 rsysdevdb.radisys.com - jkstill@dv01 SQL set echo off
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/04/2002 09:33 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: PCTUSED - when is block added to freelist?
See Note: 1029850.6 on MetaLink for more details but here is algorithm
used
for freelist
A block
is put on free list if
the
free space in the block is
greater
than the
space
reserved by PCTFREE.
Blocks
linked in a free list are
available for
future
updates or inserts.
A block
is unlinked from a free
list if
the free space in the
block
is not
enough
to allow a new row
insert,
and if the percentage of
the
used space
remains
above PCTUSED.
A block
is relinked to a free
list if
after DELETE or UPDATE
operations, the
percentage of the used space
falls
below PCTUSED.
Each
time a block is added to a
free
list, it is linked at the
head of
the
chain.
Rick
Miller, Jay
JayMiller@TDWater To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
house.com cc:
Sent by: Subject: RE: PCTUSED -
when is block added to freelist?
[EMAIL PROTECTED]
09/04/2002 11:03
AM
Please respond to
ORACLE-L
Yes, that's what I intended to ask :).
Thanks, Jared.
Jay
-Original Message-
Sent: Tuesday, September 03, 2002 9:18 PM
To: Multiple recipients of list ORACLE-L
If I understand Jay's question correctly, what he's asking is
not how PCTUSED and PCTFREE work, but what action or
actions trigger Oracle to put a block back on the freelist
RE: PCTUSED - when is block added to freelist?
1- Delete means freeing space. 2- When the used space in the blocks falls below PCTUSED, the block needs to go back to the free list. 3- This requires extra processing cost. 4- So lowering PCTUSED will lower the frequency of triggering this procedure. Waleed -Original Message- Sent: Tuesday, September 03, 2002 6:39 PM To: Multiple recipients of list ORACLE-L I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled. TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: PCTUSED - when is block added to freelist?
Title: RE: PCTUSED - when is block added to freelist? The way I understand it: If you have a low pctused, then you have less blocks being moved to the freelist (because it's less probably that a block will be moved to the freelist): so reduced processing costs during update (if a row length is diminished by the update) or a delete. If you have a high pctused, then there's more of a chance of getting a chained (migrated) row, which would negatively affect the peformance of an insert or an update (if row length is increased by the update). My question is: why are the defaults pctfree 10 and pctused 40? Wouldn't it make more sense (less wasted space) for the defaults to be two numbers that add up to 90 of 95? e.g. pctfree 20 and pctused 70 -Original Message- From: Miller, Jay [mailto:[EMAIL PROTECTED]] I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled.
Re: PCTUSED - when is block added to freelist?
If I understand Jay's question correctly, what he's asking is not how PCTUSED and PCTFREE work, but what action or actions trigger Oracle to put a block back on the freelist after changing PCTUSED to a higher value? Is that correct Jay? Jared On Tuesday 03 September 2002 15:38, Miller, Jay wrote: I have one huge table (takes up about 30% of the total database storage) which has a monthly batch deletion of old data. I had PCTFREE and PCTUSED set to the defaults of 10 and 40 respectively. I occurred to me that I could probably free up a lot more space by increasing the PCTUSED so that more blocks would be available to be written to (since getting more storage for the server is a bureaucratic nightmare here). So my question is, if I just raise the PCTUSED from 40 to, say, 75 would all blocks that fall into the 40-75 range become available for inserts? Or is it only after their next update or delete? Different sections of the docs seem to imply different things. The docs say: A lower PCTUSED reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage. This seems to imply that it won't be moved to the freelist until a delete or update is done that affects that block. But they also say: A higher PCTUSED increases processing cost during INSERTs and UPDATEs. This seems to imply that when it's looking to do the insert it might find that it can insert to a block. Anyway, why would a lower PCTUSED reduce processing costs during a DELETE but a higher PCTUSED wouldn't increase processing costs during a DELETE. That makes no sense. I'm befuddled. TIA, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
