as has been pointed out to me privately (and it's really okay to
correct me publicly!), it is not delete that would release blocks but
truncate or drop.
Resolution: do not post before at least two cups of coffee.
My apologies to anyone I might have confused.
- Rachel Carmichael <wisernet100_at_yahoo.com> wrote:
> 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
> > > > > >
>
=== 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 - 09:28:38 CST
