PCTUSED - when is block added to freelist?

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Tue, 03 Sep 2002 14:38:40 -0800
Message-ID: <F001.004C6BA1.20020903143840@fatcity.com>

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: JayMiller_at_TDWaterhouse.com

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: ListGuru_at_fatcity.com (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). Received on Tue Sep 03 2002 - 17:38:40 CDT