Re: RE: Autoallocate vs Uniform extent performance

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).