| 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: PCTFREE and PCTUSED
OK, I used to teach DBA courses and that was one of my favorite topics so let
me give it a shot here:
1) If the free space percentage in the block falls below PCTFREE, the block is
taken off the free list. Heuristically speaking, we can say that oracle does
its best to keep the block PCTFREE free.
2) When the block is taken off the free list and records are deleted, the block is not returned to the free list until the percentage of used space doesn't fall below PCTUSED. Again, heuristically speaking, oracle tries to keep blocks at least PCTUSED used.
I believe that your question was about the need for two parameters, in other words, why do we need both of them, why don't we return block to free list after the percentage of free space grows above PCTFREE? The answer is that free list handling is overhead, which means that the database is working on its own structures and not working on the user data. It's easy to conceive a busy transaction table to which records are frequently added and from which they're frequently removed. Having only one parameter would significantly increase the amount of time spent in moving blocks to and from the free list, and significantly increase the overhead. You can test it by setting up a table with PCTFREE+PCTUSED=100. In other word, the answer to your question is that two parameters are needed to reduce the overhead of the free list maintenance.
Fortunately, if you are on Oracle v9.2 and above, you can avoid the whole thing by creating your tablespaces in such a way that the objects in them have free lists managed by oracle (SEGMENT SPACE MANAGEMENT AUTO clause).
On 2003.11.04 18:09, Maryann Atkinson wrote:
> Suppose I have the following settings which happen to be
> the defaults as well:
>
> PCTFREE 10
> PCTUSED 40
>
>
> I am trying to figure out what PCTUSED is really used for.
> My book is telling me that is used so that Oracle knows
> whether to keep a block in the "free-list".
>
> My point is this: If PCTFREE is 10%, that means the block can be
> up to 90% full, right?
>
> Well, if the block happens to be 60% full at the moment, then Oracle
> knows that this block is not full enough because 60 is less than 90,
> so it can keep it in the free list. I dont see what PCTUSED is needed,
> it kind of seems I can accomplish the same with just one parm,
> that being PCTFREE.
>
> But Oracle wouldnt have just put a parm there without any usage,
> so I guess there's something I dont see...
>
> Any ideas/examples? Any good reasoning anywhere?
>
> Thanks,
> maa
>
>--
>--
-- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: mgogala_at_adelphia.net 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: 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 Nov 04 2003 - 17:34:26 CST
 |
 |