rumor hath it (as I've never actually had an object hit that high a
number) that when you exceed 4K extents it's time to resize. This came
from one of the instructors in Oracle University, one who is well-known
to actually have more than a clue. He said this at the Data Internals
class, before 9i was released.
I have not seen his test results but.... I do know that tests done with
DMTs have shown that large numbers of extents (I believe Kevin Loney
tested with 60K extents, and I vaguely remember a conversation with
Cary where he said he had also tested large numbers) are a problem
during operations that empty a lot of extents (think large deletes)
because of thrashing on FET$ and UET$. Since an LMT doesn't access
those tables by design, I would think that that problem goes away.
- Richard Foote <richard.foote_at_telstra.com> 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" <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"
>
=== message truncated ===
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: Rachel Carmichael
INET: wisernet100_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).
Received on Fri Apr 04 2003 - 07:01:26 CST
