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).
