As people have noted, putting all of those
empty/unused tables that often come with SAP et al
into 64k extents could save some space.
The segments that I think are often neglected are the
ones that I describe with the very technical term:
'smallish'
Things in the 256k => 1M range, should conceivably
benefit from (I hate to say it), a *single* extent
because they fit into that zone where a full scan
should be able to be serviced with a single read.
Since (unless things have changed recently) Oracle
will never cross an extent boundary, a (say) 700k
segment in 64k extents would take more effort to scan
than that same segment in a 1m extent. The moment you
go above 1m (or SSTIOMAX) this would then become
somewhat moot, ie, back to the ol' any # of extents is
fine style of argument.
Of course this simple assessment ignores all the
little intricasies of buffering at the myriad of
levels that occurs nowadays on file system, disk
controllers, disks, caches blah blah etc etc etc.
Throw into the mix that these segments might quite
easily fit under the small table threshold and thus be
commonly in the oracle cache anyway, and you're back
to that famous of all mantras: "You should test on
this on your system to assess the benefit..."
happy easter everyone
connor
- Richard Foote <richard.foote_at_bigpond.com> wrote:
> Hi Steve,
>
> I'm assuming that each of your 1 extent tables are
> 64K in size, hence the
> (close to) 2G wasted space.
>
> Interestingly, my investigations into using a
> uniform size of 64K is showing
> that what I had initially considered to be potential
> issue, that being ASSM,
> is actually not as much of a problem as I suspected.
> As the segment
> increases in size, the L1 bitmap blocks
> progressively become more and more
> efficient (initially map 16 blocks, then 64 blocks,
> then 128 blocks etc.
> with 8K block size).
>
> Also the comparisons of FTS between non-assm and
> assm, few extents, heaps of
> extents (500,000) show that the differences are all
> quite marginal. The ASSM
> reads generally access a moderate number of extra
> blocks (overhead of the
> bitmaps) although having to reference them is having
> a negligible effect.
> Haven't tested on variable length rows yet nor fully
> tested effectiveness of
> deletes.
>
> In my environment(s), haven't yet seen any
> conclusive evidence that using a
> uniform size of 64K for all objects that I would
> want to share in the same
> tablespace has any significant detrimental effect
> (with or without ASSM).
>
> Still a way to go yet though ...
>
> Cheers
>
> Richard
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> Sent: Thursday, April 17, 2003 8:38 PM
>
>
> > That's kind of where I'm stuck...
> > I just checked our SAP-BW system (small 100 gig)
> and there are 2000+ table
> > partitions that use 1 extent.
> > If I set the uniform extent to
> > 1 meg that would be 2 gig of wasted space.
> > 5m would be 10 gig
> > 10m would be 20 gig
> >
> > multiply that times 5 BW systems
> > 10 gig of wasted space or
> > 50 gig of wasted space or
> > 100 gig of wasted space
> >
> > then there are 30,000 index partitions of the
> same, so...
> > 30 gig more or
> > 150 gig more or
> > 300 gig more
> >
> > It may not sound like much (2 drives or 6 for
> EMC), but with a crappy tape
> > system (longer backup times) and limited space...
> I ended up using
> > autoallocate for most of the objects and uniform
> tablespaces for objects >
> > 10 gig.
> >
> > I guess I'm stuck back in time when I only had a
> bunch of 2 gig drives and
> > space was at a premium.
> >
> > Steve
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, April 17, 2003 12:25 AM
> >
> >
> > > Jacques
> > > When we were first trying LMT, we got the
> extent size set wrong at
> 1M.
> > We
> > > installed an ERP that creates about 1,000
> tables. The SA wondered where
> > all
> > > his disk space went!
> > >
> > > Dennis Williams
> > > DBA, 40%OCP, 100% DBA
> > > Lifetouch, Inc.
> > > dwilliams_at_lifetouch.com
> > >
> > >
> > > -----Original Message-----
> > > Sent: Wednesday, April 16, 2003 8:54 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > I guess that's true too. Let's suppose I have
> for example 20 tables that
> > > would fit into a 64K extent (the smallest extent
> size in an autoallocate
> > > tablespace), but for each of those I use 1M.
> > > The wasted space would be 20 * (1024K - 64K) =
> approx. 19M
> > > Is that "a little" wasted space? Depends on your
> point of view.
> > >
> > > -----Original Message-----
> > >
> > > Then again, with Conner's suggestion of having
> just the one extent size
> of
> > > 1m, you only have the choice of one tablespace.
> A little wasted space
> for
> > > tiny tables and partitioned tables for the real
> biggies.
> > > --
> > > Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> > > --
> > > Author: Jacques Kilchoer
> > > INET: Jacques.Kilchoer_at_quest.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: DENNIS WILLIAMS
> > > INET: DWILLIAMS_at_LIFETOUCH.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_bigpond.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 Thu Apr 17 2003 - 11:42:58 CDT
