Re: Locally managed tablespaces - autoallocate vs. uniform

From: Mark Brinsmead <pythianbrinsmead_at_gmail.com>
Date: Thu, 30 Jul 2009 20:44:43 -0600
Message-ID: <cf3341710907301944i467983a8md079fdbe05996609_at_mail.gmail.com>

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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Cheers,
-- Mark Brinsmead
  Senior DBA,
  The Pythian Group
  http://www.pythian.com/blogs

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 30 2009 - 21:44:43 CDT