Re: OBJECT DESING
Interesting, I hadn't previously heard that. Makes sense though, indexes work that way by default. And you can't change that behavior. Using a combination of PCTFREE and PCTUSED that adds up to 100 can lead to a lot of update activity on the free list. Or at least I seem to recall that, I haven't actually experienced it. At least I dont' remember experiencing it. Everyone was a newbie at some point. :) Jared On Saturday 20 April 2002 09:33, Don Granaman wrote: > For the radical approach - as espoused by Dave Ensor - PCTUSED=0 !!! (If > this doesn't generate some serious discussion, I'll be amazed!) > > This works quite well for tables where records are purged some set length > of time after insert. The net result is that blocks are filled, then left > alone until purged when they (the vast majority at least) are completely > emptied and put back on the free list. Blocks tend to be fully populated > and freelist activity is minimized. Be aware that in some older versions > of Oracle, there is a bug that requires PCTUSED = 1 (blocks only move back > to the free list if used space < PCTUSED instead of <= PCTUSED). I do not > know offhand what version fixed this. > > Dave Ensor's suggestion was to use this (almost) everywhere, but I'm not > really comfortable with it as a blanket policy. It seems that tables with > deletes that are "chronologically random" would end up with a LOT of very > sparsely populated blocks. > > The approach recommended below is to set it rather high to accomplish the > block density goal, but at the potential expense of high freelist activity. > Which approach is more appropriate depends on the nature of one's > insert/delete criteria. > > Also, PCTFREE can be set extremely low (1,5,?) for some kinds of objects - > static tables, indexes with strictly inorder data (CREATE_DATE, SEQUENCE#, > etc.). > > I agree with always setting PCTINCREASE =0 > > Don Granaman > [OraSaurus - Honk if you remember UFI !] > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, April 18, 2002 8:08 AM > > > Seema, > > > > PCTINCREASE - always 0. > > > > PCTFREE - for lookup (code) tables = 10 for others, I use 20. My theory > > is > > > that lookup tables have little or no updates, so this value should be > > small. > > > If you have a high-update tables (columns that are updated a 8lot* after > > the > > > record was created, you may want a higher value here. > > > > PCTUSED - for lookup (code) tables = 90, for others, I use 80. Again, > > for lookup tables with little or no updates, I fill the data blocks as > > high as possible. For other tables, I currently use 80 as a starting > > point. > > > > You will get many opinions here - read the docs to determine what you > > think > > > is best for you. > > > > Hope this helps. > > > > Tom Mercadante > > Oracle Certified Professional -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OBJECT DESING - Odd reason for bounce
Perhaps OT, but funny. I wasn't aware that the word "random" was offensive to anyone. I'll try to be more careful! --- The following email you sent was not delivered to the intended recipients as it had restricted contents in it! The restricted content present was "Random". Action taken: The email was Deleted. = The Mail came from: [EMAIL PROTECTED] The Mail recipient: [EMAIL PROTECTED] Subject of the Mail : Re: OBJECT DESING Message-ID: <[EMAIL PROTECTED]> = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OBJECT DESING
For the radical approach - as espoused by Dave Ensor - PCTUSED=0 !!! (If this doesn't generate some serious discussion, I'll be amazed!) This works quite well for tables where records are purged some set length of time after insert. The net result is that blocks are filled, then left alone until purged when they (the vast majority at least) are completely emptied and put back on the free list. Blocks tend to be fully populated and freelist activity is minimized. Be aware that in some older versions of Oracle, there is a bug that requires PCTUSED = 1 (blocks only move back to the free list if used space < PCTUSED instead of <= PCTUSED). I do not know offhand what version fixed this. Dave Ensor's suggestion was to use this (almost) everywhere, but I'm not really comfortable with it as a blanket policy. It seems that tables with deletes that are "chronologically random" would end up with a LOT of very sparsely populated blocks. The approach recommended below is to set it rather high to accomplish the block density goal, but at the potential expense of high freelist activity. Which approach is more appropriate depends on the nature of one's insert/delete criteria. Also, PCTFREE can be set extremely low (1,5,?) for some kinds of objects - static tables, indexes with strictly inorder data (CREATE_DATE, SEQUENCE#, etc.). I agree with always setting PCTINCREASE =0 Don Granaman [OraSaurus - Honk if you remember UFI !] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, April 18, 2002 8:08 AM > Seema, > > PCTINCREASE - always 0. > > PCTFREE - for lookup (code) tables = 10 for others, I use 20. My theory is > that lookup tables have little or no updates, so this value should be small. > If you have a high-update tables (columns that are updated a 8lot* after the > record was created, you may want a higher value here. > > PCTUSED - for lookup (code) tables = 90, for others, I use 80. Again, for > lookup tables with little or no updates, I fill the data blocks as high as > possible. For other tables, I currently use 80 as a starting point. > > You will get many opinions here - read the docs to determine what you think > is best for you. > > Hope this helps. > > Tom Mercadante > Oracle Certified Professional -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: OBJECT DESING
Seema, PCTINCREASE - always 0. PCTFREE - for lookup (code) tables = 10 for others, I use 20. My theory is that lookup tables have little or no updates, so this value should be small. If you have a high-update tables (columns that are updated a 8lot* after the record was created, you may want a higher value here. PCTUSED - for lookup (code) tables = 90, for others, I use 80. Again, for lookup tables with little or no updates, I fill the data blocks as high as possible. For other tables, I currently use 80 as a starting point. You will get many opinions here - read the docs to determine what you think is best for you. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, April 17, 2002 5:38 PM To: Multiple recipients of list ORACLE-L Hi WHat is best practices for PCTFREE,PCTUSED AND PCT_INCREASE value at object level? Thx -seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
OBJECT DESING
Hi WHat is best practices for PCTFREE,PCTUSED AND PCT_INCREASE value at object level? Thx -seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).