Re: RE: Autoallocate vs Uniform extent performance

I count that as 127 thousand extents dropped in 6seconds. The CREATE TABLE + EXTENTS took 49seconds.

No, I am not trying to draw any conclusions, just making an observation. I am comfortable with thousands of extents but wouldn't look at a million extents.

Hemant
At 12:23 AM 04-04-03 -0800, you wrote:
>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" <ORACLE-L_at_fatcity.com>
> > > 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 <hamcdc_at_yahoo.co.uk> 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
> > > > >
> > > > > --- Peter.McLarty_at_mincom.com 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:
> > > > > > Peter.Mclarty_at_mincom.com
> > > > > > 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: Peter.McLarty_at_mincom.com
> > > > > >
> > > > > > 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: ListGuru_at_fatcity.com (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: connor_mcdonald_at_yahoo.com
> > > > >
> > > > > "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: hamcdc_at_yahoo.co.uk
> > > > >
> > > > > 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: ListGuru_at_fatcity.com (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: oraperfman_at_yahoo.com
> > > >
> > > > 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: ListGuru_at_fatcity.com (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: sperry_at_sprynet.com
> > >
> > > 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: ListGuru_at_fatcity.com (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: richard.foote_at_telstra.com
> >
> > 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: ListGuru_at_fatcity.com (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: peter.sharman_at_oracle.com
> >
> > 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: ListGuru_at_fatcity.com (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: richard.foote_at_telstra.com
>
>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: ListGuru_at_fatcity.com (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).

Hemant K Chitale
My personal web site is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

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: ListGuru_at_fatcity.com (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).