Re: LOCALLY MANAGED EXTENT PERFORMANCE

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 28 Apr 2005 09:26:58 +0100
Message-ID: <7765c89705042801262f122621@mail.gmail.com>

On 4/27/05, Dogan, Ibrahim - Ibrahim <Ibrahim.Dogan_at_lowes.com> wrote:

> > Ah, but then you keep mentioning this 27G table and how=3D20
> > autoallocate helped it. If there is ever situation that=3D20
> > screams for uniform-sized extents, it would be that one. =3D20
> > Certainly, you know quite well how much space that thing is=3D20
> > consuming day-by-day as it grows. So why would you need to=3D20
> > cede control over extent sizing to autoallocate?
> You're missing the point. It is not the only table I have.. This is 3
> party app and there hundreds of tables with different space
> requirements. And bunch of them are very volatile.. They grow and get
> truncated in middle of day..

If as you seem to suggest this is a repeated thing, and its the extent management that takes the time (seems a little unlikely to me, I'd be looking at possibly tablespace quotas) then it would seem to me that the 'correct' fix isn't a change from one extent management policy to another, but to use the REUSE STORAGE clause for the TRUNCATE TABLE statement and not do the extent deallocation at all.

> And you make it sound like autoallocate creates millions of extents with
> different sizes. This is wrong.. Actually it is pretty uniform in
> itself: It has only 4 or 5 extent sizes. 64K, 1M, 8M, 64M ... Some DBAs
> who use only uniformed size LMTs generally ignore/don't know this fact.
> If this many extent sizes confuse some people and they think it will
> create fragmentation, I have nothing else to say..

http://www.niall.litchfield.dial.pipex.com/scripts/tests/auto_alloc_frag.sq= l=20

I think that it will cause fragmentation, because I can show that it does.= =20

As for only 4 extent size, I'll agree that those are the most common by far, they are however subject to change without notice and they aren't all you will get

http://www.freelists.org/archives/oracle-l/01-2005/msg00782.html=20

Now there is a perfectly reasonable argument that goes fragmentation is a tiny problem these days with cheap disk and large tablespaces, and to be honest there is a lot to that argument. However the fact remains that you can fragment an autoalloc tablespace and you cannot predict what extent size you will get, the same does not hold for uniform allocation.

=20
> And if uniform is a cure to all cases, why would hundreds of Oracle
> engineers come up with an alternative? A marketing gimmick ? I don't
> think so..

>=20

> > LMT was not introduced until version 8.1. So, the advice in the paper
> is for DMT only, which is all that existed at the time it was > written.

>=20

> Yes but the paper was written with LMTs in mind..here is another clip
> from the paper:

>=20


> 2.1     Eliminating Extent Fragmentation Using SAFE

> Oracle provides administrators extreme flexibility in sizing the extents
> that are allocated to segments. This full specification flexibility is
> almost never needed and in fact, should be avoided in most cases. By
> following a simple set of administrative rules, fragmentation at the
> extent level can be entirely eliminated. We will use the term SAFE to
> refer to these rules. SAFE stands for Simple Algorithm for
> Fragmentation Elimination. SAFE consists of a set of rules, many of
> which have been internalized and implemented as part of the LOCALLY
> MANAGED TABLESPACE feature being introduced in Oracle8i. ORACLE8i users
> should refer to the presentation on that subject for an updated version
> of SAFE.=3D20

>=20

> Thanks,
> =3D20
> Ibrahim DOGAN
> Sr. Sybase/Oracle DBA
> www.lowes.com
> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

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

Received on Thu Apr 28 2005 - 04:32:00 CDT