Re: Locally managed tablespaces - autoallocate vs. uniform
Date: Thu, 30 Jul 2009 20:44:43 -0600
Fragmentation of free space is *impossible* with uniform extent sizes. It is merely *rare* with automatic extent sizes, but definitely *achievable*.
With automatic extent sizes, the database has a small number of extent sizes to choose from, but as soon as you have more than 1 choice, fragmentation is possible. I have had very good (better than ever expected!) results with automatic extent sizes myself, but I *can* imagine how these *could* be used to fragment a tablespace.
I not certain how to do it, but this is probably a pretty good recipe for fragmentation: Create a bunch of small segments. Grow them to 100x or 1000x their initial size (allocating lots of extents of varying sizes). Drop. Repeat. Happily, few (sane) applications will follow a pattern like this.
For exactly this reason, I prefer to use uniform extent sizes whenever I can find one that fits reasonably. It is not always a viable option, though.
On Tue, Jul 28, 2009 at 12:52 PM, Schauss, R. Peter (IT Solutions) < peter.schauss_at_ngc.com> wrote:
> How did the product cause free space fragmentation?
> - Peter Schauss
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
> Sent: Tuesday, July 28, 2009 2:04 PM
> To: Oracle L
> Subject: RE: Locally managed tablespaces - autoallocate vs. uniform
> With one exception where the product actually managed to create a free
> space fragmentation condition the feature works well. For that one
> product we converted the tablespace to using uniform extents and have
> not had an issue since.
> -- Mark D Powell --
> Phone (313) 592-5148
-- Cheers, -- Mark Brinsmead Senior DBA, The Pythian Group http://www.pythian.com/blogs -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 30 2009 - 21:44:43 CDT