Re: PCTFREE and PCTUSED

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Tue, 04 Nov 2003 18:39:25 -0800
Message-ID: <F001.005D594B.20031104183925@fatcity.com>

On 2003.11.04 19:54, Maryann Atkinson wrote:
> At 06:34 PM 11/4/2003, you wrote:

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

>
> So, if pctfree is 10%, oracle does its best to keep it full 90%. Fine.

No. That is not what I said. Oracle tries its best to keep it at least 10% 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.

>
> So, Oracle lets the block get emptier and eptier, but does it best to stop
> this downfall of emptiness(!) at 40%, right?
>

>> > 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?

>
> Yes, oh YES!!! :-)
>
>

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

>
> GOT IT! I see, its beginning to make sense...
>
> Let me see... If the block were to become 85% full(ie 15% empty),
> then if Oracle were to put it on the free list again, that would cause
> overhead because it would fill it pretty soon, then would have to move it
> off
> the free list, therefore NOT doing work for me but itself, etc... right?
>
> So, PCTFREE and PCTUSED are kind of like boundary values, or kind-of-like
> FREE SPACE IS BETWEEN PCTUSED and PCTFREE values, right?

That is correct. You've got it. May the force be with you.

>
> Many, many 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 - 20:39:25 CST