Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: LOCALLY MANAGED EXTENT PERFORMANCE


From: Dogan, Ibrahim - Ibrahim <>
Date: Wed, 27 Apr 2005 13:29:50 -0400
Message-ID: <>

> Less doubtful and more likely, is that in rebuilding the=20
> table in question to another tablespace, several other=20
> important modifications to storage parameters were also made,=20
> such as increasing INITRANS, increasing FREELISTS or using=20
> ASSM, or any number of possible changes to indexes on the=20
> table, perhaps?

No way..all storage parameters remained same except the extent size. is INITTRANS or FREELISTS changed when you do export from autoallocate and import it into unfiorm sized?

Diagnosing perf bottleneck is not rocket science... You turn on sql trace and look into raw trace file or tkprof file it is right there.. TRUNCATE TABLE is taking 45-60 seconds..

This app had over 700 tables and almost 7-8 of them had over 30,000 extents... I didn't set up the uniformed LMT, I was called in to analyze the long running processes..I'd set the extent size to default, 1M but somebody apparently thought that would be waste of space :(

> Cases such as...?

You even repeat the questions as if I didn't answer them... I think I sent the test SQL that will give you some idea.. Go to my 2. or 3. email..

> Ah, but then you keep mentioning this 27G table and how=20
> autoallocate helped it. If there is ever situation that=20
> screams for uniform-sized extents, it would be that one. =20
> Certainly, you know quite well how much space that thing is=20
> consuming day-by-day as it grows. So why would you need to=20
> 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..

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

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

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

Yes but the paper was written with LMTs in is another clip from the paper:

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.=20

Ibrahim DOGAN
Sr. Sybase/Oracle DBA

Received on Wed Apr 27 2005 - 13:35:09 CDT

Original text of this message