| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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:JayMiller@TDWaterhouse.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.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jacques Kilchoer INET: Jacques.Kilchoer_at_quest.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 - 18:58:47 CDT
 |
 |