Re: LOCALLY MANAGED EXTENT PERFORMANCE
- From: "Don Granaman" <granaman@xxxxxxx>
- To: <tim@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 24 Apr 2005 23:51:21 -0700
I agree. Uniform extent sizes are not inherently bad. If you don't know
how large something will be, put it in autoLMT - that is the main reason its
there. Small to moderately large segments are also good candidates. It
works well in tablespaces with many mixed-sized segments. For segments that
will be huge though, why not supersize from the start?
I need to perform a major data conversion soon that will result in several
50+ GB tables. They don't really need *any* 64k extents and will each be
dedicated tablespaces. This particular system is RAC, so I am going to
choke down the obligatory side order of automatic to get the ASSM entree.
However, I would prefer uniform extents on the side in this case. One table
> 300 GB on autoLMT/ASSM in this system has a largest extent of 64M, so why
not just start there and make them uniform?
The extent count difference (auto will be higher) doesn't bother me - it
will converge fast anyway on this scale (details left as an exercise for the
reader). The potential for unnecessary free space fragmentation during a
segment's awkward adolescence (where it doesn't know how big it wants to be
when it grows up) bothers me a bit more. Sure, you can tweak it some - by
making the smallest sizes by setting initial large enough for one thing.
For example, (doing this from memory - your mileage may vary) for 8k blocks:
initial>=2M starts with 1 MB extents and initial>1024M starts with 8 MB
extents. Initial>X(?) may start with 64M - or something larger than 8 MB,
but I couldn't get there with finite space (a few hundred GB) to test on.
But... Who really wants to micromanage autoLMTs anyway? Isn't that sort of
missing the point?
OraSaurus - and contrarian by nature...
----- Original Message -----
From: "Tim Gorman" <tim@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, April 24, 2005 12:44 PM
Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE
> Exactly why might a large number of extents be a bad thing? In other
words,
> are you sure you are attaching the proper level of importance to the
issue?
>
> To help figure out if this is true, can you describe exactly what
operations
> might be affected by the number of extents, and how? Queries?
> Inserts/updates/deletes? Truncates? Drops? Monitoring queries?
>
> And, are you certain that autoLMT resolves the problem of "too many
> extents"? Isn't there an upper limit on extent size even with autoLMT?
If
> so, then how is this different from intelligently sized uniform LMTs?
>
> My apologies for the Socratic questioning, but this thread contained too
> many assertions that need a little more examination...
>
> -Tim
>
>
> on 4/22/05 11:07 PM, Dogan, Ibrahim - Ibrahim at Ibrahim.Dogan@xxxxxxxxx
> wrote:
>
> >
> > Even with LMTs, you still wory about number of extents whenever you run
> > any command that performs extent allocation/deallocation (create
> > table/rebuild index/truncate table etc..)
> >
> > My point was that I saw many people going back to DMT because of very
> > same issue you're experiencing with LMTs.. When LMT with uniform extent
> > size is used, you need to babysit the segments to make sure they don't
> > go beyong couple thousand extents.. But you don't have this problem if
> > you use LMT with AUTO extent allocation. My biggest table is 27G in a
> > AUTO LMT and it has around 600 extents..
> >
> > I generally prefer AUTO LMT and reorg the tables after bulk deletes...
> >
> >
> > Thanks,
> >
> > Ibrahim DOGAN
> > Sr. Sybase/Oracle DBA
> > www.lowes.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
