| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tablespace management.
Personally, I think the issue of tablespace fragmentation has always been
highly overrated.
I'll use one of our databases as an example. It's a 3rd party app, and
has had only a little
maintence on the extent sizes. When I catch one growing quickly, I will
increase the next_extent
size.
This was until recently an 8.0.4 database. I had considered reorging to
make the extents
more uniform, but only briefly. I'm going to try and migrate to LMT later
this year when it
gets upgraded to 9i.
( see numbers below )
Used_bytes is all space consumed by the schema.
Free bytes is all chunks of free space greater than 8m (1024 blocks) in size.
Frag_bytes is all chunks of space <= 8m, and may or may not get used.
Next_extent are all distinct values for NEXT_EXTENT on tables and indexes for the schema. Though there are next_extent sizes less than 8m, I'm not looking at which tablespace they are in and am just making a blanket assumption that chunks < 1024 blocks will not get used.
Even with this extent management free for all, the database has ~30m of unusable space. ~30m of unusable space out of 366g is 0.008% wasted space.
It's up to the individual DBA to determine if s/he wants to remove all
possibilty
of fragmentation to avoid wasted space.
I just can't see where it's really worth the effort.
As as I'm concerned, the advantage of LMT's is not to reduce
fragmentation,
cuz frankly, I don't care. The advantages are avoiding possible
contention
on the ST latch, ( and that was mostly eliminated with true temporary temp
tablespaces ), and eliminating the huge amount of recursive SQL that is
generated by truncating or dropping an object with many extents.
Jared
USED_BYTES
1 row selected.
FREE_BYTES
1 row selected.
FRAG_BYTES
29,548,544
NEXT
EXTENT
--------------
16,384
40,960
81,920
163,840
516,096
655,360
1,048,576
2,088,960
2,621,440
3,145,728
4,194,304
5,242,880
6,291,456
10,485,760
12,582,912
15,728,640
18,874,368
20,971,520
26,214,400
31,457,280
41,943,040
52,428,800
62,914,560
83,886,080
94,371,840
104,857,600
115,343,360
29 rows selected.
"Goulet, Dick" <DGoulet_at_vicr.com>
Sent by: root_at_fatcity.com
05/30/2003 10:39 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: 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 absolutely
!
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.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).Received on Fri May 30 2003 - 21:29:38 CDT
 |
 |