RE: Tablespace management.

Steve,

        I'm not sure I'd call all of the functionality that has been added over the years worth it. Way too many of them have caused more trouble than their worth, like descending indexes. And given the drivel that I've seen from many a third party vendor in the past (PeopleSoft and their damned 16K extents) this can certainly get turned into another nightmare. As far as fragmentation is concerned, I've NOT had to do any in the last few years, mainly due to spending a lot of time & effort to get computing storage needs into an exact science around here. That has been due to disk storage space not being an invisible cost item, but instead a significant one that we're constantly battling with. Sure they've become cheaper, but when our buying GB's of the stuff, mirrored, from a reliable vendor those half MB's wasted begin to add up FAST. Therefore I still contend that everything inside a single tablespace does not need a uniform extent size. If "one size fits all" was absolutel! y !
true there would be a lot less problems in this world.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Friday, May 30, 2003 1:06 PM
To: Multiple recipients of list ORACLE-L

I think you're missing the point of the last message. What's wrong with multiple extents if the extent size is a multiple of a multiblock read? What's wrong with having two tablespaces? I'd definitely suggest reading "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation". (http://otn.oracle.com/deploy/availability/pdf/defrag.pdf) No one is suggesting *everything* should have a single extent size but everything in a tablespace should.

LMT is the future and dovetails nicely with a lot of the functionality we've seen added in recent releases. What good are online table/index rebuilds if the space reclaimed is far outweighed by the space wasted by the fragmentation left behind?

S-

On Fri, 30 May 2003, Goulet, Dick wrote:

> Richard,
>
> My troubles come mainly form PeopleSoft and some in-house created
> applications. I'll use the in-house applications as the example since
> their simpler.
>
> Our CIM system has tables that contain very few rows of data, like
> the identification information for each robot(CELLS). Now there are
> only 30 robots on the longest/most complex line we have (BTW: due to the
> duhvelopers of this application each line needs it's own instance on
> it's own server, don't ask why). Now this table NEVER grows beyond
> 512KB is size. But each robot can have up to 1024 component slots (512
> on each side) that need to be defined with what is in them (SLOTS).
> This table easily gets into a couple of MB but then sits there since we
> do tons of updates but no more inserts. If we're doing LMT's then to
> optimize the storage on this mess I either need 2 tablespace or else set
> the uniform extent size to 512K and allow the SLOTS table to have
> several extents.
>
> This example is one of the simpler ones, there are a lot more that
> get even more problematic, like those for our test data. If 10i has bad
> news on this front it may well become the "straw that breaks the camel's
> back" for Oracle around here. We're already toying around with DB2.
>
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
>
> -----Original Message-----
> Sent: Friday, May 30, 2003 11:30 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Dick,
>
> What do you consider to be "a large number of extents" in a LMT ? At what
> point do you consider performance and manageability to be such that you sigh
> "gee, I wish I had fewer extents" ? What do you consider to be the "ideal"
> number of extents for a segment in a DMT vs. LMT that makes DMT so desirable
> ?
>
> I'm really really curious.
>
> BTW, I think 10i has some bad news in store for you ...
>
> Cheers ;)
>
> Richard
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Friday, May 30, 2003 11:49 PM
>
>
> > Jared,
> >
> > It's rather simple. If you follow the rules of third normal form you have
> a table with a certain number of rows, a second with a certain number of
> rows for each row in the first table. Obviously the second table needs more
> space than the first. Now if you use Dictionary management you can set the
> storage parameters of each table individually. But if your using local
> management they both have the same extent sizes. This leads one to having
> the extent sizes smaller to accommodate the first table and large numbers of
> extents for the second table. True fragmentation, namely those small
> useless extents that land between larger used extents, is eliminated in
> local management but then I have not had those problems with dictionary
> management either, unless someone makes the case for moving a table but
> that's very rare.
> >
> > Dick Goulet
> > Senior Oracle DBA
> > Oracle Certified 8i DBA
> >
> > -----Original Message-----
> > Sent: Thursday, May 29, 2003 8:25 PM
> > To: ORACLE-L_at_fatcity.com
> > Cc: Goulet, Dick
> > Importance: High
> >
> >
> > Dick,
> >
> > I'm trying to follow your line of thought, but I think I missed the path.
> >
> > Objects may not have the same storage requirements, but what does that
> > matter?
> >
> > The only way I can make sense of what you say is if trying to have all
> > objects
> > occupy a single extent, and there's not much point in that.
> >
> > Jared
> >
> >
> >
> >
> >
> >
> > "Goulet, Dick" <DGoulet_at_vicr.com>
> > Sent by: root_at_fatcity.com
> > 05/29/2003 03:51 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > cc:
> > Subject: RE: Tablespace management.
> >
> >
> > Thomas,
> >
> > With the exception of temp and rollback tablespaces I
> > have not user locally managed tablespaces just because all objects must
> > have the same sized extents. I do not see most tables sharing an equal
> > need for storage and using dictionary management allows one to do that, at
> > a cost I'll admit, but one that is much easier to swallow.
> >
> > Dick Goulet
> > Senior Oracle DBA
> > Oracle Certified 8i DBA
> >
> > -----Original Message-----
> > Sent: Thursday, May 29, 2003 3:25 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as
> > the options for tablespace management. Does anyone have any bad
> > experiences with these? AUTOALLOCATE seems to come up with extents that
> > are much smaller than I want and MANUAL segment management requires the
> > use
> > of FREELISTs (and I know that there are problems with freelists freeing up
> > space correctly, especially in a parallel environment).
> >
> > I can't find any basis for making a decision between UNDO and ROLLBACK
> > SEGMENTS. Does anyone have any experience or recommendations about UNDO
> > usage?
> >
> > The database will be a materialize view replication of a transaction
> > master
> > that is being used for decision support and has a 15 minute update/refresh
> > cycle. Basically, people can run queries against the snapshot without
> > impacting the master.
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Thomas Day
> > INET: tday6_at_csc.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: Goulet, Dick
> > INET: DGoulet_at_vicr.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: Goulet, Dick
> > INET: DGoulet_at_vicr.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: Goulet, Dick
> INET: DGoulet_at_vicr.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 Rospo
  INET: srospo_at_watchmark.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: Goulet, Dick
  INET: DGoulet_at_vicr.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).