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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
