RE: Autoallocate vs Uniform extent performance

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