Hi Pete, Stop using my favourite answer :)
I'm not suggesting this is particularly scientific but here's a quick test on dropping a moderate number of extents (9.2 on XP): SQL> create tablespace biggish 2 datafile 'c:\bowie\biggish01.dbf' size 2000M 3 uniform size 16K; Tablespace created. SQL> set timing on SQL> create table lots_of_extents (x number) 2 storage (initial 1990M) 3 tablespace biggish; Table created. Elapsed: 00:00:49.06 SQL> select count(*) from user_extents where segment_name = 'LOTS_OF_EXTENTS'; COUNT(*) ---------- 127423 Elapsed: 00:00:04.01 SQL> drop table lots_of_extents; Table dropped. Elapsed: 00:00:06.08 Based on the above, I could drop a table with 1,000,000 extents in under 1 minute. I need a better reason for keeping this table to the 100 extent mark ... Cheers Richard ----- Original Message ----- Date: Friday, April 4, 2003 1:53 pm > BD > > Did you expect any other answer than "it depends"? :) > > Seriously, we've seen a few thousand extents without any problems > in later > releases. We've also heard of customers not noticing things had gone > horribly adrift from what they thought and end up with hundreds of > thousandsof extents. No empirical evidence of performance > problems with said > hundreds of thousands of extents, but boy that drop command can be an > absolute mother if you try to fix it! :) > > Pete > "Controlling developers is like herding cats." > Kevin Loney, Oracle DBA Handbook > "Oh no, it's not. It's much harder than that!" > Bruce Pihlamae, long-term Oracle DBA > > > > -----Original Message----- > Sent: Thursday, April 03, 2003 5:28 PM > To: Multiple recipients of list ORACLE-L > > > 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). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Pete Sharman > 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).
