• From: "Dogan, Ibrahim - Ibrahim" <Ibrahim.Dogan@xxxxxxxxx>
  • To: <tim@xxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 27 Apr 2005 13:29:50 -0400

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

> 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

Other related posts: