No, I don't mean that. If the free space percentage goes above PCTFREE, that
means that there is more then PCTFREE % free space in the block. Block is
thus eligible for free list. If the percentage of free space falls below
PCTFREE, that means that there is less then PCTFREE % of free space. Block is
taken off the free list.
On 2003.11.04 20:19, Melanie Caffrey wrote:
> Ummm ... Mladen?
>
> MG> 1) If the free space percentage in the block falls
> MG> below PCTFREE, , the block is
> MG> taken off the free list.
>
> Don't you mean if the free space percentage goes
> *above* the value in PCTFREE?
>
> Unless I'm reading you incorrectly, don't you mean
> that, say, if the PCTFREE value is 90, and the block
> becomes 91% full, then the block is taken off the free
> list?
>
> (Maybe this is what you mean and I'm not reading you
> correctly .... )
>
> --- Mladen Gogala <mgogala_at_adelphia.net> wrote:
> > 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
> > >
> > >--
> > > 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
> >
> > 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).
>
>
> __________________________________
> Do you Yahoo!?
> Protect your identity with Yahoo! Mail AddressGuard
> http://antispam.yahoo.com/whatsnewfree
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Melanie Caffrey
> INET: melanie_caffrey_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
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:34:24 CST
