Ok, I'll go for something controversial and lets see if it can be shot down...
On the assumption that every system's maximum physical I/O is a common divisor or multiple of the Oracle setting for SSTIOMAX (which is 1m in most relevant versions), then every extent for every segment should be 1m (in a locally managed tspace of course) Arguments against possible rebuttals: a) "Wastes space" Lets say you've got something like Apps or SAP with 20,000 segments, the majority of which are empty or never used. That means less than 20G wasted - big deal! 20G of disk is nothing - you could go buy a cheapy disk and just plonk all the unused segments on that. For backup, empty segments will compress just fine on a tape or disk drive, totally unused segments could be in read-only tspaces and not backed up at all. b) "Extent map block pollution" Any segment that is more than (say) 4G typically is a candidate for partitioning anyway. So if set a ceiling of 4G on a segment, that's 4000 extents which means about 8 extent map blocks (assuming 8k blocks). I'm not too fussed about 8 blocks c) "DBA_EXTENTS takes 4 days to query" The most common query I've seen to this view was to map file/block to a segment for I/O monitoring. Well, we've got segment level monitoring in 9i now - who needs it. DBA_SEGMENTS / DBA_FREE_SPACE etc provides ample info for space usage. OK - I'm tossing this bone to the dogs to see what pops up. Possible areas for discussion: - an impact on parallel ops? - ASSM with lots of extents? Cheers Connor --- Richard Foote <[EMAIL PROTECTED]> wrote: > Just a general question to everyone (and one I've > asked a few times > before in different forums). > > If we're talking LMT, how many extents are too many > ? > > Assuming no quotas (which does introduce some known > issues) at what > point do you say that your standard uniform size of > 64K has generated > too many extents and that performance is noticeably > suffering to the > level where the inconvenience of a table reorg is > warranted ? > > When has anyone reached the point with an object in > a LMT whereby > performance has been an issue and by *only* reducing > the number of > extents, you've said "phew, that's better" ? > > If seen many suggestions on standard uniform sizes > that are somewhat > similar to those used by autoallocate, most of which > have a scale of > magnitude around the 100 mark. These always made > sense with DMT so are > we trying to implement outdated recommendations to > LMTs ? Does hitting > the 100 extent mark warrant such concern and need to > change our extent > size ? > > My little brain usually works best with smaller > numbers and I can gauge > the level of growth somewhat easier with smaller > number of extents but > is that a justification for being so picky with what > extent size an > object should have ? > > Some dba_ views will take longer to get me details > I'm after but is > that sufficient justification for being so picky > with extent sizes ? > > Curious in anyone's thoughts as I would hate to > think we have a myth a > happening ... > > Richard > > > ----- Original Message ----- > Date: Friday, April 4, 2003 9:18 am > > > I totally agree Gaja. > > > > I support a SAP BW system and they create tables > with a 100 of > > partitionsand only load 24 of them. With > autoallocate, most of > > them are small (64k) > > and space is not wasted. If they do decide to load > them up, I'm > > still safe > > because the extent size increase as the object > grows. > > > > I'm don't advocate of autoallocate for everything > because I can't > > determinethe next extent, but this is one place > where it's better > > than uniform. > > > > I also have some uniform LMTs for larger tables > that I migrate to when > > tables get too big. > > > > Steve > > > > ----- Original Message ----- > > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > > Sent: Thursday, April 03, 2003 11:33 AM > > > > > > > Totally agree with Connor. Just to add a comment > to > > > his note. > > > > > > A usage model recommended for UNIFORM vs. > AUTOALLOCATE > > > follows: > > > > > > If you know the data volume and growth of your > > > segments and they are predictable, then use > UNIFORM. > > > > > > If you are completely in the dark with: > > > > > > 1) How much data is going to be persisted in the > > > segments? > > > 2) What growth patterns the segments are going > to > > > exhibit? > > > > > > Then use AUTOALLOCATE. > > > > > > Of course, if you do change your mind, after the > fact, > > > you can use the MOVE command to the tablespace > of > > > choice with the extent allocation of your > choice. > > > > > > Cheers, > > > > > > Gaja > > > > > > --- Connor McDonald <[EMAIL PROTECTED]> wrote: > > > > I don't believe that was the case. auto and > uniform > > > > in all of the (admittedly rudimentary and > > > > subjective) > > > > tests I've done appear the same in terms of > > > > performance. > > > > > > > > I prefer uniform purely for the reasons of: > > > > > > > > - more thorough elimination of fragmentation > > > > - predictability of next extent sizes > > > > > > > > hth > > > > connor > > > > > > > > --- [EMAIL PROTECTED] wrote: > Hi all > > > > > > > > > > Some time ago there was a discussion about > the use > > > > > of the different extent > > > > > management types and that if my memory > serves me > > > > > that there was a > > > > > perception that Auto allocate extents had > some > > > > > performance issues against > > > > > Uniform extents. > > > > > > > > > > Was this correct and can it be backed up > with some > > > > > definitive testing, has > > > > > someone done a whitepaper??? > > > > > > > > > > Cheers > > > > > > > > > > > > > > > -- > > > > > > ================================================= > > > > > Peter McLarty E-mail: > > > > > [EMAIL PROTECTED] > > > > > Technical Consultant WWW: > > > > > http://www.mincom.com > > > > > APAC Technical Services Phone: +61 (0)7 > 3303 > > > > > 3461 > > > > > Brisbane, Australia Mobile: +61 > (0)402 094 > > > > > 238 > > > > > Facsimile: +61 > (0)7 > > > > 3303 > > > > > 3048 > > > > > > ================================================= > > > > > A great pleasure in life is doing what > people say > > > > > you cannot do. > > > > > > > > > > - Walter Bagehot (1826-1877 British > Economist) > > > > > > ================================================= > > > > > Mincom "The People, The Experience, The > Vision" > > > > > > > > > > > ================================================= > > > > > > > > > > This transmission is for the intended > addressee > > > > only > > > > > and is confidential > > > > > information. If you have received this > > > > transmission > > > > > in error, please > > > > > delete it and notify the sender. The > contents of > > > > > this e-mail are the > > > > > opinion of the writer only and are not > endorsed by > > > > > the Mincom Group of > > > > > companies unless expressly stated otherwise. > > > > > > > > > > > > > > > -- > > > > > Please see the official ORACLE-L FAQ: > > > > > http://www.orafaq.net > > > > > -- > > > > > Author: > > > > > 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). > > > > > > > > > > > > > ===== > > > > Connor McDonald > > > > web: http://www.oracledba.co.uk > > > > web: http://www.oaktable.net > > > > email: [EMAIL PROTECTED] > > > > > > > > "GIVE a man a fish and he will eat for a day. > But > > > > TEACH him how to fish, and...he will sit in a > boat > > > > and drink beer all day" > > > > > > > > > __________________________________________________ > > > > Yahoo! Plus > > > > For a better Internet experience > > > > http://www.yahoo.co.uk/btoffer > > > > -- > > > > Please see the official ORACLE-L FAQ: > > > > http://www.orafaq.net > > > > -- > > > > Author: =?iso-8859-1?q?Connor=20McDonald?= > > > > 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!? > > > Yahoo! Tax Center - File online, calculators, > forms, and more > > > http://tax.yahoo.com > > > -- > > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > > -- > > > Author: Gaja Krishna Vaidyanatha > > > 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). > > > > > > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Steve Perry > > 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). > > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Richard Foote > 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). > ===== Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __________________________________________________ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
