Ok, I'll go for something controversial and lets see
if it can be shot down...
On the assumption that every system's maximum physical
I/O is a common divisor or multiple of the Oracle
setting for SSTIOMAX (which is 1m in most relevant
versions), then every extent for every segment should
be 1m (in a locally managed tspace of course)
Arguments against possible rebuttals:
- "Wastes space"
Lets say you've got something like Apps or SAP with
20,000 segments, the majority of which are empty or
never used. That means less than 20G wasted - big
deal! 20G of disk is nothing - you could go buy a
cheapy disk and just plonk all the unused segments on
that. For backup, empty segments will compress just
fine on a tape or disk drive, totally unused segments
could be in read-only tspaces and not backed up at
all.
- "Extent map block pollution"
Any segment that is more than (say) 4G typically is a
candidate for partitioning anyway. So if set a
ceiling of 4G on a segment, that's 4000 extents which
means about 8 extent map blocks (assuming 8k blocks).
I'm not too fussed about 8 blocks
- "DBA_EXTENTS takes 4 days to query"
The most common query I've seen to this view was to
map file/block to a segment for I/O monitoring. Well,
we've got segment level monitoring in 9i now - who
needs it. DBA_SEGMENTS / DBA_FREE_SPACE etc provides
ample info for space usage.
OK - I'm tossing this bone to the dogs to see what
pops up.
Possible areas for discussion:
- an impact on parallel ops?
- ASSM with lots of extents?
Cheers
Connor
- 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"
> > > >
> > > >
> __________________________________________________
> > > > 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).
>
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).
Received on Fri Apr 04 2003 - 04:03:41 CST
