RE: Tablespace management.

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