Re: LOCALLY MANAGED EXTENT PERFORMANCE

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_at_evdbt.com> To: <oracle-l_at_freelists.org> 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_at_Lowes.com
> 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

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