Re: PCTFREE and PCTUSED

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.

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

Many, many thanks!
maa

>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
>>-- Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>-- Author: Maryann Atkinson
>> INET: maryann_30_at_yahoo.com
>>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).
>--
>Mladen Gogala
>Oracle DBA
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Mladen Gogala
> INET: mgogala_at_adelphia.net

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Maryann Atkinson
  INET: maryann_30_at_yahoo.com

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