Re: Locally Managed Tablespaces - Questions.

Thanks Richard,

After years of the few vs. 1000's of extents brainwashing it's hard to change old habits :)
I personally never worry about 100's, but 1000's made me feel like I was doing my part to improve the performance by reorging it (when time permitted).
The problem I have now is I'm swamped with database stuff all day and have just recently been "elected" to be the SAN administrator (they laid off my old mgr. without realizing that he was maintaining the our EMC system for the last 4+ years). That leaves even less time available to do the fun stuff of testing "best practices" to see if they stand up. I'm glad people like yourself and others on the list are out there testing this stuff and discussing it.

I'll try testing uniform extents of 64K on our sandbox and see how it goes. If all goes well, I'll have to convince my boss who is a firm believer in 1 big extent reorgs. I'll tell him we can set it up like sybase and have a data device and log device with 64K extents. He's an old sybase DBA who still talks about the good ol' days of sybase :)

Thanks again,
Steve

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, April 17, 2003 10:36 AM

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

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