Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Looking for a good way to convert from dictionnary manged TBS to Locally managed ones

Re: Looking for a good way to convert from dictionnary manged TBS to Locally managed ones

From: Howard J. Rogers <>
Date: Fri, 19 Mar 2004 15:36:16 +1100
Message-ID: <405a78c3$0$8355$>

"Daniel Roy" <> wrote in message
> > It has been argued and demonstrated here that UNIFORM is of little use
> > and AUTOALLOCATE is the way to go, as this will make sure all extents
> > are properly sized.
> >
> Can I ask where this has been "argued and demonstrated"? I've been
> using uniform, and I'm quite happy with the result. I also group the
> objects in tablespaces depending on their expected size (extents 8K,
> 16K, 32K, ...).
> Daniel

It's been discussed here quite recently, with Richard Foote posting an interesting test demonstrating that autoallocate is extremely difficult to fragment, and is extremely smart about the way it allocates space to objects. I'm sure you could hunt for it on Google. It would be within the last two months.

Your suggested pot pourri of extent sizes, imho, completely misses the point of LMTs. What was the one huge advantage LMTs brought us? Performance?? Nah... there's sod-all performance difference between LMTs and DMTs, unless your data dictionary is being hammered to death and FET$ and UET$ become a point of contention.

No, the real benefit of LMTs was that we could finally stop worrying about the number of extents a segment acquired. And the corollary of that is we could finally stop micro-managing extent sizes to try and prevent segments acquiring a bazillion extents.

Point being, LMTs deserve a 'broad brush' approach to extent sizes. Around about 5 extent sizes are all you really need, and you simply drop a table into whichever extent-size-tablespace seems most appropriate at the time. If you get it wrong, it won't kill you. If you get it seriously wrong, there's always the "Move" command. And those 5 extent sizes would be...?? Well, I'll follow Oracle's own lead and suggest 64K, 1M, 8M, 64M and 256M.

Your extent sizes, on the other hand, combine LMTs with the "I'm going to really control this issue" approach which we *needed* in DMTs, but which is now completely unnecessary.

HJR Received on Thu Mar 18 2004 - 22:36:16 CST

Original text of this message