Uh, oh. No wonder I confused you with what I wrote Mladen. The below should read if the PCTFREE value is 10, NOT 90.
See what happens when you compose emails when taking antihistamines. Don't worry. I'm not doing any storage tuning at the present moment ... :) Melanie --- Melanie Caffrey <[EMAIL PROTECTED]> 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). __________________________________ 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).
