Re: Locally Managed Tablespaces - Questions.

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