OK.
I think we're essentially saying the same thing. But
in two different ways.
- Mladen Gogala <mgogala_at_adelphia.net> wrote:
> 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).
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).
Received on Tue Nov 04 2003 - 22:09:25 CST